PDA

View Full Version : Can't Get Userform to Pop Up After Deleting Worksheet



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

SamT
07-26-2016, 12:29 PM
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
07-26-2016, 04:23 PM
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
07-26-2016, 05:06 PM
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
07-26-2016, 05:45 PM
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
07-27-2016, 05:01 AM
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.16731

zoom38
07-27-2016, 05:09 AM
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.