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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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.
 

Forum statistics

Threads
1,147,513
Messages
5,741,601
Members
423,671
Latest member
ProSafe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top