CODEWITHSUNDEEP
excelvbabuttonshapes
Julian
Julian

Reputation: 185

VBA Button Application.Caller returning wrong row

I try to use the following command on several buttons in different worksheets of myexcel workbook.

MsgBox (ActiveSheet.Shapes(Application.Caller).TopLeftCell.row)

When copying those buttons to another area of my workbook, I sometimes have the problem that still the row number of the original button location is displayed. As example in the screenshot below, I click the button at the bottom, but it return row 705 instead of 739. Can anyone explain this behaviour?

enter image description here

Upvotes: 0

Views: 1309

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

It never happened to me to copy a shape with the same name. Only its Caption remained... But, since people says it is possible, try assigning this code to all the shapes in discussion, please. If the shape double name would be the reason, the code will return twice:

Sub callButName()
Dim sh As Shape
 For Each sh In ActiveSheet.Shapes
   If sh.Name = Application.Caller Then MsgBox sh.TopLeftCell.Address
 Next
End Sub

Upvotes: 3

Pᴇʜ
Pᴇʜ

Reputation: 57743

Make sure all your buttons have unique names. Somtimes it can happen that copied buttons have the same name (due to a bug in Excel). Then VBA cannot distinguish them and uses the first one that it finds. Check all your button names and make sure they are unique.

This issue can be reproduced easily:

  1. Open a new workbook
  2. Add a button (FormControl)
  3. Name it MyButton
  4. Copy that button
  5. Paste it somewhere else in the same sheet
  6. Use the code from the question for both buttons

The button will now always show the row of the first button, because both have the exact same name.

enter image description here Image 1: Illustration how this issue can occur. In the end both buttons show the same row value. Because they have the same name VBA only can find the first one.

The soulution is, when ever you copy a button immediately make sure you rename it to a unique name.

Upvotes: 6

Related Questions