Need to link up controls on a userform created at runtime with code

Glory

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
It’s been suggested to me that I use a class module to accomplish this. It makes sense given what I’ve been reading about class modules. But I can’t get off the ground.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Do I start by creating a sub that will act as a method in the class module, which will create new controls on the userform I’m working with?
<o:p> </o:p>
I can’t figure out how to call the sub in the class module. “Call ClassModuleName.ProcedureName” isn’t working?
<o:p> </o:p>
Can anyone offer me any help?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
I’ve managed to get a class module up and running that generates a checkbox when a certain command button is clicked.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Code: 
‘Code in class module “Class1”
 
Public Sub addbox2()
<o:p></o:p>
Set g = UserForm1.Controls.Add("Forms.Checkbox.1")<o:p></o:p>
<o:p></o:p>
g.Name = "CheckBox1"<o:p></o:p>
g.Caption = "Hello"<o:p></o:p>
g.Height = 22<o:p></o:p>
g.Left = 6<o:p></o:p>
g.Top = 12<o:p></o:p>
g.Width = 72<o:p></o:p>
g.Visible = True<o:p></o:p>
<o:p></o:p>
End Sub
<o:p></o:p>
Code: 
'Procedure in userform object module
 
Private Sub CommandButton1_Click()<o:p></o:p>
<o:p></o:p>
Dim ck As New Checkbox<o:p></o:p>
<o:p></o:p>
Set ck = New Checkbox<o:p></o:p>
<o:p></o:p>
ck.addbox2<o:p></o:p>
<o:p></o:p>
End Sub
<o:p></o:p>
I have no idea how to set this checkbox up to run code. Naming the checkbox the same as the sub doesn’t work. I’ve never known any other way to link controls to code.
<o:p></o:p>
Any help would be extremely beneficial.
 
Upvote 0
Code: 
[COLOR="Green"]'Procedure in userform object module[/COLOR]

Private CkBx As clsRTCheckBox

Private Sub CommandButton1_Click()
    
    Dim ck As MSForms.CheckBox
    
    Set ck = Me.Controls.Add("forms.Checkbox.1")
    
    With ck
        .Height = 22
        .Left = 6
        .Top = 12
        .Width = 72
        .Name = "CheckBox1"
        .Caption = "Hello and Goodbye"
    End With

    Set CkBx = New clsRTCheckBox
    CkBx.Init ck

End Sub

Code: 
[COLOR="Green"]' Code in class module “clsRTCheckBox”[/COLOR]

Private WithEvents cb As MSForms.CheckBox

Public Sub Init(ByVal CBox As MSForms.CheckBox)
    Set cb = CBox
End Sub

Private Sub cb_Click()

    If cb.Value = True Then
        MsgBox "Hello!"
    Else
        MsgBox "Goodbye!"
    End If
    
End Sub
 
Upvote 0
That's much more than I hoped I'd receive.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Any idea why this is failing? (Object or with block variable not set)<o:p></o:p>
<o:p></o:p>
Code: 
[COLOR=black][FONT=Verdana]'userform2 module code<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private checker2, mp1 As Class2<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub UserForm_Initialize()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Dim checker1 As MSForms.CheckBox<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set checker1 = Me.Controls.Add("forms.Checkbox.1")<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   With checker1<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Height = 22<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Left = 6<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Top = 12<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Width = 72<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Name = "CheckBox1"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Caption = "CheckBox1"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   End With<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set checker2 = New Class2<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   checker2.Init checker1<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set mp = Me.Controls.Add("forms.Multipage.1")<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   With mp<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Height = 40<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Left = 6<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Top = 40<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Width = 100<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       .Pages.Remove (1)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   End With<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set mp1 = New Class2<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mp1.Init2 mp<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]‘class2 module code<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private WithEvents cb As MSForms.CheckBox<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private WithEvents Mult As MSForms.Multipage<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Sub Init(ByVal CBox As MSForms.CheckBox)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set cb = CBox<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Sub Init2(ByVal Multi As MSForms.Multipage)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set Mult = Multi<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub cb_Click()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   If cb.Value = True Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   MsgBox Mult.Pages.Count<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       'With mult<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       '    .Pages.Controls.Add<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       'End With<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]
<o:p></o:p>
<o:p></o:p>
When I try using the click event to add a page to a multipage object that's already present on the userform from design-time, the server disconnects from its clients; eg:<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
'class2 module code<o:p></o:p>
Private WithEvents cb As MSForms.CheckBox<o:p></o:p>
Private WithEvents mult As MSForms.Multipage<o:p></o:p>
Public Sub Init(ByVal CBox As MSForms.CheckBox)<o:p></o:p>
   Set cb = CBox<o:p></o:p>
