ActiveX controls – Label in Excel VBA

Building an application for entering data – Inserting Labels – Label (ActiveX Control) 

The label Label is already known to us and should not require a broader discussion. How do you insert a Label Label? From the ActiveX controls in the Developer tab, select the Label control. Next, we put 3 labels next to the combo boxes responsible for entering the day, month and year. You can change their name in the properties of the control by editing the Caption position . Then inserta header label for our application. Let’s call it “Data entry”. The font size of the label can also be changed in the properties by editing the Font position . For the needs of the application, select 18px font.

 

Programming the remaining elements of the application

Of the elements to be programmed in our application, we still have to insert the date of the record addition. Using the Date ()function, we change the code of our program in the Sheet1 object.

Cells(sngWiersz, 11) = Date

In addition to the date of introduction, we would like each time the data is entered, our form would clean up its fields, like after clicking the “Clear” button. We would also like the introduction of a single record to trigger the MsgBox window with information on the success of the operation.

MsgBox "Dane zostały wprowadzone"

TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""

ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""

ListBox1 = ""

OptionButton1 = False
OptionButton2 = False

CheckBox1 = False

The code of our program in the Sheet1 object looks like this. At the bottom of the article was also attached the application file that we wrote. Yes, yes, it’s the end of programming our application 🙂

Private Sub CommandButton1_Click()

Dim sngId As Single
Dim sngWiersz As Single
Dim sngLoginLicznik As Single

'WYBIERANIE OSTATNIEGO NIEZAPISANEGO WIERSZA I NADAWANIE ID
sngId = 1 + Application.WorksheetFunction.Max(Range("A:A"))
sngWiersz = 6 + Application.WorksheetFunction.CountA(Range("A:A"))

'SPRAWDZANIE LOGINU
sngLoginLicznik = 7

Do While sngLoginLicznik <= sngWiersz

If Cells(sngLoginLicznik, 2) = LCase(TextBox1) Then
MsgBox "Ten login znajduje się już w bazie, wprowadź inny", vbCritical
GoTo endlabel
End If

sngLoginLicznik = sngLoginLicznik + 1
Loop

'WPROWADZANIE DANYCH
Cells(sngWiersz, 1) = sngId
Cells(sngWiersz, 2) = LCase(TextBox1)
Cells(sngWiersz, 3) = UCase(Left(TextBox2, 1)) & LCase(Mid(TextBox2, 2))
Cells(sngWiersz, 4) = StrConv(TextBox3, vbProperCase)
Cells(sngWiersz, 5) = LCase(TextBox4)
Cells(sngWiersz, 6) = ComboBox1
Cells(sngWiersz, 7) = DateSerial(ComboBox4, ComboBox3, ComboBox2)
Cells(sngWiersz, 8) = ListBox1

If OptionButton1 = True Then
Cells(sngWiersz, 9) = "Kobieta"
ElseIf OptionButton2 = True Then
Cells(sngWiersz, 9) = "Mężczyzna"
Else: Cells(sngWiersz, 9) = ""
End If

If CheckBox1 = True Then
Cells(sngWiersz, 10) = "Tak"
Else: Cells(sngWiersz, 10) = "Nie"
End If

Cells(sngWiersz, 11) = Date

MsgBox "Dane zostały wprowadzone"

TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""

ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""

ListBox1 = ""

OptionButton1 = False
OptionButton2 = False

CheckBox1 = False

endlabel:
End Sub

Private Sub CommandButton2_Click()

TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""

ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""

ListBox1 = ""

OptionButton1 = False
OptionButton2 = False

CheckBox1 = False

End Sub

Private Sub SpinButton1_SpinUp()
ActiveWindow.SmallScroll up:=1
End Sub
Private Sub SpinButton1_SpinDown()
ActiveWindow.SmallScroll down:=1
End Sub

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: