Trouble with Disappearing UserForms when Running VBA Code to Delete Unneeded Worksheets

[JasonLiska]

Hello. This is my first post so thanks for your patience.

Prior to posting this thread I have exhausted many hours on multiple forum websites, Excel VBA books and other help resources. I'm spent.

COMPUTER/SOFTWARE: I'm working on a US Military PC (Windows 7 Enterprise, Service Pack 1 // MS Excel 2007).

BACKGROUND: My problem is in an automated calendar/scheduling system I built for my unit. It is an unprotected workbook that, when macros are enabled, becomes a nearly fully automated scheduling system. I protect the master file by automating backup copies for the general user to view without potential for corrupting the main program. The system ties into a large SMART Board which allows the scheduler to use tool menus (vbModeless UserForms). The goal is to keep a digital replacement for the standard large white board or dry erase board concepts that you see in most scheduling offices.

PROBLEM: I use multiple modeless userforms to allow automated manipulation of the calendar. I've built in quite a bit of redundancy but am unable to crack this one. The bottomline issue is that my userforms disappear when I loop through all worksheets in the workbook (hidden and visible) and delete extraneous worksheets (will explain later). The macro only causes the problem when run off a command button on one of the modeless userforms. All open userforms close regardless of where the code was initiated from.

EXAMPLE PROCESS: Scheduler uses a macro via command button on the scheduling tools userform to create or schedule an event within the calendar. The background code saves a copy of the calendar worksheet (prior to adding the event) and names the hidden sheet "UNDO". The event is scheduled and the "UNDO" command button on the Scheduling Tools userform highlights yellow letting the scheduler know they can undo the last action if the program result was not satisfactory. If the scheduler clicks the "UNDO" command button, the new calendar worksheet is hidden and renamed "REDO" while the "UNDO" worksheet is unhidden and renamed "CALENDAR" as it becomes the active calendar. Because of the large size of the calendar worksheet, it is necessary to remove unneeded UNDO/REDO worksheets prior to saving the file. (Before discovering this, the workbook was nearly 15 MB in size...and EXTREMELY slow...by removing unneeded worksheets I can keep the workbook under 4 MB). When the scheduler clicks the "SAVE" command button on the scheduling tools userform, a procedure is called prior to saving the workbook which deletes these unneeded worksheets. The code executes all the way through and then all active modeless userforms disappear.

FORMULAS: I loop through the worksheets looking at the worksheet names and deleting those that are no longer valuable to the program/workbook. For instance, if the Worksheet Name = "UNDO" then I delete it.

RESULTS: I run this same "clearing procedure" when the calendar is loaded...again, to ensure there are no extraneous worksheets hidden in the workbook. When executed during a calendar load or manual refresh, the procedure occurs prior to loading any userforms. There are no issues with the code here. The issue exists ONLY when there are already modeless userforms open (up to 4 different forms are possible at a given time). The same result, however, exists when the procedure is run from the userform command button and does not matter whether there is only 1 modeless form or all 4 open...they disappear. Additionally, I have performed extensive troubleshooting and found the problem to exist in the "clearing procedure" which loops through the worksheets and deletes the unneeded ones. The problem DOES NOT exist in the Workbook.Save execution nor does Excel crash or exhibit any problems beyond losing the userforms.

DESIRED RESULT: I want the Modeless UserForms to remain open following the procedure call to remove/delete unneeded worksheets.

ERROR MESSAGES: There are no error messages. In fact, the current result is similar to clicking the stop code button inside the VBA editor. The code, userforms, etc just quietly stop/quit and go away.

SPECIFIC CODE: If seeing specific lines of code would help you solve this issue, please let me know what you want to see. I may be able to answer your questions just based on all of the research and extensive trouble shooting.

Thank you in advance for reading this post and for attempting to solve this problem! I truly appreciate the effort!

Respectfully,
Jason

P.S. I'm posting this from my duty station in Germany...9 hour difference from the West Coast of the USA...so please be patient if awaiting a response from me. Thanks!

 

[Warship]

Post your "clearing procedure" code

 

[JasonLiska]

Sorry for the delay. I was under the impression I'd get an email when someone replied. I've learned that I don't. So, now my code is at work and I will need to get it for you... here's a rough idea...from memory...

