PDA

View Full Version : Solved: User form numeric value



jwise
10-16-2007, 10:40 AM
I'm trying to build a userform that accepts two values. One is a number that can vary from 1 to 100 with a default of 15. I can't figure out which of the userform controls is appropriate. I've used the combo box, list box, scroll bar, and spin button. The scroll bar is the closest to what I want, but it doesn't display this value as you click on the arrows or move the bar. I also want vertical arrows, not horizontal. I've seen this kind of control in many Windows applications. Does VBE lack this, or am I misssing something (else!)?

Bob Phillips
10-16-2007, 10:50 AM
Create a spinbutton and a label



Private Sub SpinButton1_SpinUp()
With Me.SpinButton1
If .Value > 100 Then
.Value = 100
End If
Me.Label1.Caption = .Value
End With
End Sub

Private Sub SpinButton1_SpinDown()
With Me.SpinButton1
If .Value < 1 Then
.Value = 1
End If
Me.Label1.Caption = .Value
End With
End Sub

jwise
10-16-2007, 01:00 PM
I'm having a hard time believing VBA doesn't have this sort of control built-in, but I did look! I hope you understand the humor in this.

I think I understand the strategy behind this code, but it seems like it should be some kind of event code, probably for a "change event". Am I off again?

I'm putting the code in now to test it out. Thanks again XLD.

Bob Phillips
10-16-2007, 01:02 PM
The event is clicking the button.

What I didn't do is to initialise it to 15, so you have to do that.

jwise
10-16-2007, 01:17 PM
Thanks again. I have the code installed, but not tested yet.

jwise
10-17-2007, 06:55 AM
The macro is working great! Thanks again.

The spin button seems to be very useful in appropriate situations. I am surprised that you need event code to make it work properly.

Bob Phillips
10-17-2007, 07:20 AM
You don't need event code to make it work properly, it works fine on its own and you test its value in any button code say, but you do need to use the event if you want special conditions, such as showing the spinners value.