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
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
This is the userform that once used, cause the issues with the workbook
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.
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
VBA Code:
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 SubThis 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
VBA Code:
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 SubThis is the userform that once used, cause the issues with the workbook
VBA Code:
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 SubThis 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.
