checking macrobutton name in marco (XL 2007 NL)

Topic

New Reply

Hello all,

I want to create te following situation:
1) a sheet with a lot of macro buttons all starting the same macrocode but with different texts on the button (button name)
2) the started code is able to check the name of the button that was pressed and displays the corresponding sheet
The reason for this is, that I automaticly create a lot of sheets (depending on a source file) and want a startpage that where the user can select what kind of sheet he wants to see.

part one is not the biggest problem I think but it is part two that is a black hole.

Could one of you give me a direction?
Thanks in Advance for your reaction.

Patrick Schouten
the Netherlands

Reply | Quote

Replies

If you use command buttons from the Forms toolbar, you can assign the same macro to all of them.
Within the macro, Application.Caller returns the name of the command button that was clicked, so you can use code like this:

Select Case Application.Caller
Case “Button 1”
…
Case “Button 2”
…
…
End Select

If you want to inspect the caption of the button, you can use ActiveSheet.Buttons(Application.Caller).Caption.

Reply | Quote

Thanks Hans,

When creating the buttons I also have some problems.
There seems no way to control the names of the shapes when creating them. When deleting all shapes and recreating them the first one is not named “Button 1” anymore
Is there a way when creating the buttond to define there name directly?
Or is the a simple way to loop through al shapes afterwards to set the OnAction and Caption properties?

greetings
Patrick

Reply | Quote

I already found a solution, just keep the object selected and change all settings before creating the next shape

Patrick

Reply | Quote

You can change the name of a button manually:
– Right-click the button so that it is selected.
– Activate the Visual Basic Editor (Alt+F11).
– Activate the Immediate window (Ctrl+G).
– Type the following, where MyButton is the name you want to give the button

Selection.Name = "MyButton"

You can loop through the buttons as follows:

Sub LoopButtons()
Dim shp As Button
Dim i As Integer
For Each shp In ActiveSheet.Buttons
i = i + 1
shp.Name = "MyLittleButton" & i
shp.OnAction = "MyMacro"
shp.Caption = "MyCaption" & i
Next shp
End Sub

Reply | Quote