Tricky problem with buttons - any thoughts
Posted by Mark Rrutherford on July 24, 2001 8:35 AM
I'm in Excel 7 on this one.
I have one macro attached to several buttons.
How can I get that (universal) macro to recognise either the specific button that called it OR the cell over which the top left corner of the button sits?
I'm scratching my head on this one and I can do so much with it if I can resolve it.
Grateful thanks
Posted by Sarah on July 24, 2001 11:37 AM
Hi Mark,
I would add a global variable to your macro, then have each button assign the variable a different value in the Button_Click() part of the code. Then you could easily test for which button was pushed.
Hope this helps,
Sarah
Posted by Mark Rutherford on July 25, 2001 1:57 AM
Sarah,
I understand your solution and I think it will work just fine.
Would you happen to know at all where I can get to the Button_click code on Excel 7 (I know it's revealed in later versions)?
If not, no worries and thanks a lot for the reply. I've got some hope again.
Mark.
Posted by Sarah on July 25, 2001 10:39 AM
Mark,
What are you using for your buttons?
If you're using the Control Toolbox command button then you just right click the button and choose view code from the pop-up menu.
If you're using an autoshape or wordart or something else then you right click the object and choose Assign Macro. You then have to name a new macro called YourObjectName_Click.
Good luck,
Sarah
Posted by Mark Rutherford on July 26, 2001 2:12 AM
Sarah,
unfortunately, that kind of explicit code doesn't come with Excel version 7 - it must exist somewhere but not for the common user.
In any case, my grateful thanks for all your help - it triggered a different strand of questioning and an Excel authority on the web has provided the following :
Sub button_handler()
'****attach this to every button****
Select Case Application.Caller
Case "Button 1" '
Range("a1").Value = "button 1"
Case "Button 2"
Range("a1").Value = "button 2"
End Select
End Sub
Best wishes from the sunny Isle of Man.
Mark.
