title: Dynamically Make ComboBox Visible on Worksheet Change - VBA 2007
body: <p>I created a userform with a multipage.  On one of the multipages I have a combination of <strong><em>TextBoxes</em></strong> and <strong><em>ComboBoxes</em></strong>.  There are a total of <strong><em>9</em></strong> 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 <strong><em>9th</em></strong> box is a <strong><em>TextBox</em></strong> which the <strong><em>visibility</em></strong> is set to <strong><em>false</em></strong>.  The <strong><em>8th</em></strong> box is a <strong><em>ComboBox</em></strong>.  If the value in the <strong><em>8th</em></strong> box is <strong><em>"Existing"</em></strong> Then the visibility of the <strong><em>9th</em></strong> Box is <strong><em>True</em></strong>.</p>
  
  <p>This is how I'm dynamically created the additional TextBoxes/ComboBoxes:</p>
  
  <pre><code>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" &amp; i &amp; 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'!" &amp; Chr(65 + i) &amp; 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" &amp; i &amp; 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'!" &amp; Chr(65 + i) &amp; Target.Row + 1
                  .Name = "BTwo" &amp; (Target.Row - 1)
                  .Visible = False
              End With
          End Select
      Next
  </code></pre>
  
  <p>Here is how I am currently changing the visibility:</p>
  
  <pre><code>        Case Is = "$H"
          If Target.Value = "Existing" Then
              UserForm1.Controls("BTwo" &amp; Target.Row - 2).Visible = True
          Else
          If Target.Row = 2 Then Exit Sub
              UserForm1.Controls("BTwo" &amp; Target.Row - 2).Visible = False
          End If
      End Select
  </code></pre>
  
  <p>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 <strong><em>WITHOUT</em></strong> having to select another box?</p>