Sender in VBA?

[joec188]

I'm a little new to VBA. What I have is a group of programmatically created buttons in cells. When a button is clicked, a form pops up asking for some details (using the btn1.OnAction = "showDetailsForm" event). I want to be able to pass into the form (or into a variable) the name of the button that was clicked, or the sender.

I know this is fairly easily done in VB.NET with something like:

Dim myButton As Button = sender
var1 = myButton.Name

My trouble is finding an equivalent method in VBA.

Any ideas?

Thanks!

[rbrhodes]

Hi joec

a sample is always helpful

[mikerickson]

Have you looked at Application.Caller?

[joec188]

No. Not familiar with that method. googled it and didn't come up with any usable code.


I'm sorry I don't have any examples but I tried VB.NET (code) and it brings up error after (VBA) un-usable error. I guess I don't get the difference b/t VBA and VB.Net. Seems like VBA should accept 'Sender as e' args but that's not the case.

Bottom Line: I want to load into a variable the name of the sender of an object (button) in VBA. Simple in VB.NET, but how do I to replicate in VBA?

[mdmackillop]

Use a Public variable to store the button name/caption, then get this when the form opens.

[joec188]

Thanks for the example! This seems like a good way to solve the problem. I'm having trouble figuring out how to set the public variable when the button is clicked since it is created dynamically. Here is what I have to generate the buttons. Could I add something to the .OnAction event to set the public variable? Or is there another method to call to generate the click event?

For Each cell In Range("I1:I5")
w = cell.Width
w2 = (w / 2) - 40 / 2


Set myBtn = ActiveSheet.Buttons.Add(cell.Left + w2, cell.Top, 40, cell.Height)

With myBtn
.Characters.Text = "Details"
.OnAction = "showDetailsForm" 'could I set the variable here somehow?
.Name = I
End With

I = I + 1
Next

Thanks for your help!

[rbrhodes]

Hi joe,

Looks like you're creating 5 buttons in this example. I think you need 5 variables then and store the nmae of each button as it's created.

[joec188]

Thanks for everyone's help. Was able to solve it by using Application.Caller and storing into a public variable.

Thanks again!

Public ButtonName As String

Public Sub showDetailsForm()

ButtonName = Application.Caller

End sub