Macro Buttons

Good evening..

Last week I posted a question on how to make multiple buttons point to a common subroutine. Leith Ross posted some code to help me out (thanks Leith) I decided to make a new thread because I had a couple of extra questions...( the original thread is here: http://www.excelforum.com/showthread.php?t=595608 )

I was on the road at the time and did not have time to test it out. I finally tried it and I was getting and "Object Required" error at

Set btn = Application.caller

I received the same error no matter if I placed the code in the sheet object or a separate module.

I played and tweaked a little and I was able to get something going from his basic structure. The following syntax seems to work:

Sub Btn_clicks

btn = Application.Caller

Select Case btn

Case "Button 1"
'do something
Case "Button 2"
'do something else
Case "Button 3"
'do something differnet altogether

End Select
End Sub

The problem with this is that without the "Dim btn as Excel.Button" then you can not access the btn properties (btn.caption, btn.name)

Any ideas on getting rid of the above error?

Now for my other question:
I have read a good bit on the difference between buttons created from the control toolbox vs the forms toolbox... I wish there was a hybrid between the two types...

Buttons from the control toolbox enable you to set properties such as name etc etc, and I know since they are really an ActiveX control that they also support many more actions than a form button (mouseover, dblclick etc etc) One limitation is that you can not point it to a specific sub like I was trying. I got around this by REMOVING the "=EMBED("Forms.CommandButton.1","") code from the formula bar while in design mode. This makes it so you can not access the properties, but you can point it to a different sub.

Is there anyway to access properties for a button created using the forms toolbox so I can edit the name? (the button is on a sheet, not an actual form)

What type of buttons do ya'll Experts use most and why?

I have attached a small spreadsheet that has working and non working code that shows what I am trying to do (I hope)

Hello Tekman,

Application.Caller only works with the Forms menu controls. If your buttons were created using the Control Toolbox it won't work.

Sincerely,
Leith Ross

Thanks Leith

I figured that out but the issue that I would like to resolve is the error I am receiving with the following:

Dim btn as Excel.button
Set btn =application.caller

if I just use

btn = application.caller

then it works, but I can not access the properties of the button
The original code that you posted gives me an object required error no matter where I put the code.

Thanks,

Lee