title: Public Sub not firing after use of UserForm
body: <p>I have an issue where, once I use a userform within the file, the public sub that is inserting columns on my sheets stops working.  I run into this issue once I use this specific userform.  I can add certain information and have the public sub add in the new columns, however, once I use the one userform, it no longer functions.</p>
  <p>A scenario that works with no problems:
  - Import xml
  - Add columns based on XML
  - Add in new member
  - Add column with new member</p>
  <p>A scenario that does not work:
  - Import XML
  - Add columns based on XML
  - Use macro/userform to make adjustment to any member
  - Add in new member
  - New column will not be added in for new member</p>
  <p>I can continue to add in new members and update the amount of columns that are inserted, however, once I hit the button to call the adjustment userform, the functionality to add in new columns (or delete columns) goes away.</p>
  <p>This is in the sheet module for the worksheet change that controls the amount of columns to be added/deleted</p>
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  On Error GoTo ErrorHandler
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
                                                                                                                                               
      Dim KeyCells As Range, colNum As Long
      Dim ws As Worksheet
      
          SOMESHEETS = &quot;*C-Proposal-19*MemberInfo-19*Schedule J-19*NOL-19*NOL-P-19*NOL-PA-19*Schedule R-19*Schedule A-3-19*Schedule A-19*Schedule H-19*&quot;
          Set KeyCells = Range(&quot;B30&quot;)
          If Not Application.Intersect(KeyCells, Target) Is Nothing Then
              If IsNumeric(KeyCells.Value) Then
                  colNum = KeyCells.Value
                  If colNum &gt; 0 Then
                   For Each ws In ThisWorkbook.Worksheets
                       If ws.Visible = xlSheetVisible Then
                       If CBool(InStr(LCase(SOMESHEETS), LCase(&quot;*&quot; &amp; ws.Name &amp; &quot;*&quot;))) Then
                              InsertColumnsOnSheet argSheet:=ws, argColNum:=colNum
                       End If
                       End If
                   Next ws
                  End If
              End If
          End If
          
  
      
      SOMESHEETS = &quot;*MemberInfo-20*C-Proposal-20*Schedule J-20*NOL-20*Schedule R-20*NOL-P-20*SchA-3-20*Schedule H-20*NOL-PA-20*Schedule A-20*Schedule A-5-20*&quot;
      Set KeyCells = Range(&quot;B36&quot;)
      If Not Application.Intersect(KeyCells, Target) Is Nothing Then
          If IsNumeric(KeyCells.Value) Then
              colNum = KeyCells.Value
              If colNum &gt; 0 Then
                  For Each ws In ThisWorkbook.Worksheets
                      If ws.Visible = xlSheetVisible Then
                      If CBool(InStr(LCase(SOMESHEETS), LCase(&quot;*&quot; &amp; ws.Name &amp; &quot;*&quot;))) Then
                              InsertColumnsOnSheet argSheet:=ws, argColNum:=colNum
                      End If
                      End If
                  Next ws
              End If
          End If
      End If
      
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
            
  ErrorHandler:
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
  
  End Sub
  </code></pre>
  <p>This is the code that controls the columns that are copied and where they are inserted for each page</p>
  <pre><code>Option Explicit
  Public Sub InsertColumnsOnSheet(ByVal argSheet As Worksheet, ByVal argColNum As Long)
  
  On Error GoTo ErrorHandler
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
      Dim Rng As Range, c As Range
      Dim TotalCol As Long, LeftFixedCol As Long, i As Long
      Dim j As Integer, k As Integer
      
      With argSheet
          Set Rng = .Range(&quot;3:3&quot;)
          Set c = Rng.Find(&quot;END&quot;)
          
          If Not c Is Nothing Then
              TotalCol = c.Column
              LeftFixedCol = Application.WorksheetFunction.Match(&quot;Member1&quot;, argSheet.Range(&quot;3:3&quot;), 0) - 1
              j = .Range(&quot;B4&quot;).End(xlToRight).Column
              k = j - LeftFixedCol
              If argSheet.Visible = xlSheetVisible Then
                  If TotalCol &lt; LeftFixedCol + argColNum + 1 Then
                      .Columns(j).Copy
                      .Columns(j + 1).Resize(, argColNum - k).Insert CopyOrigin:=xlFormatFromLeftOrAbove
                          Application.CutCopyMode = False
                  End If
                  If TotalCol &gt; LeftFixedCol + argColNum + 1 Then
                  For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                      .Columns(i).Delete
                  Next i
                  End If
              End If
          End If
      End With
       
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
            
  ErrorHandler:
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
  
  End Sub
  </code></pre>
  <p>This is the code for the userform that seems to be causing the issue</p>
  <pre><code>Option Explicit
  Private Sub CommandButton1_Click()
  
  On Error GoTo ErrorHandler
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
  Dim ws As Worksheet
  Dim N As Long
  Dim i As Long
  Dim Rng1 As Range
  Dim Rng2 As Range
  Dim Rng3 As Range
  Dim Rng4 As Range
  Dim Rng5 As Range
  
  
  Set ws = ActiveSheet
  Set Rng1 = ws.Range(&quot;6:6&quot;).Find(Me.TextBox2.Value)
  Set Rng2 = ws.Range(&quot;6:6&quot;).Find(Me.TextBox6.Value)
  Set Rng3 = ws.Range(&quot;6:6&quot;).Find(Me.TextBox5.Value)
  Set Rng4 = ws.Range(&quot;6:6&quot;).Find(Me.TextBox4.Value)
  Set Rng5 = ws.Range(&quot;6:6&quot;).Find(Me.TextBox7.Value)
  
  N = ActiveSheet.Cells(Rows.Count, &quot;B&quot;).End(xlUp).Row
  
  If WorksheetFunction.CountIfs(ws.Range(&quot;6:6&quot;), TextBox2, ws.Range(&quot;6:6&quot;), TextBox2) = 0 And ComboBox1 &lt;&gt; 0 Then
   MsgBox &quot;Sorry, &quot; &amp; TextBox2 &amp; &quot; not found!&quot;
      Else
  If TextBox3.Value = &quot;&quot; And ComboBox1.Value &lt;&gt; &quot;&quot; Then
          MsgBox &quot;There is no data to add&quot;, 48
      Else
      
  If WorksheetFunction.CountIfs(ws.Range(&quot;6:6&quot;), TextBox6, ws.Range(&quot;6:6&quot;), TextBox6) = 0 And ComboBox2 &lt;&gt; 0 Then
   MsgBox &quot;Sorry, &quot; &amp; TextBox6 &amp; &quot; not found!&quot;
      Else
  If TextBox8.Value = &quot;&quot; And ComboBox2.Value &lt;&gt; &quot;&quot; Then
          MsgBox &quot;There is no data to add&quot;, 48
      Else
      
  If WorksheetFunction.CountIfs(ws.Range(&quot;6:6&quot;), TextBox5, ws.Range(&quot;6:6&quot;), TextBox5) = 0 And ComboBox3 &lt;&gt; 0 Then
   MsgBox &quot;Sorry, &quot; &amp; TextBox5 &amp; &quot; not found!&quot;
      Else
  If TextBox9.Value = &quot;&quot; And ComboBox3.Value &lt;&gt; &quot;&quot; Then
          MsgBox &quot;There is no data to add&quot;, 48
      Else
      
  If WorksheetFunction.CountIfs(ws.Range(&quot;6:6&quot;), TextBox4, ws.Range(&quot;6:6&quot;), TextBox4) = 0 And ComboBox4 &lt;&gt; 0 Then
   MsgBox &quot;Sorry, &quot; &amp; TextBox4 &amp; &quot; not found!&quot;
      Else
  If TextBox10.Value = &quot;&quot; And ComboBox4.Value &lt;&gt; &quot;&quot; Then
          MsgBox &quot;There is no data to add&quot;, 48
      Else
      
  If WorksheetFunction.CountIfs(ws.Range(&quot;6:6&quot;), TextBox7, ws.Range(&quot;6:6&quot;), TextBox7) = 0 And ComboBox5 &lt;&gt; 0 Then
   MsgBox &quot;Sorry, &quot; &amp; TextBox7 &amp; &quot; not found!&quot;
      Else
  If TextBox11.Value = &quot;&quot; And ComboBox5.Value &lt;&gt; &quot;&quot; Then
          MsgBox &quot;There is no data to add&quot;, 48
      Else
  
  For i = 5 To N
      If ActiveSheet.Cells(i, &quot;B&quot;).Value = frmAddAdj.ComboBox1.Value Then
          ActiveSheet.Cells(i, Rng1.Column).Value = frmAddAdj.TextBox3.Text
      End If
      
      If ActiveSheet.Cells(i, &quot;B&quot;).Value = frmAddAdj.ComboBox2.Value Then
          ActiveSheet.Cells(i, Rng2.Column).Value = frmAddAdj.TextBox8.Text
      End If
      
      If ActiveSheet.Cells(i, &quot;B&quot;).Value = frmAddAdj.ComboBox3.Value Then
          ActiveSheet.Cells(i, Rng3.Column).Value = frmAddAdj.TextBox9.Text
      End If
      
      If ActiveSheet.Cells(i, &quot;B&quot;).Value = frmAddAdj.ComboBox4.Value Then
          ActiveSheet.Cells(i, Rng4.Column).Value = frmAddAdj.TextBox10.Text
      End If
      
      If ActiveSheet.Cells(i, &quot;B&quot;).Value = frmAddAdj.ComboBox5.Value Then
          ActiveSheet.Cells(i, Rng5.Column).Value = frmAddAdj.TextBox11.Text
      End If
  Next i
  
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  
  ErrorHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  
  End Sub
  Private Sub CommandButton2_Click()
  
  Unload frmAddAdj
  
  End Sub
  
  Private Sub CommandButton3_Click()
  Dim ctl As MSForms.Control
  
      For Each ctl In Me.Controls
          Select Case TypeName(ctl)
              Case &quot;TextBox&quot;, &quot;ComboBox&quot;
                  ctl.Text = &quot;&quot;
              Case &quot;CheckBox&quot;, &quot;OptionButton&quot;, &quot;ToggleButton&quot;
                  ctl.Value = False
          End Select
      Next ctl
  End Sub
  
  Private Sub UserForm_Initialize()
  
  On Error GoTo ErrorHandler
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
  Dim iRow As Integer, iMax As Integer
  
  iRow = Cells.Find(What:=&quot;New Jersey Audit Adjustment&quot;, _
      LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).Row
  
  iMax = Cells.Find(What:=&quot;New Jersey Audit Adjustment&quot;, _
      LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
      MatchCase:=False, SearchFormat:=False).Row
      
  If ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Cells.Count = 1 Then
      Me.ComboBox1.AddItem ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox2.AddItem ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox3.AddItem ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox4.AddItem ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox5.AddItem ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      
  Else
      Me.ComboBox1.List = ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox2.List = ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox3.List = ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox4.List = ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      Me.ComboBox5.List = ActiveSheet.Range(&quot;B&quot; &amp; iRow &amp; &quot;:B&quot; &amp; iMax).Value
      
  End If
  
  ErrorHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  
  End Sub
  </code></pre>
  <p>I can't figure out why adding in the new columns stops after I use the add adjustment macro.  This is the last bit of code above.</p>
  <p>I tried adding in the errorhandler but that didn't change the end result.  I also add a debugger to try and find out if there was an error, and it would pop up saying there was an error with no information being provided.  The line and description were blank, so that was no help.</p>