CODEWITHSUNDEEP
excelbuttonvba
Manish
Manish

Reputation: 213

Excel VBA Button Cell address is wrong

I have a tool, where I insert multiple small button in multiple column automatically (assume Column C1:C90,E1:E90,G1:G90...), based on some selection criteria. I am using the .Buttons.Add method of the Worksheet class to create the buttons.

I am unable to find correct cell address when I click on a button. E.g. If I click on button in cell C2, It gives C70 cell address, if I Click on E4 it still gives some other cell address. I tried with following code below. Can anyone help me on this?, 

Sub Mainscoresheet() 
     ' Mainlineup Macro to add button

    Dim b As Object, RowNumber,ColNumber As Integer 
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        RowNumber = .Row
        ColNumber = .Col
    End With 
    MsgBox "Row Number " & RowNumber 
    MsgBox "Row Number " & ColNumber
End Sub

I added below the button add function.

XRow = 7: xCol = 5
        Do Until wks.Cells(XRow, 1) = ""
        DoEvents
            For i = 1 To M_Count
                Set Bt = wks.Range(Cells(XRow, xCol), Cells(XRow, xCol))
                Set btn = wks.Buttons.Add(Bt.Left + 1, Bt.Top + 1, Bt.Width - 2, Bt.Height - 2)
                With btn
                    .OnAction = "BtnCopy"
                    .Caption = ">>"
                    .name = "Note" & Now
                End With
                xCol = xCol + 2
            Next i
        xCol = 5
        XRow = XRow + 1
        Loop

Upvotes: 2

Views: 914

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19299

You are trying to identify the button that was clicked with the following line:

Set b = ActiveSheet.Buttons(Application.Caller)

Using Application.Caller in this way relies on each Button having a unique name. However, when you create the buttons you use this code:

With btn
    .OnAction = "BtnCopy"
    .Caption = ">>"
    .name = "Note" & Now
End With

But you use the Now function to add an 'id' to the Button. Using Now means that you may create more than one button with the same name because the code will run quick enough that Now will not increment within the code. Also, if you run the code a few times e.g. create buttons in 3 columns in 3 different runs then each set of buttons may likely have the same name. This will confuse Application.Caller in that it will just return the Button that is the 'first match`.

So you should use a counter to create a unique name for each button. See the code example below that does this for sample set of 10 Buttons:

Option Explicit

Sub CallButton()

    Dim btn As Object
    Dim lngRow As Long, lngCol As Long

    Set btn = ActiveSheet.Buttons(Application.Caller)
    With btn.TopLeftCell
        lngRow = .Row
        lngCol = .Column
    End With

    MsgBox "Button name " & btn.Name
    MsgBox "Row Number " & lngRow
    MsgBox "Col Number " & lngCol

End Sub

Sub Make10Buttons()

    Dim ws As Worksheet
    Dim lng As Long
    Dim rng As Range
    Dim btn As Object

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    For lng = 1 To 10
        Set rng = ws.Cells(lng + 1, 2)
        Set btn = ws.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
        With btn
            .OnAction = "CallButton"
            .Caption = ">>"
            .Name = "Note" & lng '<--- need a unique ID for each button
        End With
    Next lng

End Sub

You can adapt the sample to work with your code by using XRow and xCol to create an unique suffix to append to each of your buttons.

Upvotes: 2

Related Questions