Loop and Check box

[gerald.lebret]

Hello,

I have designed a form with about 160 controls among about a third of checkboxes.
The purpose for the users is to be able to check several checkboxes and when they validate the form vba will create a new entry in an excel sheet.

I've written the following code but can't make it work.

Can anyone help?

Private Sub Update_Click()
Dim CBX As CheckBox
Dim LastRow As Long
Dim ctrl As Control
On Error GoTo ErrorHandle
  
LastRow = Worksheets("Rapport").Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(LastRow, 1).Select
  
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.CheckBox Then
           If CheckBox.Value = True Then
           ActiveCell.Value = CheckBox.Caption
            End If
        End If
    Cells(LastRow, 1).Select
    Next ctrl
ErrorHandle:
MsgBox Err.Description
End Sub

 

[Alan_P]

Hi Gerald,

You were not incrementing the "LastRow" so all data was sent to the same cell...

Try this small change;

Private Sub Update_Click()
Dim CBX As CheckBox
Dim LastRow As Long
Dim ctrl As Control
On Error GoTo ErrorHandle
  
LastRow = Worksheets("Rapport").Cells(Rows.Count, 1).End(xlUp).Row + 1
  
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.CheckBox Then
            If CheckBox.Value = True Then
                [COLOR=#ff0000]Cells(LastRow, 1).[/COLOR]Value = CheckBox.Caption
            End If
        End If
        [COLOR=#ff0000]LastRow = LastRow + 1[/COLOR]
    Next ctrl
ErrorHandle:
MsgBox Err.Description
End Sub

Hope this helps,
Cheers,
Alan.

 

[dmt32]

As well as Alans's suggestions may want to try changing part of this line:

If CheckBox.Value

to this

If ctrl.Value

Dave

 

[gerald.lebret]

Thank you guys for your answer. It does go to the right cell. However, I do not get the caption of my check boxes when I tick them. Any Idea??

Thanks