form will not show when VBE is modified

[bigt95nt]

When the following code is executed, the form that the controls (CheckBoxes 1 to n) are created on fails to "Show" on the sheet. However the form appears in the project window with the correct number CheckBox Controls and if "RUN FORM" is selected the form apears on the sheet and each control works properly when each CheckBox is selected.

If on the other hand, if the code is commented out, the form displays on the sheet with the correct number of CheckBox controls, but no functionality
NOTE: the code shown is in a FOR NEXT loop and is not shown in its entirity.

Any comment would be greatly appreciated!

With TempForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Sub Checkbox" & i & "_Change()"
.InsertLines Line + 2, " if ActiveChart.SeriesCollection(" & i & ").Format.Line.visible = True then"
.InsertLines Line + 3, ""
.InsertLines Line + 4, "ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= False"
.InsertLines Line + 5, "exit sub"
.InsertLines Line + 6, "else ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= True"
.InsertLines Line + 7, ""
.InsertLines Line + 8, "exit sub"
.InsertLines Line + 9, "end if"
.InsertLines Line + 10, "End Sub"
End With

 

[AlphaFrog]

The 1st thing I would suggest is to compare the names of the checkboxes to the Names of the _Click event procedures. I can't tell from the code you posted, but the checkboxes that you create have to be named Checkbox" & i so that the _Click event procedures you create match the same Names of the checkboxes you create.

 

[bigt95nt]

I have included the code in its entirety

For i = iCount To 1 Step -1
Set NewCheckBox = TempForm.Designer.Controls _
.Add("forms.CheckBox.1")
With oChart.TrendLegend

NewCheckBox.Caption = i & " " & .GetText(i)
NewCheckBox.Left = 1
NewCheckBox.Top = WidthIndex
NewCheckBox.Width = .GetWidth(i)
NewCheckBox.Height = .GetLength(i) + 5
WidthIndex = WidthIndex + .GetLength(i)
NewCheckBox.Font = .GetFont(i)
NewCheckBox.ForeColor = .GetColor(i)
NewCheckBox.Value = True
NewCheckBox.Caption = "CheckBox" & i
End With
On Error Resume Next
With TempForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Sub Checkbox" & i & "_Change()"
.InsertLines Line + 2, " if ActiveChart.SeriesCollection(" & i & ").Format.Line.visible = True then"
.InsertLines Line + 3, ""
.InsertLines Line + 4, "ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= False"
.InsertLines Line + 5, "exit sub"
.InsertLines Line + 6, "else ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= True"
.InsertLines Line + 7, ""
.InsertLines Line + 8, "exit sub"
.InsertLines Line + 9, "end if"
.InsertLines Line + 10, "End Sub"
End With
Next i
VBA.UserForms.Add(TempForm.Name).Show
'

 

[AlphaFrog]

Remove or comment out the On Error Resume Next which may be hiding an error.

Explicitly name each Checkbox added (in red) so they are the exact same names as you write procedures for.

For i = iCount To 1 Step -1
Set NewCheckBox = TempForm.Designer.Controls _
.Add("forms.CheckBox.1")
With oChart.TrendLegend
[COLOR="Red"]NewCheckBox.Name = "CheckBox" & i[/COLOR]
NewCheckBox.Caption = i & " " & .GetText(i)
NewCheckBox.Left = 1
NewCheckBox.Top = WidthIndex
NewCheckBox.Width = .GetWidth(i)
NewCheckBox.Height = .GetLength(i) + 5
WidthIndex = WidthIndex + .GetLength(i)
NewCheckBox.Font = .GetFont(i)
NewCheckBox.ForeColor = .GetColor(i)
NewCheckBox.Value = True
NewCheckBox.Caption = "CheckBox" & i
End With
[COLOR="Green"]' On Error Resume Next[/COLOR]
With TempForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Sub Checkbox" & i & "_Change()"
.InsertLines Line + 2, " if ActiveChart.SeriesCollection(" & i & ").Format.Line.visible = True then"
.InsertLines Line + 3, ""
.InsertLines Line + 4, "ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= False"
.InsertLines Line + 5, "exit sub"
.InsertLines Line + 6, "else ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= True"
.InsertLines Line + 7, ""
.InsertLines Line + 8, "exit sub"
.InsertLines Line + 9, "end if"
.InsertLines Line + 10, "End Sub"
End With
Next i
VBA.UserForms.Add(TempForm.Name).Show
'

Does the code create the _Change procedures and if yes, are those procedures triggered when you change the checkboxes?

Forum Tip:
It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

 

[bigt95nt]

I added the line of code "NewCheckBox.Name = "CheckBox" & i" , but it has no effect. when program runns form appears for a second then disapears, but the form can be found in the project window.

 

[AlphaFrog]

Based on your description, it seems the code is working at least up to the point where the new form is displayed, and then something happens after it is displayed.

Can you post all your code that is after...
VBA.UserForms.Add(TempForm.Name).Show

'

 

[bigt95nt]

VBA.UserForms.Add(TempForm.Name).Show is the last line of code in the sub Like I mentioned that if you comment out the code modification, the form appears on the sheet with the correct number of CheckBox Controls, but with no functionality!

 

[AlphaFrog]

when program runns form appears for a second then disapears, but the form can be found in the project window.

...the form appears on the sheet with the correct number of CheckBox Controls, but with no functionality!

I'm confused?

 

[bigt95nt]

Observation 1: When the Code posted below is NOT commented out the UserForm will NOT show on the Sheet; however, it does show up in the project explorer window under "Forms" and if you select "RUN USER FORM"
the Userform appears on the Sheet with all CheckBox Controls each of which function correctly.

Observation 2: When the Code posted below IS commented out, the form DOES appear on the Sheet compleet with all CheckBox Controls, but controls do not function.

I would assume that there is some problem with the code below, but do not know what

I hope I have done a better job of stating the problem

TempForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Sub Checkbox" & i & "_Change()"
.InsertLines Line + 2, " if ActiveChart.SeriesCollection(" & i & ").Format.Line.visible = True then"
.InsertLines Line + 3, "" .InsertLines Line + 4, "ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= False"
.InsertLines Line + 5, "exit sub"
.InsertLines Line + 6, "else ActiveChart.SeriesCollection(" & i & ").Format.Line.visible= True""
.InsertLines Line + 7, " .InsertLines Line + 8, "exit sub"
.InsertLines Line + 9, "end if"
.InsertLines Line + 10, "End Sub"
End With

 

[AlphaFrog]

Give this a try...

' On Error Resume Next
With TempForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, ""
.InsertLines Line + 2, "Sub Checkbox" & i & "_Change()"
.InsertLines Line + 3, "    With ActiveChart.SeriesCollection(" & i & ").Format.Line"
.InsertLines Line + 4, "        IIf .Visible = msoCTrue, .Visible = msoFalse, .Visible = msoCTrue"
.InsertLines Line + 5, "    End With"
.InsertLines Line + 6, "End Sub"
End With