Excel/Shape name and macro
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
Dim i As Integer
For i = 1 To 3
Range("B" & i).Paste
.ShapeRange.Name = "Button" & i
.OnAction = "macro" & i
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)
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 http://www.excelvbamacros.com/2011/08/print-shape-name-and-text-you-clicked.html
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!
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