ActiveX controls – SpinButton in Excel VBA

Construction of the application for entering data – Inserting the Knob Button – SpinButton (ActiveX Control) 

The next ActiveX control we will know is the knob button control. We will use it for navigation, and precisely move the rows of the sheet up and down. Our goal is to click on the upper arrow buttons to move the view of the rows one sheet up. If you click the down arrow, the sheet view should move down. How do I insert the SpinButton knob? We go into the tab Developer . From the Insert icon, choose the spinner button and put it over the last column of our table.

 

Building an application for entering data – Programming the SpinButton knob (ActiveX control) 

Due to the fact that the button and all controls of our application are in Sheet1, we also program the knob button in the Sheet1 object in the VisualBasic Editor. Assign the following code to the button by selecting previously the SpinUp and SpinDown triggers :

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

The arrows on the button should work properly. A single click on the top arrow will move the sheet view up. The program will work the opposite when you click the down arrow. The code of our program in the Arkusz1 object looks like this:

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()

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: