VBA application terminates when Control events are added programaticly

[bigt95nt]

The code in my module creates a UserForm with n Number of CheckBox Controls and adds the proper event handlers for the CheckBoxes 1 to n. The code that creates the event handlers is listed below. NOTE I have not listed the entire module for brevity.

Initialy, I thought the main issue was that the UserForm was not being displayed when the code that adds the event executes; however, the reason why the UserForm doesn’t display when the code listed below executes is that the entire application terminates!

Before I added this routine the application worked fine. However, when code below executes, all of the variables go out of scope. If I choose any of the application's functions I get "Subscript out of Range" On the other hand, if the code below is "Commented Out" everything executes fine. All of the applications functions work fine and the UserForm displays with all of the correct controls. The controls themselves do not function because the code that ads their functionality is not executing. These results hold no matter where the UserForm.show is executed from.

Thank You,
Tony

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
end sub

[mikerickson]

One way to handle controls created at run-tim is to use Custom Classes.

Code like this in the userform's code module will create a run-time checkbox.
Private Sub CommandButton1_Click()
Dim newControl As clsRunTimeCheckbox

Set newControl = New clsRunTimeCheckbox
Set newControl.madeCheckBox = Me.Controls.Add("forms.CheckBox.1")

Rem fomat new checkbox
With newControl.madeCheckBox
.Height = 20: .Width = 75
.Top = 25 * newControls.Count + 5
.Left = 20 * newControls.Count + 5
.Caption = .Name
End With

Rem add new instance of custom class to collection

newControls.Add Item:=newControl, Key:=newControl.madeCheckBox.Name

Rem clean-up
Set newControl = Nothing
End Sub

In a Class module, this will give event code to the newly created custom object (and the checkbox that is a property of that object). Be sure to name the Class module clsRunTimeCheckBox. The event code uses the madeCheckBox.Parent to refer to the userform. (In that module, the Me codeword refers to the custom object, not the userform.)
Public WithEvents madeCheckBox As MSForms.CheckBox

Private Sub madeCheckBox_Click()
With madeCheckBox
.Parent.Caption = .Name & " is " & .Value
End With
End Sub

[bigt95nt]

Im a bit lost as to where the object newControls is defined in the code below?




Private Sub CommandButton1_Click()

Dim newControl As clsRunTimeCheckbox


Set newControl = New clsRunTimeCheckbox
Set newControl.madeCheckBox = Me.Controls.Add("forms.CheckBox.1")

Rem fomat New checkbox
With newControl.madeCheckBox
.Height = 20: .Width = 75
.Top = 25 * newControls.Count + 5
.Left = 20 * newControls.Count + 5
.Caption = .Name
End With

Rem add New instance of custom class To collection

newControls.Add Item:=newControl, Key:=newControl.madeCheckBox.Name

Rem clean-up
Set newControl = Nothing
End Sub

[mikerickson]

Please use code tags when posting code.

About the collection newControls, I forgot to post it as a module wide variable in the userform's code module. Here's the complete userform code module from my test workbook, including the clean up in the Userform_Terminate event.

Option Explicit

Dim newControls As Collection

Private Sub CommandButton1_Click()
Dim newControl As clsRunTimeCheckbox

Rem create new instance of custom class clsRunTimeCheckbox
Set newControl = New clsRunTimeCheckbox

Rem add run-time checkbox to userform and assign that chkbox to the madeCheckBox property of the instance
Set newControl.madeCheckBox = Me.Controls.Add("forms.CheckBox.1")

Rem position new checkbox and give it a caption
With newControl.madeCheckBox
.Height = 20: .Width = 75
.Top = 25 * newControls.Count + 5
.Left = 20 * newControls.Count + 5
.Caption = .Name
End With

Rem place new instance of custom class in collection
newControls.Add Item:=newControl, Key:=newControl.madeCheckBox.Name

Rem clean-up
Set newControl = Nothing
End Sub

Private Sub UserForm_Initialize()
Set newControls = New Collection
End Sub

Private Sub UserForm_Terminate()
Dim oneControl As Object
For Each oneControl In newControls
Set oneControl = Nothing
Next oneControl
Set newControls = Nothing
End Sub

[bigt95nt]

Got it! Thank you!

[bigt95nt]

I have played with the code you supplied and I have got a fairly good Idea of how it works.

I am now adapting your code example to my app. In your code the the button_click event is what controls when how the check box is created and after the adapttation this functionality will be controled my procedure.

Intially, there is a blank UserForm1 with the following code is placed as eventHandlers


Private Sub UserForm_Initialize()
Set newControls = New Collection
End Sub

Private Sub UserForm_Terminate()
Dim oneControl As Object
For Each oneControl In newControls
Set oneControl = Nothing
Next oneControl
Set newControls = Nothing
End Sub

'The next code is the actual creation of the checkBox in my module. In 'your example this is being done from whithin the form itself and the form 'is being refrenced via the .me operatior and in mine I refrence the form by 'its name "UserForm1"


Sub MySUb()
Dim newControl As clsRunTimeCheckBox
Dim TempForm As Object 'VBComponent
Set newControl = New clsRunTimeCheckBox

Set TempForm = ThisWorkbook.VBProject. _ ' This Works Fine
VBComponents("UserForm1") 'vbext_ct_MSForm

Set newControl.madeCheckBox =TempForm.Controls.Add'("forms.CheckBox.1")'This Fails

end sub


There seems to be a multiplicity of ways to create various controlls, and I guess that each method is "context" dependent. This is where I am comming short when it comes to forms.

Example out of Walkenbach:


Set NewButton = TempForm.Designer.Controls _
.Add("forms.CommandButton.1")


Could you point me in the right direction in inturpiting context?

[mikerickson]

Before discussing userforms and the difference between run time controls from my code and design time controls from Walkenbach's, it really is important that the code you post on this board be wrapped.

To wrap code properly, one can either select the text to be wrapped and click on the VBA button above the message dialog box, or they can type [ V B A ] (with no spaces) before the test to be wrapped and [ / V B A ] after. (similarly no spaces)

[bigt95nt]

Got the VBA Tags NOW Thanks!

I modified the previous post to include the VBA tags and will use them in all future post.

Tony

[mikerickson]

There is a difference between a userform that the user sees and its VBComponent.

The code from Walkenbach modifies the Designer property of a userform's vbComponent, at design time, to permenantly add a checkbox to that userform.

The code that I posted modifies the userform that the user sees to temporarily add a checkbox.
This modification of your code should work.
Sub MySUb()
Dim newControl As clsRunTimeCheckBox
Dim TempForm As Object ' alternatly Dim TempForm as UserForm1
Set newControl = New clsRunTimeCheckBox

Set TempForm = UserForm1

Set newControl.madeCheckBox = TempForm.Controls.Add("forms.CheckBox.1")

TempForm.Show
End Sub

[bigt95nt]

Below is my adaptation of the code you provided. I am able to set all properties , but not able to set the . value property of CheckBox instance.


'Create a CheckBox for Each series in the Active Chart
For i = 1 To iCount
'Create New CheckBox and add it to the Colletction
Call MakeCheckBoxObjext

' Set Parameters For New CheckBox
With newControls.Item(i).madeCheckBox
.Height = 20: .Width = 75
.Top = WidthIndex
.Left = 1
.Caption = oChart.TrendLegend.GetText(i)
.ForeColor = oChart.TrendLegend.GetColor(i)
.Value = True'<-- This Line Fails All other properties set ok
End With
newControls.Item(i).Index = i
WidthIndex = WidthIndex + 15
Next i

With UserForm1
.Left = 1
.Top = 100
.Width = 80
.Height = newControls.Count * 20 + 5
End With
UserForm1.Show
End Sub






Sub MakeCheckBoxObjext()
Dim NewControl As New clsRunTimeCheckBox
Set NewControl.madeCheckBox = UserForm1.Controls.Add("forms.CheckBox.1")
newControls.Add Item:=NewControl, Key:=NewControl.madeCheckBox.Name
End Sub



In addition here is the modifications to the clsRunTimeCheckBox



Public WithEvents madeCheckBox As MSForms.CheckBox
Public Index As Integer

Private Sub madeCheckBox_Click()
If ActiveChart.SeriesCollection(Index).Format.Line.Visible = msoFalse Then
ActiveChart.SeriesCollection(Index).Format.Line.Visible = msoTrue
Exit Sub
Else
ActiveChart.SeriesCollection(Index).Format.Line.Visible = msoFalse
End If

End Sub


THANK YOU

TONY