Public Sub doesn't work after using UserForm

[Tdorman]

I am having an issue where, after I use a UserForm to delete information from a data sheet, the code that I am using to insert and delete columns does not work. Every other macro and userform seems to work fine after this one particular userform, except for the code dictating when to insert and delete columns. Before using this userform, the code works great, either manually adjusting the the keycells or using a macro to refresh the sheet. This issue only appears after I use the one userform.

I also have an issue with resetting the code in the VBA editor. If I am editing the code and receive any error at all anywhere in the file and I reset it in the editor, the error goes away and I can use the file. However, the code does not work and I receive no new error messages. If I then save the file and open it back up, I receive the same error message as before. Its an endless cycle, and the only way to get the code to try to work again is to close the file and reopen it.

This is the private sub that controls the keycells range and sheets it applies to

Private Sub Worksheet_Change(ByVal Target As Range)
                                                                                                                                             
    Dim KeyCells As Range, colNum As Long
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    
        SOMESHEETS = "*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*"
        Set KeyCells = Range("B30")
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            If IsNumeric(KeyCells.Value) Then
                colNum = KeyCells.Value
                If colNum > 0 Then
                 For Each ws In ThisWorkbook.Worksheets
                     If ws.Visible = xlSheetVisible Then
                     If CBool(InStr(LCase(SOMESHEETS), LCase("*" & ws.Name & "*"))) Then
                            InsertColumnsOnSheet argSheet:=ws, argColNum:=colNum
                     End If
                     End If
                 Next ws
                End If
            End If
        End If
        

    
    SOMESHEETS = "*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*"
    Set KeyCells = Range("B36")
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        If IsNumeric(KeyCells.Value) Then
            colNum = KeyCells.Value
            If colNum > 0 Then
                For Each ws In ThisWorkbook.Worksheets
                    If ws.Visible = xlSheetVisible Then
                    If CBool(InStr(LCase(SOMESHEETS), LCase("*" & ws.Name & "*"))) Then
                            InsertColumnsOnSheet argSheet:=ws, argColNum:=colNum
                    End If
                    End If
                Next ws
            End If
        End If
    End If
    
    Application.ScreenUpdating = True
End Sub

This is the general code that applies to each sheet. I only copied one sheets code, but its similar for each sheet in the workbook, just specific to that sheet

Option Explicit
Public Sub InsertColumnsOnSheet(ByVal argSheet As Worksheet, ByVal argColNum As Long)

    Dim Rng As Range, c As Range
    Dim TotalCol As Long, LeftFixedCol As Long
    Dim i As Long
    Dim ws As Worksheet
    Dim j As Integer, k As Integer

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Set ws = Worksheets("MemberInfo-20")
    With argSheet
        Set Rng = .Range(.Cells(7, 2), .Cells(7, .Columns.Count))
        Set c = Rng.Find("END")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 1
            j = .Range("A8").End(xlToRight).Column
            k = j - LeftFixedCol
            If ws.Visible = xlSheetVisible Then
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                    .Columns(j).Copy
                    .Columns(j + 1).Resize(, argColNum - k).Insert CopyOrigin:=xlFormatFromLeftOrAbove
                        Application.CutCopyMode = False
            End If
            End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

This is the userform that once used, cause the issues with the workbook

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'declare the variables
    Dim Findvalue As Range, DeleteRange As Range
    Dim Response As VbMsgBoxResult
    Dim cNum As Integer
    Dim Search As String, FirstAddress As String
    Dim ws As Worksheet
    
    
    Set ws = ThisWorkbook.Sheets("DATA Member-19")

    
'error statement
    On Error Resume Next
    
    Search = TextBox6.Value
'check for control from listbox dblclick values
    If TextBox6.Value = "" Or Search = "" Then
        MsgBox "There is not data to delete", 48
        Exit Sub
    Else
'find the employees number row
        Set Findvalue = ws.Range("D:D").Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole)
        If Not Findvalue Is Nothing Then
'mark first address
        FirstAddress = Findvalue.Address
'give the user a chance to change their mind!
            Response = MsgBox(Search & Chr(10) & _
            "Are you sure that you want to delete this Member?", 292, "Are you sure?")
            If Response = vbYes Then
'find all matching records
            Do
                If DeleteRange Is Nothing Then
                    Set DeleteRange = Findvalue
                Else
                    Set DeleteRange = Union(DeleteRange, Findvalue)
                End If
            Set Findvalue = ws.Range("D:D").FindNext(Findvalue)
            Loop While FirstAddress <> Findvalue.Address
            
'delete record(s)
            DeleteRange.EntireRow.Delete
                
'clear the user form controls
                cNum = 12
                For x = 1 To cNum
                    Me.Controls("Reg" & x).Value = ""
                Next
                
'Employee deleted from the database
                MsgBox Search & Chr(10) & "The Member has been deleted successfully.", 64, "Record Deleted"
                
'add the values to the listbox
               lstLookup.RowSource = ""
                
            End If
        Else
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
        End If
    End If

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
Private Sub CommandButton2_Click()

Unload frmDeleteMembers19


End Sub

This issue with the columns being inserted and/or deleted is concerning, but I'd really like to figure out why, after resetting the VBA editor, no code changes seem to do anything. It's getting frustrating having to close and reopen the file every time I need to make a change.

 

[RoryA]

Remove the Exit Sub line from the userform code. Having it there allows the code to exit without re-enabling events, which is why the other code then doesn't run, and it doesn't actually serve any useful purpose.

 

[Tdorman]

@RoryA Thanks for the response. I tried removing that line from the Userform as well as removing the enable events lines from there. Still can't get the code to work after that userform is used.

 

[RoryA]

You should only have needed to remove the exit sub. If that does not rectify the problem (after restarting excel) then you possibly have other code that also disabled events