Listbox 01 --> 50 in UserForm

[RyanH]

I have a Combo Box in a UserForm with values that range from 01 to 50. Is
there a way that I can have my users just click an up or down arrow next to a
ListBox and the number will have 1 added or subtracted from the number in the
listbox. For example, the Number in the list box is "03", if the user clicks
the up arrow "04" is displayed. If the user clicks the down arrow "02" is
displayed. I figure this would be better than a combo box.

Thanks in Advance,
Ryan

 

[JE McGimpsey]

One way:

I'd recommend a text box instead. Put a SpinButton control next to it.
Load your starting value in the userform's initialize event:

Private Sub UserForm_Initialize()
SpinButton1.Value = 1 'Default value
End Sub

Private Sub SpinButton1_Change()
TextBox1.Text = Format(SpinButton1.Value, "00")
End Sub

 

[Dave Peterson]

An alternative to JE's suggestion is to use a label and an a scrollbar.
Sometimes, clicking 50 times on a spinner can be too much to ask the user to do.

Option Explicit
Private Sub ScrollBar1_Change()
Me.Label1.Caption = Me.ScrollBar1.Value
End Sub
Private Sub UserForm_Initialize()
With Me.ScrollBar1
.Max = 50
.Min = 1
.LargeChange = 10
.SmallChange = 1
.Value = 1
Me.Label1.Caption = .Value
End With
End Sub

 

[JE McGimpsey]

Sometimes, clicking 50 times on a spinner can be too much to ask the user to
do.

Keeps them numbers low... encourages workarounds... <g>

However, you can use a Textbox to make it convenient to enter values,
too:

Private Sub ScrollBar1_Change()
If Not bDontUpdate Then
On Error Resume Next
bDontUpdate = True
Me.TextBox1.Text = Format(Me.ScrollBar1.Value, "00")
bDontUpdate = False
End If
End Sub

Private Sub TextBox1_AfterUpdate()
If Not bDontUpdate Then
On Error Resume Next
bDontUpdate = True
With Me.TextBox1
If IsNumeric(.Value) Then _
If .Value >= Me.ScrollBar1.Min Then _
If .Value <= Me.ScrollBar1.Max Then _
Me.ScrollBar1.Value = CLng(.Value)
.Value = Format(Me.ScrollBar1.Value, "00")
bDontUpdate = False
End With
End If
End Sub

Private Sub UserForm_Initialize()
bDontUpdate = False
With Me.ScrollBar1
.Max = 50
.Min = 1
.LargeChange = 10
.SmallChange = 1
.Value = 1
End With
End Sub

 

[RyanH]

Thanks for the suggestion. For some reason it is saying the .LargeChange =
10 is not a valid property of the scroll bar. Any idea why?

Thanks
Ryan

 

[Dave Peterson]

Nope.

Try deleting the line and retyping it manually. Sometimes copying from web
pages can add extra hidden characters--but that's sometimes a problem with
Google.

Or if you typed in the code manually, try copy and pasting.

And if all else fails, post the code you used.