0

I created a userform with a multipage. On one of the multipages I have a combination of TextBoxes and ComboBoxes. There are a total of 9 Boxes next to each other. They are all control sourced to the excel sheet behind it. When a user enters data into the the first box, it dynamically creates another 9 boxes which correspond to the next row. This process repeats. Now, the 9th box is a TextBox which the visibility is set to false. The 8th box is a ComboBox. If the value in the 8th box is "Existing" Then the visibility of the 9th Box is True.

This is how I'm dynamically created the additional TextBoxes/ComboBoxes:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Left(Target.Address, 2)
        Case Is = "$A"
        Dim cCntrl As Control
        Dim i As Integer
        For i = 0 To 8
        //skipping to case 7 and 8
        Case Is = 7
            Set cCntrl = UserForm1.Frame13.Controls.Add("Forms.ComboBox.1", "Login" & i & Target.Row + 1, True)
            With cCntrl
                .Width = UserForm1.ComboBox240.Width
                .Height = UserForm1.ComboBox240.Height
                .Top = (10 + UserForm1.ComboBox240.Height) * (Target.Row - 1)
                .Left = UserForm1.ComboBox240.Left
                .ControlSource = "'Multi Branch'!" & Chr(65 + i) & Target.Row + 1
                .List = Worksheets("Setup").Range("BILLING").Value
                .Style = fmStyleDropDownList
            End With
            Case Is = 8
                Set cCntrl = UserForm1.Frame13.Controls.Add("Forms.TextBox.1", "Login" & i & Target.Row + 1, True)
            With cCntrl
                .Width = UserForm1.TextBox287.Width
                .Height = UserForm1.TextBox287.Height
                .Top = (10 + UserForm1.TextBox287.Height) * (Target.Row - 1)
                .Left = UserForm1.TextBox287.Left
                .ControlSource = "'Multi Branch'!" & Chr(65 + i) & Target.Row + 1
                .Name = "BTwo" & (Target.Row - 1)
                .Visible = False
            End With
        End Select
    Next

Here is how I am currently changing the visibility:

        Case Is = "$H"
        If Target.Value = "Existing" Then
            UserForm1.Controls("BTwo" & Target.Row - 2).Visible = True
        Else
        If Target.Row = 2 Then Exit Sub
            UserForm1.Controls("BTwo" & Target.Row - 2).Visible = False
        End If
    End Select

This method technically works, but only after I leave the 8th box. This seems like an OnExit sort of move. The first row i simply did an onchange event which works fine. Any thoughts as to how I can change the visibility on change WITHOUT having to select another box?

CBC_NS
  • 1,961
  • 4
  • 27
  • 47
  • Why don't you do an `Change` event for each of the textboxes/combobox controls? Then you can perform that sort of validation on the rest of the form controls. – David Zemens Nov 13 '13 at 14:40
  • The problem resides here David, I am creating the next row of boxes dynamically. This occurs on sheet change, thus I would need to create the events dynamically as well. – CBC_NS Nov 13 '13 at 14:56
  • Ahhh yes that's what I thought. I think this can be done using a class modules. See [HERE](http://stackoverflow.com/questions/10592641/assign-event-handlers-to-controls-on-user-form-created-dynamically-in-vba) for an example. – David Zemens Nov 13 '13 at 15:35

0 Answers0