获取单元格中的CommandButton名称/ID [英] Get CommandButton name/ID in a cell

问题描述

我有一个在单元格中带有CommandButtons的电子表格.我只想获取特定单元格中的命令按钮的名称/ID，而无需单击按钮.我该怎么办?我一直在使用此代码，但是它效率低下，因为我必须逐个键入每个按钮ID.我大约有60个按钮.

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

推荐答案

一种方法是循环浏览工作表上的所有按钮，并检查TopLeftCell是否与您的单元格匹配.可能会因为很多按钮而陷入困境，但我认为60可以正常运行.如果按钮重叠，可能会遇到问题，但是您可以避免这种情况.它可以查看所有 Shape ，可以将其范围缩小一些，但我不确定您使用的是哪种类型的按钮.

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

这篇关于获取单元格中的CommandButton名称/ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！