End Sub<o:p></o:p>
Private Sub cb_Click()<o:p></o:p>
   If cb.Value = True Then<o:p></o:p>
   'MsgBox mult.Pages.Count<o:p></o:p>
       'With mult<o:p></o:p>
       '    .Pages.Add<o:p></o:p>
       'End With<o:p></o:p>
   Userform2.Multipage.Pages.Add<o:p></o:p>
   End If<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p>Thanks for your help so far. </o:p>
 
Upvote 0
I've tried making "Mult" public, and declaring "Init" as Static, thinking I needed to preserve the information between calls.

Code: 
Public Static Sub Init2(ByVal Multi As MSForms.MultiPage)
    Set Mult = Multi
    If Not Mult Is Nothing Then: MsgBox Mult.Name
End Sub

This returns the name of the object ("Multipage1") in "Init", but not when I put it into the checkbox click event. That's where the object error occurs, and nothing I do seems to stop that from happening.
 
Upvote 0
This is really weird. I can link code to the multipage object that's created at runtime, but I can't reference the object variable "Mult" from within the class module's checkbox click event.

eg:

Code: 
Public Sub Mult_Click(ByVal Index As Long)
MsgBox "Hey"
End Sub

What gives?
 
Upvote 0
I understand what you were trying to do with the Static variable, but that's not how classes work.

Each time you use the New keyword with a class; e.g.
Set checker2 = New Class2
Set mp1 = New Class2
...that creates a New instance of that class. In this example, all the variables in checker2.class2 are completely different from all the variables in mp1.class2. In simplistic terms, it's as if they are two separate macros in separate modules. They don't share values\objects unless you explicitly pass them as I've done in the code below using...
checker2.Init checker1, mp

The code below functions, but it's probably not the best way to construct your code (everything in one class). Ideally, you may want to set up a class for the CheckBox events and a seperate class for the MultiPage events. It all depends on what you are ultimately attempting to do (which I really have know idea).

Here's a good primer on Classes you may find useful.

Alternatively, have you considered just hiding\unhiding objects as needed. Programatically, that would be a lot easier.


Code: 
'Procedure in userform object module

Private checker2 As Class2, mp1 As Class2

Private Sub UserForm_Initialize()

   Dim checker1 As MSForms.CheckBox
   Set checker1 = Me.Controls.Add("forms.Checkbox.1")

   With checker1
       .Height = 22
       .Left = 6
       .Top = 12
       .Width = 72
       .Name = "CheckBox1"
       .Caption = "CheckBox1"
   End With
   
   Set mp = Me.Controls.Add("forms.Multipage.1")

   With mp
       .Height = 40
       .Left = 6
       .Top = 40
       .Width = 100
       .Pages.Remove (1)
   End With
   
   Set checker2 = New Class2
   checker2.Init checker1[COLOR="Red"], mp[/COLOR]
   
   Set mp1 = New Class2
   'mp1.Init1 mp

End Sub

Code: 
'class2 module code

Private WithEvents cb As MSForms.CheckBox
Private WithEvents mult As MSForms.MultiPage

Public Sub Init(ByVal CBox As MSForms.CheckBox, [COLOR="Red"]ByVal MPage As MSForms.MultiPage[/COLOR])
   Set cb = CBox
   [COLOR="Red"]Set mult = MPage[/COLOR]
End Sub

Private Sub cb_Click()
   If cb.Value = True Then
        [COLOR="Red"]mult[/COLOR].Pages.Add
        ' UserForm2.Controls("MultiPage1").Pages.Add
   End If
End Sub

'Public Sub Init1(ByVal MPage As MSForms.MultiPage)
'    Set mult = MPage
'End Sub

Private Sub mult_Click(ByVal Index As Long)
    If Index > 0 Then MsgBox "Hello!"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA linking Class(es) and Userforms
Replies
4
Views
537
Jaafar Tribak
Jaafar Tribak
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
113
6StringJazzer
6StringJazzer
F
  • Solved
Activating dynamically created checkboxes
Replies
2
Views
2K
Bosquedeguate
Bosquedeguate
H
  • Question Question
How to login with username and password via Microsoft Edge in VBA code
Replies
1
Views
1K
healeydm
H
J
  • Question Question
Can I send event to a class from a UserForm in Excel vba?
Replies
1
Views
541
Jo Helge Rorvik
J

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,921
Latest member
BBQKING

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top