spinbutton appearance at max

cameron

New Member
Joined
Aug 12, 2002
Messages
18
How do I grey out/disable one half of a spinbutton so when you reach the max (or min) value you know that you can't click any further?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
cameron,

I don't know how to change the appearance of the button, but since you haven't gotten any input, I thought I'd suggest an alternative:
change the color of the cell the number is in. Lets say the min is 3 and the max is 20. You would do this by making the normal pattern color of the cell the min/max pattern color (say yellow). Now put conditional formatting over the cell, that says:

CELL VALUE IS BETWEEN 4 AND 19

... and when this condition is true, change the pattern to no color.


Hope this helps.
 

cameron

New Member
Joined
Aug 12, 2002
Messages
18
Thanks but I'm using UserForms so cells not visible.

Sub SpinButton1_SpinUp()
if SpinButton1.Max = True Then
SpinButton.Enabled = False
End If
End Sub

This will disable the whole button which isn't what I want. There must surely be a way to do something like;

SpinButton1.SpinUp_Button.Enabled = False
 

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
Surprisingly it doesn't seem like there is a way to do what you're trying to do with the spin button control. Given that the functionality of the control is pretty basic, I would suggest you 'roll your own' with a couple of command buttons, and incorporate your functionality there.

(By the way, with VB you can make your own user controls, which is fun. I'm not sure if you can use them in VBA userforms though.)
 

cameron

New Member
Joined
Aug 12, 2002
Messages
18
guess I could make my own command buttons with arrows but if Microsoft give you a control that does the job you think they'd have finished it off properly.... hmmm, no, maybe not!!!

Anyway, have a nasty solution in that I've stuck blank, borderless images in front of each side of the spinbutton and made them visible or not based on spinbutton min and max. V. nasty, I know!

Thanks for looking into it Jim/Dmc, appreciated.

Cameron.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,435
Messages
5,572,083
Members
412,440
Latest member
jaeremata
Top