CODEWITHSUNDEEP
excelvba
BoCode
BoCode

Reputation: 925

Get the cell address when a form button in it is clicked

I've multiple buttons in multiple cells and when any one of them is clicked, I've to perform a function.

I need to know which cell has this clicked button.

Upvotes: 4

Views: 8571

Answers (5)

Kinia
Kinia

Reputation: 1

My suggestion is to try this:

Dim r As Range
Set r = ActiveSheet.Shapes(Application.Caller).TopLeftCell
MsgBox r.Address

Here you can get the address where the button is placed

Upvotes: 0

julien
julien

Reputation: 21

For some reasons I was obliged to use .Shapes(Application.Caller) because the .Buttons(Application.Caller) was returning a bug. 

Dim r As Range
  Set r = ActiveSheet.Shapes(Application.Caller).TopLeftCell

Upvotes: 2

Arran549
Arran549

Reputation: 182

  Dim r As Range
  Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

The above code will give you the cell address of the button (This only works for form buttons not active x buttons).

Upvotes: 4

Michael
Michael

Reputation: 1708

While you're in Design Mode (meaning you can move and resize buttons), you can double click on a button and the VBA editor will open up with a subroutine for the Click event of that button. As mentioned above, they're not linked to the cell you happen to have them over in any way.

Upvotes: 0

Lunatik
Lunatik

Reputation: 3948

Form controls aren't linked directly to worksheet cells. They can be 'anchored' to them for size & alignment purposes, but don't as far as I know have any relationship with the cell that would allow you to get a cell reference, assuming one could even be determined without ambiguity.

Each form control can only run one macro though, so surely it is easy to identify that way, no?

Upvotes: 0

Related Questions