Results 1 to 5 of 5
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    checking macrobutton name in marco (XL 2007 NL)

    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
    Greetings,

    Patrick Schouten
    (The Netherlands)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: checking macrobutton name in marco (XL 2007 NL)

    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.

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: checking macrobutton name in marco (XL 2007 NL)

    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
    Greetings,

    Patrick Schouten
    (The Netherlands)

  4. #4
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: checking macrobutton name in marco (XL 2007 NL)

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

    Patrick
    Greetings,

    Patrick Schouten
    (The Netherlands)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: checking macrobutton name in marco (XL 2007 NL)

    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
    <code>
    Selection.Name = "MyButton"
    </code>
    You can loop through the buttons as follows:
    <code>
    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</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •