Loop not chatching all values

[ImSimple]

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.

'SHEETS
For Each ctrl As Control In Me.Controls
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 1ST 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

Jacque

 

[Sorwen]

I think I understand what you are asking. Look at it like this lets say that sheet.name is ThisSheet and then break it all up into actual meaning rather than code.

For Each head that is a control in Me.Controls
If head is a CheckBox and head is checked and the head name contains HeadThisSHeet

That is what you code says. Are all the head controls checked? Does each of them contain Head and the sheet.name with no space in the name not the text of the control? If so I have no clue what to tell you.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[ImSimple]

You got it perfectly. There is a character between head and sheet.name i didn't think it would matter since i was using contains instead of equals. i'll give it a try.

Fingers Crossed! Thanks!

Jacque

 

[ImSimple]

it didn't work.

Jacque

 

[Sorwen]

The only thing I can suggest is to break and step through watching for each head and see what isn't adding up. One of those conditions in the If is wrong in some way. I wouldn't think this would matter if it is getting the first heading, but keep in mind that if the controls are in a container like a panel or group box they technically belong to that container not the form. So that you would have to check Me.Panel1.Controls rather than Me.Controls if they are on a panel.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[ImSimple]

They're not in a container. I don't know what could be wrong. Thanks for trying.

Jacque

 

[ImSimple]

It's pulling the 1st heading for each sheet but not the second. later there will be anywhere from 3-10 on a sheet.

Jacque