Loop not catching all values

[jacqjac]

'SHEETS
For Each ctrl As Control In Me.Controls
What am I doing wrong here? When I put in manual text after the line return it fill in but as is it doesn't pick up all the checkboxes under HEADINGS. It does create all the sheets under SHEETS.

If (TypeOf ctrl Is CheckBox AndAlso CType(ctrl, CheckBox).Checked) AndAlso CType(ctrl, CheckBox).Name.Contains("Sheet") Then
wb.Worksheets("Template").copy(after:=sheet)
sheet = wb.ActiveSheet
sheet.Name = CType(ctrl, CheckBox).Text

'HEADINGS *******ONLY CATCHING 1ST HEADING ON EACH SHEET*******
For Each head As Control In Me.Controls
If (TypeOf head Is CheckBox AndAlso CType(head, CheckBox).Checked) AndAlso CType(head, CheckBox).Name.Contains("Head" & sheet.Name) Then
sheet.Range("E31").Select()
excel.ActiveCell.Value2 = CType(head, CheckBox).Text
'LINE RETURN
excel.ActiveCell.Offset(+1, 0).Select()
End If
Next
End If
Next

[HanneSThEGreaT]

Give us more details on what you are trying to achieve and what precicely you are struggling with, please! :)

[jacqjac]

I have a lot of it worked out now. I am very stuck on one last thing that I thought would be simple
I am trying to get the selected values of a multiselect listbox into the excel.activecell

Here's the code section and the error i get

For Each box As ListBox In Me.Controls '***CASTING ERROR INFINITY*****
If box.Name.Contains(head.Name) Then
Dim itms As ListBox.SelectedObjectCollection
itms = box.SelectedItems
excel.ActiveCell.Value2 = itms
excel.ActiveCell.Offset(+1, 0).Select()
End If
Next

[chkmos]

You cannot directly write this code, because the Me.Controls returns all controls in the form (including buttons, checkboxes, listboxes), and the Button cannot simply cast as ListBox, right? Therefore, a casting error is resulted.


For Each box As ListBox In Me.Controls
...
Next



To select only the Listboxes, you have to do this


For Each ctl As Control In Me.Controls
If Not TypeOf ctl Is ListBox Then Continue For
'Only ListBox controls were catched
Next

[jacqjac]

For Each box As Object In Me.Controls
If TypeOf box Is ListBox AndAlso Name.Contains(head.Name) Then

Would this do the same thing?