Excel form opens then immediately closes

I am new to VB so please excuse anything lame that I might post. I am creating a program that tracks my call times for me. The code does exactly what I want it to do except if it needs to execute the code in bold, it does everything correctly except when it opens the Userform1, the form flashes open and the closes. The form opens correctly when executed in any of the previous instances. The issue is only in the bolded part of the script. Why won't my form stay open when the code in bold is executed?
Any help would be greatly appreciated. And I thank you in advance.

My code:
----------------------------------------------

Private Sub cmdOpenForm_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("ThisWeek").Select
If Range("AI4") = ("") Then
Range("AI4") = Date
Call FormLoad
ElseIf Range("AI4") = Date Then
Call FormLoad
Exit Sub
ElseIf Range("AI15") = ("") Then
Range("AI15") = Date
Call FormLoad
ElseIf Range("AI15") = Date Then
Call FormLoad
Exit Sub
ElseIf Range("AI26") = ("") Then
Range("AI26") = Date
Call FormLoad
ElseIf Range("AI26") = Date Then
Call FormLoad
Exit Sub
ElseIf Range("AI37") = ("") Then
Range("AI37") = Date
Call FormLoad
ElseIf Range("AI37") = Date Then
Call FormLoad
Exit Sub
ElseIf Range("AI48") = ("") Then
Range("AI48") = Date
Call FormLoad
ElseIf Range("AI48") = Date Then
Call FormLoad
Exit Sub
ElseIf Range("AI48") < Date Then
Sheets("ThisWeek").Select
Sheets("ThisWeek").Copy
ActiveWorkbook.SaveAs Filename:="C:\PhLogBackup\" & Format(Range("AI1").Value, "mm-dd-yy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Sheets("ThisWeek").Delete
Worksheets("MasterSheet").Copy Before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "ThisWeek"
Sheets("ThisWeek").Select
Range("AI4") = Date
End If
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Call FormLoad
End Sub
--------------------------------------------------------------------------
Sub FormLoad()
Application.WindowState = xlMinimized
UserForm1.Show
UserForm1.Height = 63.75
End Sub

Welcome to the Board.

You tell it to close the workbook, so why wouldn't you expect it to close? ElseIf Range("AI48") < Date Then
Sheets("ThisWeek").Select
Sheets("ThisWeek").Copy
ActiveWorkbook.SaveAs Filename:="C:\PhLogBackup\" & Format(Range("AI1").Value, "mm-dd-yy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Perhaps you need to explain what you're trying to do with that bit of code?

-EM

Hello emartinho and thank you for the reply.

'The Workbook name is CallTracker.xls'
‘At this point the program detects the worksheet (ThisWeek) is full'
ElseIf Range("AI48") < Date Then
‘The program selects, then copies and saves the worksheet as a new workbook
'in a different directory naming it using the value that was entered in cell ("AI1") of the worksheet (ThisWeek) ‘
Sheets("ThisWeek").Select
Sheets("ThisWeek").Copy
ActiveWorkbook.SaveAs Filename:="C:\PhLogBackup\" & Format(Range("AI1").Value, "mm-dd-yy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
‘SaveAs makes the newly created workbook the active workbook, so the newly created workbook needs to be closed to get back to the original workbook (CallTracker.xls)’
ActiveWorkbook.Close
‘The original workbook (CallTracke.xls) needs to be made active’
ThisWorkbook.Activate
‘The old worksheet (ThisWeek) needs to be deleted and a new worksheet
'(ThisWeek) is created by copying worksheet (MasterSheet) and renaming it (ThisWeek)
Sheets("ThisWeek").Delete
Worksheets("MasterSheet").Copy Before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "ThisWeek"
‘The date is then entered in cell (AI4) in the new worksheet (ThisWeek)
'and is now ready to start a new week of data’
Sheets("ThisWeek").Select
Range("AI4") = Date
End If
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
‘At this point, the active workbook (CallTRacker.xls) minimizes to the taskbar.
'Everything so far works perfectly and exactly as it should with the Workbook (CallTracker.xls)
'active and minimized to the taskbar, a correctly named new workbook in the correct directory
'has been created and closed. So far so good ’
Call FormLoad
End Sub
Sub FormLoad()
Application.WindowState = xlMinimized
‘The form (UserForm1) opens. But this is where the issue is.
'The form will flash for a second and then close.
'The workbook (CallTracker.xls is still open and minimized on the taskbar.
'I can even bring up the workbook from the taskbar and click on a button in the worksheet (ThisWeek)
'that opens the form manually and the form opens up that way with no problems.
'I am going crazy trying to figure out why the form closes when I have the script open it.’
UserForm1.Show
UserForm1.Height = 63.75
End Sub

2 things:
1. Edit the extremely LOOOOOOOOOOONG :huh: comments in your code block to
be a little shorter, they make the post as wide as the longest comment. :D
2. Here's your problem:ActiveWorkbook.SaveAs Filename:="C:\PhLogBackup\" & Format(Range("AI1").Value, "mm-dd-yy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
‘SaveAs makes the newly created workbook the active workbook, so the newly
'created workbook needs to be closed to get back to the original workbook (CallTracker.xls)’
ActiveWorkbook.Close
‘The original workbook (CallTracke.xls) needs to be made active’
ThisWorkbook.Activate
When you SaveAs, the newly created workbook replaces the
original, effectively closing it right there. The original workbook is no longer
open in Excel at all. Try it manually, ie open a file and use the menus like a
regular user :D, and you'll see what I mean. You'll probably need to call the
Workbooks.Open method to re-open your original file, or find another way to
do what you need to do. Perhaps you could create a template and then it
will run everything off the "new" file always?

Hope this helps.
-EM

I haven't read the whole thread, but addressing the final point in the last post, there is a Workbook.SaveCopyAs() method which may be of interest.

Forgot about that method. :whoops: Thanks for the save, Colin!

When I SaveAs the newly created workbook is saved. However, the original
workbook (CallTracker.xls) is still open (it doesn't close) and, with 'ThisWorkbook.Activate' the
original becomes the active workbook. Even the worksheet (ThisWeek) is then
deleted and a new (ThisWeek) worksheet is created by copying the template
worksheet (MasterSheet) and renaming (MasterSheet2) to (ThisWeek) The
date even gets entered into the correct cell. This same original workbook
(CallTracker.xls) then minimizes using 'Application.WindowState = xlMinimized'.
It is there active in the taskbar. I can maximize it and it functions like normal. I
have a button on the worksheet called "Open Form" that when clicked on will
open the form manually and that even works at this point.The original is active
and working. It's just that when 'UserForm1.show' opens the form, it opens
then closes immediately. The workbook (CallTracker.xls) still remains active in
the taskbar.

Is UserForm1 used prior to the execution of this code? I've had problems where closing forms clears the memory and causes an error or crash when trying to reference it again. You could try loading the form before displaying it i.e. Load UserForm1

Another idea might be to step through the module by pressing F8 and see if it brings up an error or at the very least, you'll know the exact line the code breaks on.