Search code examples
excelvbacheckboxdynamictextbox

Dynamic checkbox to enable textbox


I have a problem about dynamic controls. I create a dynamic userform with single frames depending by a number (rngprt in my example dependent by user input). No problem till now. Now I want to enable a textbox (in order to change its value) inside a specific frame when the relative checkbox is click. I used a Class Module (Classe1), but with my code I succeeded only to enable the last textbox of the n-frames when its checkbox is clicked (e.g. if I have 3 frames with 3 textboxes and 3 checkboxes, only the third checkbox it's able to enable the third textbox, the first and the second don't work).

Class Module: Classe1

Option Explicit
Public WithEvents cmbEvent1 As MSForms.CommandButton
Public WithEvents txbEvent1 As MSForms.TextBox
Public WithEvents frmEvent1 As MSForms.Frame
Public WithEvents ckbEvent1 As MSForms.CheckBox

Private Sub cmbEvent1_Click()
UserForm3.Hide
End Sub

Private Sub frmEvent1_Click()
End Sub

Public Sub txbEvent1_Change()
End Sub

Private Sub ckbEvent1_Click()
If UserForm3.Controls("CK" & xx).Value = True Then
UserForm3.Controls("TB" & xx).Enabled = True
End If
End Sub

Module: UserForm3

Option Explicit
Dim cmdB As New Classe1
Dim txtB As New Classe1
Dim chkB As New Classe1
Dim frm As New Classe1
Dim chkBColl As New Collection

Private Sub UserForm_Initialize()
Dim x As Long
Dim c As Variant
Dim cmdB1 As MSForms.CommandButton
Dim frm1 As MSForms.Frame
Dim txtB1 As MSForms.TextBox
Dim chkB1 As MSForms.CheckBox

Set cmdB1 = UserForm3.Controls.Add("Forms.CommandButton.1")
       With cmdB1
            .Name = "OKButton"
            .Caption = "OK"
            .Top = 40 * rngprt
            .Left = 120
            .Width = 40
            .Height = 25
       End With
       Set cmdB.cmbEvent1 = cmdB1

For x = cel.Row To cel.Row + rngprt - 1 '**rngprt is a number from a Module1**

xx = x - cel.Row + 1  '**for progessive name of controls Dim xx as long into the Module1**

Set frm1 = UserForm3.Controls.Add("Forms.Frame.1")
            frm1.Top = 40 * (xx - 1)
            frm1.Left = 10
            frm1.Width = 300
            frm1.Height = 35
            frm1.Name = "FR" & xx

With frm1.Controls

Set txtB1 = .Add("Forms.TextBox.1")
       With txtB1
            .Name = "TB" & xx
            .Top = 10
            .Left = 160
            .Width = 30
            .Height = 15
            .TextAlign = fmTextAlignRight
            .Enabled = False
            .Value=50
       End With
       Set txtB.txbEvent1 = txtB1

     Set chkB1 = .Add("Forms.CheckBox.1")
       With chkB1
            .Name = "CK" & xx
            .Caption = "Part"
            .Top = 10
            .Left = 245
            .Width = 45
            .Height = 15
       End With
       Set chkB.ckbEvent1 = chkB1
       'Here I added the code below

End With
       Set frm.frmEvent1 = frm1
Next x

End Sub

I tried also to add this code below the chkB1 setting, but nothing.

       Set chkB = New Classe1
       Set chkB.ckbEvent1 = Me.Controls(chkB.txbEvent1)
       chkBColl.Add chkB

Anybody have idea. Thank in advance to all for help.


Solution

  • Your code will not work.

    You need to create an array of classe1. Check this changes:

    Classe1

    Option Explicit
    Public WithEvents cmbEvent1 As MSForms.CommandButton
    Public WithEvents txbEvent1 As MSForms.TextBox
    Public WithEvents frmEvent1 As MSForms.Frame
    Public WithEvents ckbEvent1 As MSForms.CheckBox
    
    Public xx As Integer
    
    Private Sub cmbEvent1_Click()
    UserForm3.Hide
    End Sub
    
    Private Sub frmEvent1_Click()
    End Sub
    
    Public Sub txbEvent1_Change()
    End Sub
    
    Private Sub ckbEvent1_Click()
    If UserForm3.Controls("CK" & xx).value = True Then
    UserForm3.Controls("TB" & xx).Enabled = True
    End If
    End Sub
    

    UserForm3

    Dim cmdB() As New Classe1
    Dim txtB() As Classe1
    Dim chkB() As Classe1
    Dim frm() As New Classe1
    
    
    Dim chkBColl As New Collection
    
    Private Sub UserForm_Initialize()
    Dim x As Long
    Dim c As Variant
    Dim cmdB1 As MSForms.CommandButton
    Dim frm1 As MSForms.Frame
    Dim txtB1 As MSForms.TextBox
    Dim chkB1 As MSForms.CheckBox
    ReDim Preserve cmdB(1)
    Set cmdB1 = UserForm3.Controls.Add("Forms.CommandButton.1")
           With cmdB1
                .Name = "OKButton"
                .Caption = "OK"
                .Top = 40 * rngprt
                .Left = 120
                .Width = 40
                .Height = 25
           End With
           Set cmdB(0).cmbEvent1 = cmdB1
    
    For x = cel.Row To cel.Row + rngprt - 1 '**rngprt is a number from a Module1**
    
    xx = x - cel.Row + 1  '**for progessive name of controls Dim xx as long into the Module1**
    
        ReDim Preserve txtB(xx)
    Set txtB(xx) = New Classe1
    Set frm1 = UserForm3.Controls.Add("Forms.Frame.1")
                frm1.Top = 40 * (xx - 1)
                frm1.Left = 10
                frm1.Width = 300
                frm1.Height = 35
                frm1.Name = "FR" & xx
    
    With frm1.Controls
    
    Set txtB1 = .Add("Forms.TextBox.1")
           With txtB1
                .Name = "TB" & xx
                .Top = 10
                .Left = 160
                .Width = 30
                .Height = 15
                .TextAlign = fmTextAlignRight
                .Enabled = False
                .Value=50
           End With
           txtB(xx).xx = xx
           Set txtB(xx).txbEvent1 = txtB1
    
    ReDim Preserve chkB(xx + 1)
    Set chkB(xx) = New Classe1
         Set chkB1 = .Add("Forms.CheckBox.1")
           With chkB1
                .Name = "CK" & xx
                .Caption = "Part"
                .Top = 10
                .Left = 245
                .Width = 45
                .Height = 15
           End With
           Set chkB(xx).ckbEvent1 = chkB1
           chkB(xx).xx = xx
           'Here I added the code below
    
    End With
    ReDim Preserve frm(xx)
           Set frm(xx).frmEvent1 = frm1
    Next x
    
    End Sub