Creating a public array in UserForm at runtime and referencing each component in a class module

N

NBahar718

New Member
Joined
Feb 10, 2022
Messages
7
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi. I am a new Excel VBA user and need help.

I have created a Userform named "ChromatographySystemChoice" which is something like the image below
Query Pic 1.png

Ignoring the frame control, I have added a textbox where I get a numeric value of the number of samples and click on "Add Sample Details" to fetch its value.
It displays something like this :
Query Pic 2.png

UserForm code is as follows :
VBA Code: 
Option Explicit
Option Base 1

Dim objMyEventClass As New Class1

Public NumberOfSamplesToAnalyze As Long
Public SampleIDNumber As Long

Private Sub AddSampleDetails_Click()
    NumberOfSamplesToAnalyze = NumberOfSamples.Value
    
    ReDim txtB1(1 To NumberOfSamplesToAnalyze)
    ReDim txtB2(1 To NumberOfSamplesToAnalyze)
    ReDim txtB3(1 To NumberOfSamplesToAnalyze)
    ReDim txtB4(1 To NumberOfSamplesToAnalyze)
    ReDim txtB5(1 To NumberOfSamplesToAnalyze)
    ReDim txtB6(1 To NumberOfSamplesToAnalyze)
    ReDim chk1(1 To NumberOfSamplesToAnalyze)
    ReDim chk2(1 To NumberOfSamplesToAnalyze)
    ReDim chk3(1 To NumberOfSamplesToAnalyze)
    ReDim chk4(1 To NumberOfSamplesToAnalyze)
    ReDim chk5(1 To NumberOfSamplesToAnalyze)
    
    Dim Lbl(1 To 11) As Control
    Dim LabelCaption
    Dim j As Integer, i As Integer
    
    LabelCaption = Array("Batch No.", "Run No.", "Tag", "Volume (mL)", "From Fraction", "To Fraction", "Load", "Standard", "Pool", "Fraction", "Other")
    
    Set Lbl(1) = Me.Controls.Add("Forms.Label.1")
        
        With Lbl(1)
            .Name = "Label1"
            .Height = 20
            .Width = 100
            .Left = 20
            .Top = NumberOfSamples.Top + NumberOfSamples.Height + 10
            .Caption = LabelCaption(1)
        End With
        
    
    For j = 2 To 11
        Set Lbl(j) = Me.Controls.Add("Forms.Label.1")
        
        With Lbl(j)
            .Name = "Label" & j
            .Height = 20
            .Width = 35
            .Left = Lbl(j - 1).Left + Lbl(j - 1).Width + 20
            .Top = NumberOfSamples.Top + NumberOfSamples.Height + 10
            .Caption = LabelCaption(j)
        End With
    Next j
    
    For i = 1 To NumberOfSamplesToAnalyze
    
        Set txtB1(i) = Me.Controls.Add("Forms.TextBox.1")
        
            With txtB1(i)
                .Name = "TextBox" & i & "1"
                .Height = 20
                .Width = 100
                .Left = 20
                .Top = Lbl(1).Top + (25 * i)
            End With

        Set txtB2(i) = Me.Controls.Add("Forms.TextBox.1")

            With txtB2(i)
                .Name = "TextBox" & i & "2"
                .Height = 20
                .Width = 35
                .Left = txtB1(i).Left + txtB1(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set txtB3(i) = Me.Controls.Add("Forms.TextBox.1")

            With txtB3(i)
                .Name = "TextBox" & i & "3"
                .Height = 20
                .Width = 35
                .Left = txtB2(i).Left + txtB2(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set txtB4(i) = Me.Controls.Add("Forms.TextBox.1")

            With txtB4(i)
                .Name = "TextBox" & i & "4"
                .Height = 20
                .Width = 35
                .Left = txtB3(i).Left + txtB3(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set txtB5(i) = Me.Controls.Add("Forms.TextBox.1")

            With txtB5(i)
                .Name = "TextBox" & i & "5"
                .Height = 20
                .Width = 35
                .Left = txtB4(i).Left + txtB4(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set txtB6(i) = Me.Controls.Add("Forms.TextBox.1")

            With txtB6(i)
                .Name = "TextBox" & i & "6"
                .Height = 20
                .Width = 35
                .Left = txtB5(i).Left + txtB5(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set chk1(i) = Me.Controls.Add("Forms.CheckBox.1")

            With chk1(i)
                .Name = "CheckBox" & i & "1"
                .Height = 20
                .Width = 35
                .Left = txtB6(i).Left + txtB6(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set objMyEventClass.cbLODEvents = chk1(i)

        Set chk2(i) = Me.Controls.Add("Forms.CheckBox.1")

            With chk2(i)
                .Name = "CheckBox" & i & "2"
                .Height = 20
                .Width = 35
                .Left = chk1(i).Left + chk1(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set objMyEventClass.cbSTDEvents = chk2(i)
            
        Set chk3(i) = Me.Controls.Add("Forms.CheckBox.1")

            With chk3(i)
                .Name = "CheckBox" & i & "3"
                .Height = 20
                .Width = 35
                .Left = chk2(i).Left + chk2(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set objMyEventClass.cbPOOLEvents = chk3(i)
            
        Set chk4(i) = Me.Controls.Add("Forms.CheckBox.1")

            With chk4(i)
                .Name = "CheckBox" & i & "4"
                .Height = 20
                .Width = 35
                .Left = chk3(i).Left + chk3(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set objMyEventClass.cbFRACEvents = chk4(i)
            
        Set chk5(i) = Me.Controls.Add("Forms.CheckBox.1")

            With chk5(i)
                .Name = "CheckBox" & i & "5"
                .Height = 20
                .Width = 35
                .Left = chk4(i).Left + chk4(i).Width + 20
                .Top = Lbl(1).Top + (25 * i)
            End With
            
        Set objMyEventClass.cbOTHEREvents = chk5(i)
                                  
    Next i
    
    SampleIDNumber = i - 1

End Sub

Private Sub CancelChromatographySystemChoice_Click()
    Unload ChromatographySystemChoice
End Sub


Private Sub OKChromatographySystemChoice_Click()
    Unload ChromatographySystemChoice
End Sub

I have created the following Class Module "Class 1" to get the object instances to work certain way.

VBA Code: 
Option Base 1
Option Explicit

Public WithEvents cbLODEvents As MSForms.CheckBox
Public WithEvents cbSTDEvents As MSForms.CheckBox
Public WithEvents cbPOOLEvents As MSForms.CheckBox
Public WithEvents cbFRACEvents As MSForms.CheckBox
Public WithEvents cbOTHEREvents As MSForms.CheckBox

Dim SampleID As Integer

Public Sub cbLODEvents_Click()

    SampleID = ChromatographySystemChoice.SampleIDNumber
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "5").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "2").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "3").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "4").Value = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "3").Value = "LOD"
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").BackColor = &H80000016
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").BackColor = &H80000016

End Sub

Public Sub cbSTDEvents_Click()

    SampleID = ChromatographySystemChoice.SampleIDNumber
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "5").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "1").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "3").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "4").Value = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "3").Value = "STD"
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").BackColor = &H80000016
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").BackColor = &H80000016

