numeric up down control in vba

Question

Is there an in-built numeric updown control in vba or do we need to create a control like that?

If there is such a control then what are the events that we may use.

Pls suggest.

Answer 1

You can use the SpinButton1 control for that

SNAPSHOT

CODE

You can either set the min and max of the SpinButton1 in design time or at runtime as shown below.

Private Sub UserForm_Initialize()
    SpinButton1.Min = 0
    SpinButton1.Max = 100
End Sub

Private Sub SpinButton1_Change()
    TextBox1.Text = SpinButton1.Value
End Sub

FOLLOWUP

If you want to increase or decrease the value of the textbox based on what user has input in the textbox then use this. This also makes the textbox a "Number Only" textbox which just fulfills your other request ;)

Private Sub SpinButton1_SpinDown()
    TextBox1.Text = Val(TextBox1.Text) - 1
End Sub

Private Sub SpinButton1_SpinUp()
    TextBox1.Text = Val(TextBox1.Text) + 1
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
      Case vbKey0 To vbKey9, 8
      Case Else
        KeyAscii = 0
        Beep
    End Select
End Sub