Get row of Checkboxes

[kelseyvdh]

Hi everyone,

I've got a sheet with Many checkboxes. Each checkbox is in on a different row.

Is there a way to determine in which row the checkbox is that is been checked and use that row for something else? I'd like to do it like this so I don't have to write a VBA macro for Each Checkbox.

Many thanks in advance!

[YasserKhalil]

Try this code
Right-click and assign the macro to your checkbok

Sub Get_CheckBox_Row()    Dim obj As Object, r As Long, c As Long


    Set obj = ActiveSheet.CheckBoxes(Application.Caller)


    With obj.TopLeftCell
        r = .Row
        c = .Column
    End With


    MsgBox "Row Number: " & r & vbCrLf & "Column Number: " & c
End Sub

[kelseyvdh]

Try this code
Right-click and assign the macro to your checkbok

Sub Get_CheckBox_Row()    Dim obj As Object, r As Long, c As Long


    Set obj = ActiveSheet.CheckBoxes(Application.Caller)


    With obj.TopLeftCell
        r = .Row
        c = .Column
    End With


    MsgBox "Row Number: " & r & vbCrLf & "Column Number: " & c
End Sub

Works great with forms checkboxes! Thanks!

Is there also a code available for Active-x checkboxes?

[YasserKhalil]

In worksheet module try this code for CheckBox1

Private Sub CheckBox1_Click()
    Dim obj As Object


    Set obj = ActiveSheet.OLEObjects("CheckBox1")
    GetCheckBoxAddress obj
End Sub


Sub GetCheckBoxAddress(cb As Object)
    Dim r As Long, c As Long


    If cb.Object.Value = True Then
        With cb.TopLeftCell
            r = .Row
            c = .Column
        End With


        MsgBox "Row Number: " & r & vbCrLf & "Column Number: " & c
    End If
End Sub

[kelseyvdh]

Great! Thanks! I got it almost working. Just one more thing I can't seem to solve.

Sub GetCheckBoxAddress(cb As Object)
    Dim ro As Long, c As Long
    If cb.Object.Value = True Then
        With cb.TopLeftCell
            ro = .row
            c = .Column
        End With
Dim FirstRow As Long
Dim FirstCell As Long
Dim ZoekenColumns As Long
FirstRow = Sheets("Offerte").Range("E158").End(xlUp).row + 1
Sheets("Zoeken").Range("B" & ro, "M" & ro).Copy
Sheets("Offerte").Range("E" & FirstRow).EntireRow.Hidden = False
Sleep 100
On Error Resume Next
Sheets("Offerte").Range("E" & FirstRow).PasteSpecial xlPasteValues
On Error Resume Next


Else
Dim r As Range
With r
Set r = Sheets("Offerte").Cells.Find(What:=Sheets("Zoeken").Range("B" & ro))
r.EntireRow.ClearContents
r.EntireRow.Hidden = True
End With
    End If
End Sub

The part after 'Else' doesn't work anymore. This is what I want: when a ceckbox in the sheet is unchecked, it needs to search for the value in column "B" and the row of the checkbox from the sheet "Zoeken" in the sheet "Offerte". When found it needs to clear the content of that row and hide it.
It works when I search for a specific cell (example: Range("B1")), but not when I search for the column + row. Any ideas?

[YasserKhalil]

Can you provide a sample workbook so as to make it clear ..?
Generally try this code (Not Tested)

Sub GetCheckBoxAddress(cb As Object)
    Dim r               As Range
    Dim ro              As Long
    Dim c               As Long
    Dim firstRow        As Long
    Dim firstCell       As Long
    Dim zoekenColumns   As Long


    With cb.TopLeftCell
        ro = .Row
        c = .Column
    End With


    If cb.Object.Value = True Then
        firstRow = Sheets("Offerte").Range("E158").End(xlUp).Row + 1
        Sheets("Zoeken").Range("B" & ro, "M" & ro).Copy
        Sheets("Offerte").Range("E" & firstRow).EntireRow.Hidden = False
        Sleep 100
        On Error Resume Next
            Sheets("Offerte").Range("E" & firstRow).PasteSpecial xlPasteValues
        On Error GoTo 0
    Else
        With r
            Set r = Sheets("Offerte").Cells.Find(What:=Sheets("Zoeken").Range("B" & ro))
            r.EntireRow.ClearContents
            r.EntireRow.Hidden = True
        End With
    End If
End Sub

[kelseyvdh]

Wow! No need for sample workbook, your code works great! Thank you, you're really helping me!

I've got on more thing and then I'll stop

With this part:

Private Sub CheckBox1_Click()
    Dim obj As Object
    Set obj = ActiveSheet.OLEObjects("CheckBox1")
    GetCheckBoxAddress obj
End Sub

Is it possible to make this so I don't need to change the number of the checkbox per checkbox? The rows start at 89 and each row has the following number for the checkbox (row 89 = CheckBox1, row 90 = CheckBox2, etc.)
Thanks again!

[YasserKhalil]

I don't know what exactly what do you mean
Try something like that

Private Sub CheckBox1_Click()
    Dim obj As Object
    Dim x As Long


    'Suppose You Have Three CheckBoxes ('CheckBox1' - 'CheckBox2' - 'CheckBo3')
    For x = 89 To 91
        Set obj = ActiveSheet.OLEObjects("CheckBox" & x - 88)
        GetCheckBoxAddress obj
    Next x
End Sub