ActiveX controls – CommandButton in Excel VBA

Building an application for entering data – Inserting a Command Button – CommandButton (ActiveX Control) 

The command button is already known to us. In the simplest form, it will be used to run written scripts. The command button can be embedded in both the Excel spreadsheet and the UserForm form. How do I insert a CommandButton command button? Go to the Developer tab in succession , then insert the ActiveX controls and select the command button. The same applies to the UserForm form. From the ToolBox window, select the controlCommandButton. At this point, our application contains one CommandButton button named Enter. We want to create another button used to clear data in the form. Let’s put the second ActiveX button – CommandButton in our form. In the properties, change the value in the Caption field to “Clear”.

 

Building an application for entering data – Programming the CommandButton command button (ActiveX Control) 

The button is programmed by double-clicking it in the design mode. The code is edited in the code of the sheet object. In our case it will be Sheet1. For our button, we create a code that erases all data present in the TextBox, ComboBox, OptionButton and CheckBox fields. The code looks like:

Private Sub CommandButton2_Click()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""

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

ListBox1 = ""

OptionButton1 = False
OptionButton2 = False

CheckBox1 = False

End Sub

In this way you have programmed the button that cleans the data in our form. The program code in the Sheet1 object looks like:

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

Private Sub CommandButton2_Click()

'KASOWANIE DANYCH Z FORMULARZA
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""

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

ListBox1 = ""

OptionButton1 = False
OptionButton2 = False

CheckBox1 = False

End Sub

Leave a comment

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

%d bloggers like this: