PDA

View Full Version : Get row of Checkboxes



kelseyvdh
09-11-2018, 10:28 AM
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
09-11-2018, 10:50 AM
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
09-11-2018, 11:08 AM
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
09-11-2018, 01:01 PM
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
09-12-2018, 01:23 AM
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
09-12-2018, 01:43 AM
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
09-12-2018, 02:43 AM
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
09-12-2018, 02:58 AM
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