I want to find out the row number on which the checkbox is on

wasif ahmed 0
Viewed 33

I want to get the row number for the cell in which the checkboxes are on. So far what i have wrote is below but it seem not working :(

Dim checkNums As Variant
checkNums = Array(1, 5, 6, 7, 10, 11, 12, 14, 15, 16, 17, 18, 50, 51)
For k = LBound(checkNums) To UBound(checkNums)
Set FoundCell = s2.Range("C:C").Find(What:=(s2.CHECKBOXES("Check Box " & checkNums(k)).Value = xlOn))
            If Not FoundCell Is Nothing Then
                Debug.Print (" found in row: ") & FoundCell.Row
            Else
                Debug.Print ("Found")
            End If
vba excel
1 Answers

If you assign this macro to each (form control) checkbox you can figure out which checkbox called the macro.

Public Sub Checkbox_Click()

    Dim ShpName As String
    ShpName = Application.Caller

    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(ShpName)
    
    MsgBox shp.Name & " is in cell " & shp.TopLeftCell.Address & vbCr & _
        "Row: " & shp.TopLeftCell.Row & vbCr & _
        "Column: " & shp.TopLeftCell.Column & vbCr & _
        "Caption: " & shp.AlternativeText

End Sub
Darren Bartrup-Cook0
Related