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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.