zoom38
07-26-2016, 09:46 AM
Hello, I have a sub that adds sheets and a sub that removes sheets. I also have a userform running modeless that I want to keep open at all times. It works great until I delete a sheet. When the removesheet sub is activated, it unloads the userform, deletes the sheet and then attempts to load it again after the sheet is deleted. However the userform does not come back unless I click on a sheet (show the userform in worksheet activate event). Below is the removesheet sub and showuserform sub i am using.
Sub Remove_Sheet()
Dim myShName As String
Dim countshts As Long
Dim ws As Worksheet
Dim dummy As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect
'Tested 7/25/2016
Unload UserForm1
myShName = Left(ActiveSheet.Name, 3)
ActiveWindow.SelectedSheets.Delete
'ActiveSheet.Delete
countshts = 0
For Each ws In ThisWorkbook.Worksheets
If StrComp(Left(ws.Name, 3), myShName, vbTextCompare) = 0 Then
countshts = countshts + 1
End If
Next ws
If countshts = 1 Then
Sheets(myShName & " (1)").Name = myShName
Sheets(myShName).Select
GoTo x
End If
For Each ws In ThisWorkbook.Worksheets
If StrComp(Left(ws.Name, 3), myShName, vbTextCompare) = 0 Then
countshts = countshts + 1
ws.Name = myShName & " (" & countshts + 500 & ")"
End If
Next
countshts = 0
For Each ws In ThisWorkbook.Worksheets
If StrComp(Left(ws.Name, 3), myShName, vbTextCompare) = 0 Then
countshts = countshts + 1
ws.Name = myShName & " (" & countshts & ")"
End If
Next
Sheets(myShName & " (" & countshts & ")").Select 'selects the last sheet in the group
x:
Call ChangeButtonCaptionsAfterSheetRemoval
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
'ShowUserform1 (dummy)
UserForm1.Show (vbModeless)
End Sub
Sub ShowUserform1(dummy)
If UserForm1.Visible = False Then
UserForm1.Show (vbModeless) 'vbModeless is the same as False
End If
End Sub
I've also attached a streamlined version of the file. Can someone advise why the userform will not pop back up after the sheet is deleted. I thought one of these two lines would do it but they don't.
'ShowUserform1 (dummy)
UserForm1.Show (vbModeless)
Thanks
Gary
Sub Remove_Sheet()
Dim myShName As String
Dim countshts As Long
Dim ws As Worksheet
Dim dummy As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect
'Tested 7/25/2016
Unload UserForm1
myShName = Left(ActiveSheet.Name, 3)
ActiveWindow.SelectedSheets.Delete
'ActiveSheet.Delete
countshts = 0
For Each ws In ThisWorkbook.Worksheets
If StrComp(Left(ws.Name, 3), myShName, vbTextCompare) = 0 Then
countshts = countshts + 1
End If
Next ws
If countshts = 1 Then
Sheets(myShName & " (1)").Name = myShName
Sheets(myShName).Select
GoTo x
End If
For Each ws In ThisWorkbook.Worksheets
If StrComp(Left(ws.Name, 3), myShName, vbTextCompare) = 0 Then
countshts = countshts + 1
ws.Name = myShName & " (" & countshts + 500 & ")"
End If
Next
countshts = 0
For Each ws In ThisWorkbook.Worksheets
If StrComp(Left(ws.Name, 3), myShName, vbTextCompare) = 0 Then
countshts = countshts + 1
ws.Name = myShName & " (" & countshts & ")"
End If
Next
Sheets(myShName & " (" & countshts & ")").Select 'selects the last sheet in the group
x:
Call ChangeButtonCaptionsAfterSheetRemoval
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
'ShowUserform1 (dummy)
UserForm1.Show (vbModeless)
End Sub
Sub ShowUserform1(dummy)
If UserForm1.Visible = False Then
UserForm1.Show (vbModeless) 'vbModeless is the same as False
End If
End Sub
I've also attached a streamlined version of the file. Can someone advise why the userform will not pop back up after the sheet is deleted. I thought one of these two lines would do it but they don't.
'ShowUserform1 (dummy)
UserForm1.Show (vbModeless)
Thanks
Gary