spinbutton appearance at max

[cameron]

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?

 

[Jim North]

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]

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

 

[Jim North]

You could still change the color of the textbox when a limit is hit.

 

[dmckinney]

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]

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.