Condense Button Code from 400 Subs to be More Manageable

[DetroitDavid]

I have a macro workbook with two buttons on a worksheet (on the way to 200) and for testing I am trying to get the button to (1) set its value to a name, and then (2) use that name to show in both a msgbox and tooltip. Eventually this will control a large Select Case statement with the named value.

I want to be able to call this in VBA and use names, so I can’t use plain form buttons, I’ve discovered that they need to be ActivX buttons.

I saw a post here by VoG in 2002 that states:

“If you wanted a generic one line of code for all buttons then you would need to use Forms buttons. ActiveX buttons do not have the Application.Caller property that you could access.”

This seems similar to what I want; is there a solution?

The working code for this is below.

I’m looking to try and condense this is that it is more compact. Rather than have potentially 400 subroutines on this page, can I somehow “call” the button, or send its name with the click event and have a more elegant piece of code?

'I WOULD LIKE SOMETHING LIKE THIS...
'Private Sub CommandButton()
    ''' btn_Name = ???
    ' Get button name and use as a variable
    '_Click
        ' Set_Agency_Number = Me.OLEObjects(btn_Name).Object.Caption
        ' MsgBox Set_Agency_Number
    '_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'    For Each objTTL In ActiveSheet.OLEObjects
'       fTTL = objTTL.Name = "TTL"
'    Next objTTL
'    If Not fTTL Then CreateToolTipLabel btn_001, "001 - Button number 001"
'End Sub

'THIS BLOCK OF CODE WORKS, BUT IS TOO MANY SUBs...
Public Set_Agency_Number_text As String
 
Private Sub btn_001_Click()
    Set_Agency_Number = Me.OLEObjects("btn_001").Object.Caption
    MsgBox Set_Agency_Number
End Sub
Private Sub btn_002_Click()
    Set_Agency_Number = Me.OLEObjects("btn_002").Object.Caption
    MsgBox Set_Agency_Number
End Sub
 
 
Private Sub btn_001_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim objTTL As OLEObject, fTTL As Boolean
    For Each objTTL In ActiveSheet.OLEObjects
       fTTL = objTTL.Name = "TTL"
    Next objTTL
    If Not fTTL Then CreateToolTipLabel btn_001, "001 - Button number 001"
End Sub
 
Private Sub btn_002_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim objTTL As OLEObject, fTTL As Boolean
    For Each objTTL In ActiveSheet.OLEObjects
       fTTL = objTTL.Name = "TTL"
    Next objTTL
    If Not fTTL Then CreateToolTipLabel btn_002, "002 - Balajie HmHC (SYN)"
End Sub

TIA-DD

 

[Norie]

Why can't you use Forms buttons?

 

[DetroitDavid]

Norie:

Thank you for your reply. I wanted to eventually be able to have more control over the visual appearance of the button and its label.

For example, I’d like to (in code) based on a lookup table:

• set the active/inactive state of the button(s)
• change the font color of the label

There was another reason while testing it, but I can’t remember.

If what I’m asking for CAN’T be done with ActiveX buttons, then I’ll probably try using form buttons. If I changed to standard form buttons, what would the code need to look like to accomplish my goal?

Thanks,

DD

 

[Norie]

I'm not 100% sure what your goal is.

What do will these 200 buttons actually do?

By the way, why are you using the OLEObjects collection to refer to the buttons in your code?

You should be able to use their name.

Private Sub btn_001_Click()
    Set_Agency_Number = Me.bt_001.Caption ' Me is optional
    MsgBox Set_Agency_Number
End Sub

PS If you really want control over the visual appearance how about using shapes?

 

[DetroitDavid]

Spreadsheet A is a macro workbook containing a worksheet with buttons that shows as a control panel.

Buttons are numbered 001-200.

OLEObjects: is to give them a tooltip (only way I found to do it).

Shapes: using buttons so it’s more idiot-proof. People get the concempt of a clickable, press-inable button.

Open workbook 001, 002, 003, 004, etc. which are up to 200 different data sources brought into Excel to be manipulated.

Run the appropriate macro using the button on the control panel.

 

[Norie]

Do you need 200 buttons for that?

Wouldn't a listbox and single button do?

User can select the file(s) to open in the listbox and then click the command button to open them.

By the way, shapes are clickable.

 

[shg]

There is surely a better way than having a bunch of buttons -- Marlett checkboxes, Norie's suggestion, anything but all those buttons.

 

[DetroitDavid]

Regardless of my desires, the issue is sometimes people want what they want. In this case it’s an effort to make this easier for users that are used to dealing with these three-digit account numbers ad know how to press a button.

Can we take a fresh look at this thread? I’m getting a lot of questions, but need some direction on a solution. If I need to use regular buttons and not ActiveX, then I’ll do that, but I’d like way to minimize this (set of) procedure(s).

Thank you

 

[Norie]

If you really want to use all these buttons try using a class module.

 

[DetroitDavid]

OK, I’ve read a little about Classes at these sites ...

• Classes In VBA
• A Simple Example of How to use Class Modules for something useful in Excel VBA | The Land Beyond Spreadsheets
• Getting Started with Classes - Excel Liberation

... but am just beginning to catch a glimpse.

How do I implement this?