ActiveX controls – CheckBox in Excel VBA

Building an application for entering data – Inserting CheckBox check boxes (ActiveX control)

How do I enter the CheckBox checkbox? We embed them in Excel in the same way as the option buttons . The important thing is that unlike the option button, we can simultaneously select several CheckBoxes. The CheckBox selected by the user assumes the value True . Decorated False value . Add to our application one checkbox above the “Consent” column. Then going toProperties Properties let’s change the value in the Caption to “Highlight”. Insert the label of our button in this way.


Building an application for entering data – Programming CheckBox check boxes (ActiveX control)

How do I get the value of the CheckBox checkbox to the VBA code? As we mentioned earlier, the check box when selected is True. Let’s try to program them using the If Then Else conditional statement . Our goal is to fill in the “Marketing Consent” column with Yes or No. We enter the VisualBasic Editor . We edit the code in the “Sheet1” object

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

Let’s test the changes we’ve made. The entire code contained in Sheet1 should now look like this:

Private Sub CommandButton1_Click()

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

sngId = 1 + Application.WorksheetFunction.Max(Range("A:A"))
sngWiersz = 6 + Application.WorksheetFunction.CountA(Range("A:A"))

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

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
End Sub

Leave a comment

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

%d bloggers like this: