You are here:

Excel/Shape name and macro


Hi Aidan,
hope you are keeping well since you last helped me.
I have a new question:
I am creating "buttons" on the fly using Shapes. Each Shape is named "button1" "button2", etc. and each button is linked to a macro let's call them for the moment "macro1" "macro2". Each macro
calls upon ranges and function,each having the appropriate number 1,2,3, The example below simply shows OK1 if it is button1, Ok2 if it is button2, etc

Sub addbuttonN()
Dim i As Integer
For i = 1 To 3
   Range("B" & i).Paste
   With Selection
       .ShapeRange.Name = "Button" & i
       .OnAction = "macro" & i
   End With
End Sub
Sub macro1()
MsgBox ("OK1")
End Sub
Sub macro2()
MsgBox ("OK2")
End Sub
Sub macro3()
MsgBox ("OK3")
End Sub

Because my macros are much more complicated than that and I do not know how many buttons each user is going to create, I would like to be able to write the macro only once and knowing the integer of each button being able to refer to the macro. In the example below the macro the button refer to would be something like
Sub macro i()
MsgBox ("OK" & i)
End Sub

Do you know how I could do this?
Thanks a lot.

Sorry for the delay in replying - I wasn't able to find the time to do the research needed until today - once I started, I've found this is actually relatively straightforward - see where you will see that you just need to use application.caller to determine which button was clicked - hopefully this gives you what you need but do let me know if I can help further - I will try to be quicker next time!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 All rights reserved.