CODEWITHSUNDEEP
excelvbacommandbutton
user4965884
user4965884

Reputation:

Get CommandButton name/ID in a cell

I have a spreadsheet with CommandButtons in cells. I would simply like to Get the name/ID of the commandbutton in a particular cell without a button being clicked. How could I go about it ? I have been using this code but its inefficient as I have to type every button ID one by one. I have about 60 buttons.

 Sub Worksheet_Calculate()

  If Cells(6, 3).Value = 0 Then

    Me.Buttons("Button 55").Enabled = False
    Me.Buttons("Button 55").Font.ColorIndex = 16
Else
    Me.Buttons("Button 55").Enabled = True
    Me.Buttons("Button 55").Font.ColorIndex = 1

    End If

    If Cells(7, 3).Value = 0 Then

    Me.Buttons("Button 61").Enabled = False
    Me.Buttons("Button 61").Font.ColorIndex = 16
Else
    Me.Buttons("Button 61").Enabled = True
    Me.Buttons("Button 61").Font.ColorIndex = 1

    End If

Upvotes: 0

Views: 1232

Answers (1)

Sobigen
Sobigen

Reputation: 2169

One way would be to loop through all the buttons on the worksheet and check the TopLeftCell for a match to your cell. Might bog down for a lot of buttons but I think 60 would run fine. May run into problems if you have overlapping buttons but you can probably avoid that. This looks at all Shapes, it's possible to narrow it down some but I wasn't sure which type of button you're using. 

'In a worksheet module change Me. to a worksheet as needed.
Function findShape(r As Range) As String
    Dim s As Shape

    findShape = "Not Found" 'if loop goes all the way around and doesn't find it.

    For Each s In Me.Shapes
        If s.TopLeftCell.Address = r.Address Then
            findShape = s.Name
            Exit For 'no need to keep going
        End If
    Next
End Function

Upvotes: 1

Related Questions