End Sub

Public Sub cbPOOLEvents_Click()

    SampleID = ChromatographySystemChoice.SampleIDNumber
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "5").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "1").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "2").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "4").Value = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "3").Value = ""
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").Enabled = True
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").BackColor = vbWhite
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").Enabled = True
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").BackColor = vbWhite

End Sub

Public Sub cbFRACEvents_Click()

    SampleID = ChromatographySystemChoice.SampleIDNumber
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "5").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "1").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "2").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "3").Value = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "3").Value = ""
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").BackColor = &H80000016
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").BackColor = &H80000016

End Sub

Public Sub cbOTHEREvents_Click()

    SampleID = ChromatographySystemChoice.SampleIDNumber
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "1").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "2").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "3").Value = False
    ChromatographySystemChoice.Controls("CheckBox" & SampleID & "4").Value = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "3").Value = ""
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "5").BackColor = &H80000016
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").Enabled = False
    ChromatographySystemChoice.Controls("TextBox" & SampleID & "6").BackColor = &H80000016

End Sub

However, when I run the Userform, the class property gets manipulated only in the last line of the chosen number of samples.

I need to find a way to fetch a variable or some sort of an array at runtime (after I add the number and click on "Add Sample Details", so that every object instance gets attached to the class module and its events. Any help would be appreciated.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi @NBahar718, welcome to MrExcel.

It looks like your issue is caused by not instantiating your Class1 object. The variable declaration your code is using provides just one instance of Class1.
Apparently you distinguish five different functions of a CheckBox and include all five in the same event handler class. There's nothing wrong with that, nevertheless each CheckBox needs its own instance of the event handler. In each pass of your loop your code is addressing the same (i.e. the one and only ...) instance, so that way its properties are changed each time. The values assigned during the last iteration remain, resulting in only the last five added CheckBoxes getting their own event handler.

So this declaration
VBA Code: 
Dim objMyEventClass As New Class1

could be replaced by an array declaration (note the parantheses) like
VBA Code: 
Dim objMyEventClass() As New Class1

but the array would then have to be resized later on, e.g.
VBA Code: 
Redim objMyEventClass(8)
to accommodate all instances of Class1, eight in the above example.


Since we not always know beforehand how many instances of a class are needed, I prefer to use a collection instead:
VBA Code: 
Private AllEventClassInstances  As Collection

In addition, I personally prefer to 'New' objects only when they are needed, in this case that's when the UserForm needs to be initialized:
VBA Code: 
Private Sub UserForm_Initialize()
    Set AllEventClassInstances = New Collection
End Sub

The moment a control is added to the UserForm a new instance of the event handler is needed. When the properties of that instance are set (and perhaps some additional methods are invoked), that particular instance can be added to the collection to keep that handler "alive":
VBA Code: 
    Set NextChkBx = Me.Controls.Add("Forms.CheckBox.1")
    ' ...
    ' ...
    Set EventHandler = New Class1               ' create new instance
    Set EventHandler.cbLODEvents = NextChkBx    ' assign property 1
    EventHandler.SampleID = i                   ' assign property 2
    Set EventHandler.HostForm = Me              ' assign property 3
    AllEventClassInstances.Add EventHandler     ' add this instance to the collection


FYI, some sidenotes on your code.
  • The event handler in your case needs immutable data which is stored in a public variable within the UserForm class, called SampleIDNumber. This variable is read every time an event is intercepted. It's more efficient if the code transfers the value of this variable only once, which is at the time the event handler instance is created or immediately shortly afterwards (property 2 in the code snippet above).
  • The event handler in your case affects directly some controls on the UserForm. Your code is using the UserForm's class name for that, which implies that you're using the default instance of the UserForm class (the latter is possible, because the hidden PredeclaredID attribute of a UserForm's class module is set to True by default, unlike any other class module, where this attribute is set to False). If at some point you decide to also New'ing up the UserForm (which is recommended imo), the event handler will no longer work. I therefore suggest introducing a 3rd property within Class1:
VBA Code: 
Public HostForm As Object
  • Your code is responding on the Click event of each of the CheckBoxes, after which the status of other CheckBoxes is changed at the same time. As a result, the code from an intercepted event itself causes a new event of another CheckBox. This can cause unexpected or unwanted Click / Tick behavior of the CheckBoxes. However, this is something you should try for yourself.
  • Finally, the procedure responsible for the dynamic construction of the UserForm contains quite a bit of repetitive code. You might consider introducing a new class module, whose object it represents is responsible for creating new TextBoxes and one for creating CheckBoxes. In this way the code is reduced considerably and therefore even more readable and managable.
Hope this helps.

This goes in your Class1 module:
VBA Code: 
Option Base 1
Option Explicit

Public WithEvents cbLODEvents As MSForms.CheckBox
Public WithEvents cbSTDEvents As MSForms.CheckBox
Public WithEvents cbPOOLEvents As MSForms.CheckBox
Public WithEvents cbFRACEvents As MSForms.CheckBox
Public WithEvents cbOTHEREvents As MSForms.CheckBox

Public SampleID As Integer
Public HostForm As Object

Public Sub cbLODEvents_Click()
    With HostForm
        .Controls("CheckBox" & SampleID & "5").Value = False
        .Controls("CheckBox" & SampleID & "2").Value = False
        .Controls("CheckBox" & SampleID & "3").Value = False
        .Controls("CheckBox" & SampleID & "4").Value = False
        .Controls("TextBox" & SampleID & "3").Value = "LOD"
        .Controls("TextBox" & SampleID & "5").Enabled = False
        .Controls("TextBox" & SampleID & "5").BackColor = &H80000016
        .Controls("TextBox" & SampleID & "6").Enabled = False
        .Controls("TextBox" & SampleID & "6").BackColor = &H80000016
    End With
End Sub

Public Sub cbSTDEvents_Click()
    With HostForm
        .Controls("CheckBox" & SampleID & "5").Value = False
        .Controls("CheckBox" & SampleID & "1").Value = False
        .Controls("CheckBox" & SampleID & "3").Value = False
        .Controls("CheckBox" & SampleID & "4").Value = False
        .Controls("TextBox" & SampleID & "3").Value = "STD"
        .Controls("TextBox" & SampleID & "5").Enabled = False
        .Controls("TextBox" & SampleID & "5").BackColor = &H80000016
        .Controls("TextBox" & SampleID & "6").Enabled = False
        .Controls("TextBox" & SampleID & "6").BackColor = &H80000016
    End With
End Sub

Public Sub cbPOOLEvents_Click()
    With HostForm
        .Controls("CheckBox" & SampleID & "5").Value = False
        .Controls("CheckBox" & SampleID & "1").Value = False
        .Controls("CheckBox" & SampleID & "2").Value = False
        .Controls("CheckBox" & SampleID & "4").Value = False
        .Controls("TextBox" & SampleID & "3").Value = ""
        .Controls("TextBox" & SampleID & "5").Enabled = True
        .Controls("TextBox" & SampleID & "5").BackColor = vbWhite
        .Controls("TextBox" & SampleID & "6").Enabled = True
        .Controls("TextBox" & SampleID & "6").BackColor = vbWhite
    End With
End Sub

Public Sub cbFRACEvents_Click()
    With HostForm
        .Controls("CheckBox" & SampleID & "5").Value = False
        .Controls("CheckBox" & SampleID & "1").Value = False
        .Controls("CheckBox" & SampleID & "2").Value = False
        .Controls("CheckBox" & SampleID & "3").Value = False
        .Controls("TextBox" & SampleID & "3").Value = ""
        .Controls("TextBox" & SampleID & "5").Enabled = False
        .Controls("TextBox" & SampleID & "5").BackColor = &H80000016
        .Controls("TextBox" & SampleID & "6").Enabled = False
        .Controls("TextBox" & SampleID & "6").BackColor = &H80000016
    End With
End Sub

Public Sub cbOTHEREvents_Click()
    With HostForm
        .Controls("CheckBox" & SampleID & "1").Value = False
        .Controls("CheckBox" & SampleID & "2").Value = False
        .Controls("CheckBox" & SampleID & "3").Value = False
        .Controls("CheckBox" & SampleID & "4").Value = False
        .Controls("TextBox" & SampleID & "3").Value = ""
        .Controls("TextBox" & SampleID & "5").Enabled = False
        .Controls("TextBox" & SampleID & "5").BackColor = &H80000016
        .Controls("TextBox" & SampleID & "6").Enabled = False
        .Controls("TextBox" & SampleID & "6").BackColor = &H80000016
    End With
End Sub


This goes in the UserForm module:
VBA Code: 
Option Explicit
Option Base 1

Private NumberOfSamplesToAnalyze    As Long
Private AllEventClassInstances      As Collection


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


Private Sub AddSampleDetails_Click()

    NumberOfSamplesToAnalyze = CLng(NumberOfSamples.Value)
   
    Dim Lbl(1 To 11) As MSForms.Control
    Dim LabelCaption
    Dim j As Integer, i As Integer
   
    LabelCaption = Array("Batch No.", "Run No.", "Tag", "Volume (mL)", "From Fraction", "To Fraction", "Load", "Standard", "Pool", "Fraction", "Other")
   
    Set Lbl(1) = Me.Controls.Add("Forms.Label.1")
    With Lbl(1)
        .Name = "Label1"
        .Height = 20
        .Width = 100
        .Left = 20
        .Top = NumberOfSamples.Top + NumberOfSamples.Height + 10
        .Caption = LabelCaption(1)
    End With
   
    For j = 2 To 11
        Set Lbl(j) = Me.Controls.Add("Forms.Label.1")
        With Lbl(j)
            .Name = "Label" & j
            .Height = 20
            .Width = 35
            .Left = Lbl(j - 1).Left + Lbl(j - 1).Width + 20
            .Top = NumberOfSamples.Top + NumberOfSamples.Height + 10
            .Caption = LabelCaption(j)
        End With
    Next j
   
    Dim PrevTbx As MSForms.Control
    Dim NextTbx As MSForms.Control
    For i = 1 To NumberOfSamplesToAnalyze
   
        Set NextTbx = Me.Controls.Add("Forms.TextBox.1")
        With NextTbx
            .Name = "TextBox" & i & "1"
            .Height = 20
            .Width = 100
            .Left = 20
            .Top = Lbl(1).Top + (25 * i)
        End With

        Set PrevTbx = NextTbx
        Set NextTbx = Me.Controls.Add("Forms.TextBox.1")
        With NextTbx
            .Name = "TextBox" & i & "2"
            .Height = 20
            .Width = 35
            .Left = PrevTbx.Left + PrevTbx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
           
        Set PrevTbx = NextTbx
        Set NextTbx = Me.Controls.Add("Forms.TextBox.1")
        With NextTbx
            .Name = "TextBox" & i & "3"
            .Height = 20
            .Width = 35
            .Left = PrevTbx.Left + PrevTbx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
           
        Set PrevTbx = NextTbx
        Set NextTbx = Me.Controls.Add("Forms.TextBox.1")
        With NextTbx
            .Name = "TextBox" & i & "4"
            .Height = 20
            .Width = 35
            .Left = PrevTbx.Left + PrevTbx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
           
        Set PrevTbx = NextTbx
        Set NextTbx = Me.Controls.Add("Forms.TextBox.1")
        With NextTbx
            .Name = "TextBox" & i & "5"
            .Height = 20
            .Width = 35
            .Left = PrevTbx.Left + PrevTbx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
           
        Set PrevTbx = NextTbx
        Set NextTbx = Me.Controls.Add("Forms.TextBox.1")
        With NextTbx
            .Name = "TextBox" & i & "6"
            .Height = 20
            .Width = 35
            .Left = PrevTbx.Left + PrevTbx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
           
        Set PrevTbx = NextTbx
       
        Dim PrevChkBx       As MSForms.Control
        Dim NextChkBx       As MSForms.Control
        Dim EventHandler As Class1

        Set NextChkBx = Me.Controls.Add("Forms.CheckBox.1")
        With NextChkBx
            .Name = "CheckBox" & i & "1"
            .Height = 20
            .Width = 35
            .Left = PrevTbx.Left + PrevTbx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
        Set EventHandler = New Class1
        Set EventHandler.cbLODEvents = NextChkBx
        EventHandler.SampleID = i
        Set EventHandler.HostForm = Me
        AllEventClassInstances.Add EventHandler

        Set PrevChkBx = NextChkBx
        Set NextChkBx = Me.Controls.Add("Forms.CheckBox.1")
        With NextChkBx
            .Name = "CheckBox" & i & "2"
            .Height = 20
            .Width = 35
            .Left = PrevChkBx.Left + PrevChkBx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
        Set EventHandler = New Class1
        Set EventHandler.cbSTDEvents = NextChkBx
        EventHandler.SampleID = i
        Set EventHandler.HostForm = Me
        AllEventClassInstances.Add EventHandler

        Set PrevChkBx = NextChkBx
        Set NextChkBx = Me.Controls.Add("Forms.CheckBox.1")
        With NextChkBx
            .Name = "CheckBox" & i & "3"
            .Height = 20
            .Width = 35
            .Left = PrevChkBx.Left + PrevChkBx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
        Set EventHandler = New Class1
        Set EventHandler.cbPOOLEvents = NextChkBx
        EventHandler.SampleID = i
        Set EventHandler.HostForm = Me
        AllEventClassInstances.Add EventHandler

        Set PrevChkBx = NextChkBx
        Set NextChkBx = Me.Controls.Add("Forms.CheckBox.1")
        With NextChkBx
            .Name = "CheckBox" & i & "4"
            .Height = 20
            .Width = 35
            .Left = PrevChkBx.Left + PrevChkBx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
        Set EventHandler = New Class1
        Set EventHandler.cbFRACEvents = NextChkBx
        EventHandler.SampleID = i
        Set EventHandler.HostForm = Me
        AllEventClassInstances.Add EventHandler

        Set PrevChkBx = NextChkBx
        Set NextChkBx = Me.Controls.Add("Forms.CheckBox.1")
        With NextChkBx
            .Name = "CheckBox" & i & "5"
            .Height = 20
            .Width = 35
            .Left = PrevChkBx.Left + PrevChkBx.Width + 20
            .Top = Lbl(1).Top + (25 * i)
        End With
        Set EventHandler = New Class1
        Set EventHandler.cbOTHEREvents = NextChkBx
        EventHandler.SampleID = i
        Set EventHandler.HostForm = Me
        AllEventClassInstances.Add EventHandler
                                 
    Next i
End Sub


Private Sub CancelChromatographySystemChoice_Click()
    Unload ChromatographySystemChoice
End Sub

Private Sub OKChromatographySystemChoice_Click()
    Unload ChromatographySystemChoice
End Sub
 
Upvote 0
Many thanks for the elaborate guidance on the code. Code works fine and I also learned the goods and bads of my previous code. Cheers !
 
Upvote 0
Glad to help and thanks for the feedback (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
116
6StringJazzer
6StringJazzer
P
  • Question Question
System error &H80004005 (-2147467259). Unspecified error
Replies
6
Views
436
jkpieterse
jkpieterse
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
366
Micron
M
S
  • Solved
VBA Populate userform dynamically with custom class Frame
Replies
1
Views
408
saboh12617
S
K
  • Solved
making a userform components attribute names and values a two dimensional array.
Replies
1
Views
408
dmt32
D

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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