Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox 01 -- 50 in UserForm
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox 01 -- 50 in UserForm
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 In article , RyanH wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox 01 -- 50 in UserForm
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 RyanH wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox 01 -- 50 in UserForm
In article ,
Dave Peterson wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox 01 -- 50 in UserForm
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" wrote: 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 RyanH wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox 01 -- 50 in UserForm
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. RyanH wrote: 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" wrote: 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 RyanH wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm with ListBox | Excel Programming | |||
UserForm & listBox | Excel Programming | |||
on Listbox in Userform.... | Excel Programming | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
UserForm with ListBox | Excel Programming |