Can't Get Userform to Pop Up After Deleting Worksheet

[zoom38]

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

[SamT]

Try this in the ThisWorkbook Module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Not (UserForm1.Visible * (Not UserForm1 Is Nothing)) Then ShowFrom1
End Sub

Private Sub ShowForm1()
UserForm1.Show (vbModeless)
End Sub

UserForm.Visible = False = 0
Not (UserForm Is Nothing) = 0, if the Form is not loaded
Not (0 * anything) = True

Result = If Form is loaded and is Visible then don't run ShowForm1.
This is redundant if Userform Not loaded results in Visible = False.

[zoom38]

Thanks for the reply SamT but this doesn't solve my problem (unless I'm doing something wrong). I tried as you suggested and realized I already have a showuserform sub in the worksheet activate event and it works fine when clicking on a worksheet tab. The same as your code. The problem is after a sheet is deleted, I can't get the userform to show up unless I click on a sheet tab where the worksheet activate event will fire and show the userform. I am trying to get the userform to automatically re-appear after a sheet is deleted without having to click on a sheet tab. I have been working on this for days and can't figure it out.

In my remove sheet code I unload the userform thinking that in the end of the code, showing the user form again would work, but it doesn't. I tried it without unloading the userform but again the userform disappears and I can't get the userform to reload automatically. I have to click on another sheet tab to make it re-appear.

Any ideas?

Gary

[SamT]

Now I am confused. . .

Did you leave your show Form and or your Sheet Activate on the code page while mine was there too?

Are you talking about it doesn't work with yours, mine, or both together codes? I already know that your code wasn't working. That info is in your first post.

My bad. I did not look at your RemoveSheet code. I assumed it was working ok.

I will look now.
Edit:boy, that sure is a busy Procedure. Give me a while.

[SamT]

What does this line do? It looks like that sub operates on the UserForm

    Call ChangeButtonCaptionsAfterSheetRemoval

This piece of my code has a typo. The VBA Compiler will catch typos if you put "Option Explicit" at the top of your code pages.

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
    If Not (UserForm1.Visible * (Not UserForm1 Is Nothing)) Then ShowFrom1 
End Sub

The last word should be ShowForm1

[zoom38]

SamT I did comment out my show form procedure and used yours. It seems they both do the same thing. The following procedure

Call ChangeButtonCaptionsAfterSheetRemoval

simply changes the button captions on each sheet after a sheet is deleted. For instance I have a sheet named "201". I add another sheet that is automatically named "201 (2)" and the first sheet is named "201 (1)". If another sheet is added, it is named "201 (3)" and so on. On the "201 (2)" and "201 (3)" sheets there is a forms control button with the caption "Remove 201 (2)" and "Remove 201 (3)" respectively. Now if the "201 (2)" sheet is deleted, the "201 (3)" sheet is renamed to "201 (2)" and the "ChangeButtonCaptionsAfterSheetRemoval" changes the caption of the button on the sheet from "Remove 201 (3)" to "Remove 201 (2)". This may be a little confusing and might be easier to see than for me to explain. I will re-attach the file.

I did notice the ShowFrom1 typo. As I said, it does work but only upon clicking on another sheet tab.

I'm not sure if it makes a difference, but in addition to the forms control button "Remove 201(x)" that is created, I also have an active X button on the sheets with the caption "Add 201". It simply adds a sheet.Demo.xlsm

[zoom38]

So i changed the active x button to a forms control button and the userform re-appears automatically after a sheet is deleted as I intended. Would you know why the active x button caused this issue to begin with? Is there a work around? I happen to like the active x buttons better, I find them easier to work with.