开发者

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 clic开发者_JAVA技巧ked, I've to perform a function.

I need to know which cell has this clicked button.


  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).


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


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.


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?


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