CODEWITHSUNDEEP
excelvba
dEs12ZER
dEs12ZER

Reputation: 848

Loop rows of column until empty in excel VBA

I have a sheet with a column "N" that contains 30 rows with checkbox inside each one

in vba i want to make a loop that stops at the empty cell, means row 31 , i tried this way :

Private Sub CommandButton1_Click()

 Range("N9").Select
 
 Do

 If CheckBox1.Value = True Then Call do_OM 
 
 ActiveCell.Offset(1, 0).Select
 
  
 
 Loop while ActiveCell.value = "" 


 


End Sub

this loop works for only one row , which means a cell with checkbox is an empty cell , while what i want is to stop at cell with no checkbox .. what to do ?

+

How to say " If CheckBox(row number).Value = True ? "

Thanks in advance :)

Upvotes: 1

Views: 421

Answers (2)

VBasic2008
VBasic2008

Reputation: 55073

ActiveX Checkboxes

  • Remove, replace or out-comment the Debug.Print lines with something you need e.g. do_OM (Call is considered deprecated).
  • Note this: ole.Name, but ole.Object.Value.
Option Explicit

Sub CheckBoxExample()
    Dim ole As OLEObject
    For Each ole In ActiveSheet.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            Debug.Print
            Debug.Print ole.Name, ole.TopLeftCell.Address(0, 0), _
                ole.BottomRightCell.Address(0, 0)
            If ole.Object.Value = True Then
                'do_OM
                Debug.Print "Check Box '" & ole.Name & "' is ticked."
            Else
                Debug.Print "Check Box '" & ole.Name & "' is not ticked."
            End If
        End If
    Next ole
End Sub

Sub CheckBoxExample2()
    Dim ole As OLEObject
    For Each ole In ActiveSheet.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            Debug.Print ole.Name, ole.TopLeftCell.Address(0, 0), _
                ole.BottomRightCell.Address(0, 0)
            With ole.Object
                Debug.Print .Caption, .Value
            End With
        End If
    Next ole
End Sub

Upvotes: 1

Christofer Weber
Christofer Weber

Reputation: 1474

EDIT
This part will iterate through the non-Active X checkboxes

Since the checkbox isn't tied to the sheet, there isn't really a way to say CheckBox(row number). Also makes sense because there could be multiple boxes on one row.
If the names of the checkboxes align with the row number, however, then you can iterate through them. Well, you can iterate through them anyway, but yeah.

You could do something like:

For i = 1 To 30
    ActiveSheet.CheckBoxes(i).Value
Next i

or

For Each chk In ActiveSheet.CheckBoxes
    chk.Value
Next chk

To iterate through the boxes. Second one would obviously go through the entire sheet.
I don't think you can check a cell for a box, but you can check what cell the box is in, by using .TopLeftCell.Row which should return the row of the top left corner of the object. Then you could cross-reference that (and perhaps .TopLeftCell.Column) with your range using Intersect.

Excel VBA - Ckeck if cell contains a checkbox Might be of interest. But as I said, it's not really checking if a cell has a check box, it's going through all the checkboxes and checking if any of them are in the cell, using Intersect as I suggested. You can argue that the end result is the same however.

Edit For the comment: This is for ACTIVE X Boxes

Dim nm As Object
Set nm = ActiveSheet.CheckBox1
nm.TopLeftCell.Row

'Iteration
For Each nm In ActiveSheet.OLEObjects
    If TypeName(nm.Object) = "CheckBox" Then
        debug.print nm.TopLeftCell.row
        If nm.Object.Value = True Then ' do stuff
    End If
Next nm

Upvotes: 1

Related Questions