Use Spinner Control to Hide/Show Buttons?

  • Thread starter Thread starter TheRobsterUK
  • Start date Start date
T

TheRobsterUK

Hi All,

I am trying to set up a spinner button (with corresponding numerical
readout) that also displays a number of buttons. The number of buttons
displayed will depend upon the spinner number e.g. if the spinner says
5, then 5 buttons will be displayed. If the spinner says 2, then the
rest of the buttons apart from 2 are hidden.

There needs to be 10 buttons in total, with a minimum of two displayed
at any one time.

I've created a mock-up worksheet to show what I mean MUCH better than
the explanation I've just given above!

http://homepages.nildram.co.uk/~proebuck/Buttons.xls

So I guess my question really relates to hiding/showing buttons. Is
there a way to do this, with a macro or VB code or whatever?

Thanks
-Rob
 
I don't open attachments--so this might not be close.

I put 10 buttons from the Forms toolbar on a worksheet. I put a spinner also
from the forms toolbar on the same worksheet.

I set the spinner to go from 2 to 10.

I named the spinner: Spinner01
(I'm gonna use those last two digits to determine which buttons go with which
spinner--in case you have lots of buttons/spinner combinations.)

And I named the 10 buttons:
sp01btn01
sp01btn02
sp01btn03
....
sp10btn10

(10 buttons "tied" to spinner01 (sp01).)

Since I'm using a consistent naming convention, I can build the names of the
buttons pretty easily.

I right clicked on the spinner and assigned it this code:

Option Explicit
Sub testme01()

Dim mySpinner As Spinner
Dim SpinNumber As Long
Dim maxBTNS As Long
Dim iCtr As Long

maxBTNS = 10

Set mySpinner = ActiveSheet.Spinners(Application.Caller)

SpinNumber = CLng(Right(mySpinner.Name, 2))

For iCtr = 1 To maxBTNS
ActiveSheet.Buttons("sp" & Format(SpinNumber, "00") _
& "btn" & Format(iCtr, "00")).Visible _
= CBool(iCtr <= mySpinner.Value)
Next iCtr

End Sub

Then I went back to excel and hid/showed my buttons by clicking on that spinner.

One way to rename the controls is to select them and type the new name in the
name box (to the left of the formula bar). Remember to hit enter after you type
the new name.
 
Back
Top