Button parameters and functions... etc

[kingkimi]

Hey!

I have a basic self taught understanding of VBA which works for most of the things I try and do, but I need some help with buttons!

I have a sub that successfully creates a button in consecutive cells and names each button after consecutive worksheets. It looks like it works fine and I can assign the macro I want to use ok.

What I really need tho, is a way of copying out the name of the button, as that is what I would need to run the other macros (to select the worksheet I need, run the calculations for the product number etc).

I'm sure its not the most pretty or efficient code (but it works and more importantly I understand what it's doing!) This creates the buttons...
Sub BUTTON_ADD()
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range
For i = 21 To 22
Set t = ActiveSheet.Range(Cells((i - 18), 6), Cells((i - 18), 6))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "BUTTON_CLICK"
.Caption = Range("A1").Offset(i - 19, 3).Value
'.Name = "TEST"
End With
Next i
Application.ScreenUpdating = True
End Sub

But I need Sub BUTTON_CLICK to copy the caption (I don't seem to need a name for the button?) so that I can run other macros... eg Sheet 21 and the first button are named 185632, so I need to be able to paste that number in a cell somewhere!

Thanks in advance for your help!!!

Matt

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this help you?

Sub BUTTON_CLICK()
    Dim Cap As String
    Cap = ActiveSheet.Buttons(Application.Caller).Caption
    MsgBox Cap
End Sub

 

[kingkimi]

That's absolutely perfect! Thanks!!