CODEWITHSUNDEEP
excelvba
Patchell13
Patchell13

Reputation: 9

Store location of command button cell address to variable in VBA

Okay so I'm using Excel and trying to get a cell address based off the location of the command button I'm clicking. I want to store that cell address in a variable as I click the button to be used in the next code for the user form that pops up as a result of clicking that button. Ive looked at this link: Store location of cell address to variable in VBA , which is similar to what I'm trying to do but I cant seem to get it to work with the ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address that gives an address based off the top left corner of the command button. I'm trying to do it this way so I can use the same code from the next part for every similar button I add without having to manually change the code based off where the button is.

I'm pretty new when it comes to Excel and VBA so any help would be appreciated.

Edit:

I tried adding the code but I'm getting a Runtime error '1004' Unable to get Buttons property of the Worksheet class. I have this

Sub Button2_Click()
    Dim BtRng As Range

    Set Btnrng = ActiveSheet.Buttons(Application.Caller).TopLeftCell

    MsgBox Btnrng.Address
    Btnrng.Offset(3, 3) = "Hello"
    formAddBill.Show

End Sub

This button is also popping up a userform for information input. the error is happening on Set Btnrng = ActiveSheet.Buttons(Application.Caller).TopLeftCell

Upvotes: 0

Views: 4353

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

If you are using application.caller then I assume your buttons are from the forms.control toolbox. This makes life a lot easier.

Assign each button with this code example:

Sub Rng_Butn_Clicked()
    Dim BtRng As Range
    Set Btnrng = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    MsgBox Btnrng.Address
    Btnrng.Offset(3, 3) = "Hello"
End Sub

If you are using command buttons from the activex toolbar, it can get a bit confusing. Doing it one at a time is easy enough

Private Sub CommandButton1_Click()
    Me.CommandButton1.TopLeftCell.Offset(0, 1) = "Hi"
    Me.CommandButton1.TopLeftCell.Offset(, 2).Interior.Color = vbBlue
    MsgBox Me.CommandButton1.TopLeftCell.Address
End Sub

But assigning one code to many activex command buttons is not as easy, I would prefer to use the Forms Buttons if you want to assign one code to many buttons.

Edit: If you are getting this error

enter image description here

You probably put the code in a command button. Please reread my answer.

This is what I am referring to:Form Controls & ActiveX Controls

enter image description here

Upvotes: 1

Related Questions