Dim UF As Userform
For Each UF in Worksheets
If LEN(UF.Name,6,1) = "DELETEX" Then
UF.Delete
End If

*In case my code is not correctly typed or spelled, I'm dimming the userform as "UF" then looking at the first 6 letters of my unneeded worksheets (renamed from "undo" or "redo" into a time-stamped "deletex" worksheet to be removed in this code. Depending on how many macros the scheduler runs prior to saving (and hence running this code) the number of "deletex" sheets will be different. An example actual sheet name for today at 9pm would be "DELETEx0531132100". Hope this helps.

 

[JasonLiska]

Correction:

Dim WS As Worksheet
For Each WS in Workbook...

 

[Smitty]

Welcome to the Board!

If you go to Settings-->General Settings, under Messaging & Notifications you can change the Default Subscription Mode to notify you by e-mail to thread responses.

As for the code, if you're trying to delete sheets, why are you looking at user forms?

Maybe something like this:

Sub foo()
    Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            If UCase(Left(ws.Name, 7)) = "DELETEX" Then
                Application.DisplayAlerts = False
                    ws.Delete
                Application.DisplayAlerts = True
            End If
        Next ws
End Sub

HTH,

 

[JasonLiska]

My code is on my office computer, at the office. I made a bunch of typos but your code is what I've got. I do the same thing within my "save" procedure. It runs the code you entered and then some other stuff before saving. When I get to my work machine on Monday, I'll enter all of that save procedure, including the worksheet deletion code as you've entered above. Thanks for the help.

How do you enter "Code:" blocks like you did above? It looks much better and will be much easier for you to read when I enter that full procedure.

 

[Smitty]

Heya Jason,

To use Code tags you do this:

[ code ] Your code here [ / code]

Just remove the spaces, and the board's software will automatically render it when you post.

You can also use the VBHTML Maker which posts much nicer than Code tags. You'll find it if you follow the HTML Maker link in my signature (the HTML Maker will let you post shots of your sheet).

 

[JasonLiska]

Smitty, here's the code...the first section is what runs if the user clicks a command button from the modeless scheduler userform. There are few called procedures, but I only added the one that specifically deletes worksheets. Let me know if you want code from any of the other called procedures.

===========================================================
=========SAVE PROCEDURE (run from modeless userform command button)=========
===========================================================
'Clear remaining worksheets from undo or redo procedures
    Call ClearUndoRedo("BOTH")
    Call ClearDELETExSheets
    
    'Reset daily global variables
    Call ClearDayVars
    
    'Reset other global variables
    boolPerfUndoRedoAvail = False
    intCopyRows = 0
    intCopyCols = 0
    varPuckID = ""
    boolJustCopied = False
    boolAllowPuckSelection = True
    boolVarifiedUser = False
    boolTimeZones = False
    
    'Erase the array if it contains any data
    If Not IsEmpty(arrDEXRDayNums) Then Erase arrDEXRDayNums
    
    'Set global variable to False
    boolPerfUndoRedoAvail = False
    
    'Set scheduler buttons to black
    fm_Scheduler.btn_QuickUndoLast.BackColor = &H808080
    fm_Scheduler.btn_QuickRedoLast.BackColor = &H808080
    fm_Scheduler.btn_QuickPaste.BackColor = &H808080
    
    'Reset global variables
    intCopyRows = 0
    intCopyCols = 0
    varPuckID = ""
    boolJustCopied = False
    
    'Set focus to calendar sheet
    wsCal.Activate
    
    'INITIATOR MACRO (END):  MACRO = FALSE / PUCK SELECTION = TRUE / SCREEN UPDATE ON
    Application.ScreenUpdating = True
    boolAllowPuckSelection = True
    boolMACRO = False
    'SAVE WORKBOOK
    wkbkCal.Save
===========================================================
=========ClearDELETExSheets (called in SAVE PROCEDURE above)==============
===========================================================
    'Loop through all worksheets
Dim ws As Worksheet
    For Each ws In wkbkCal.Worksheets
        'Check for "DELETEx" in first 7 letters of each sheet name
        If Left(ws.Name, 7) = "DELETEx" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws

 

[JasonLiska]

No help received yet. Just checking back in with the community. I really need help on this...

Thanks.