Find the existence of a button in the worksheet

[luolovepi]

I'm trying to detect whether a button exists in each of the cell in column N. If there exists a button, the button should be in a cell.
And I write the code as below. But strangely, how come it always returns me CellNotCovered a false value?

Pr.Range("N" & i).Activate
        With ActiveCell
            For Each oneShape In .Parent.Shapes
                 CellNotCovered = CellNotCovered And (Application.Intersect(.Cells, Range(oneShape.TopLeftCell, oneShape.BottomRightCell)) Is Nothing)
            Next oneShape
        End With

 

[pgc01]

Hi

Try this code to check the cells in Sheet2!N1:N5 that have a button (the button completely inside the cell).

Sub Test()
Dim ws As Worksheet
Dim r As Range, rC As Range
Dim bt As Button
Dim bExists As Boolean
 
Set ws = Worksheets("Sheet2")
Set r = ws.Range("N1:N5")
 
For Each rC In r
    bExists = False
    For Each bt In ws.Buttons
        If Not Intersect(bt.TopLeftCell, bt.BottomRightCell, rC) Is Nothing Then
            bExists = True
            Exit For
        End If
    Next bt
    MsgBox "Cell " & rC.Address & " has " & IIf(bExists, "a", "no") & " button"
Next rC
End Sub

 

[luolovepi]

Hi PGC, thanks for the help. I tested the code but I find all return me with no button.

I find your code is able to detect the form button. But all my buttons seem to be activeX button. Then do you happen to know how to detect the existence of ActiveX buttons?

Best regards,
lolo

Hi

Try this code to check the cells in Sheet2!N1:N5 that have a button (the button completely inside the cell).

Sub Test()
Dim ws As Worksheet
Dim r As Range, rC As Range
Dim bt As Button
Dim bExists As Boolean
 
Set ws = Worksheets("Sheet2")
Set r = ws.Range("N1:N5")
 
For Each rC In r
    bExists = False
    For Each bt In ws.Buttons
        If Not Intersect(bt.TopLeftCell, bt.BottomRightCell, rC) Is Nothing Then
            bExists = True
            Exit For
        End If
    Next bt
    MsgBox "Cell " & rC.Address & " has " & IIf(bExists, "a", "no") & " button"
Next rC
End Sub

 

[pgc01]

I find your code is able to detect the form button. But all my buttons seem to be activeX button.

Hi Lolo

The buttons seem to be activeX button? Did someone else create them?

If the buttons are activeX controls then try this code to check the cells in Sheet2!N1:N5 that have a button (the button completely inside the cell).

Sub Test()
Dim ws As Worksheet
Dim r As Range, rC As Range
Dim OLEobj As OLEObject
Dim bExists As Boolean
 
Set ws = Worksheets("Sheet2")
Set r = ws.Range("N1:N5")
 
For Each rC In r
    bExists = False
    For Each OLEobj In ws.OLEObjects
        If TypeOf OLEobj.Object Is MSForms.CommandButton Then
            If Not Intersect(OLEobj.TopLeftCell, OLEobj.BottomRightCell, rC) Is Nothing Then
                bExists = True
                Exit For
            End If
        End If
    Next OLEobj
    MsgBox "Cell " & rC.Address & " has " & IIf(bExists, "a", "no") & " button"
Next rC
End Sub