I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...

Hi Hawk -

Try adding the statement:

Cancel = True

immediately before the comment statement:
'Restore form

in your Workbook_BeforeClose procedure.

--
Jay


"The Hawk" wrote:

I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...

Thanks Jay. Unfortunately that did not fix the problem as the message and
form still spawn a second time.

Any other advice will be appreciated.

"The Hawk" wrote:

I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...

To clarify, the message and form are now in a loop and you cannot exit the
Workbook or Excel without entering the VBA code and commenting out the
Cancel=True statement.

Thanks...

"The Hawk" wrote:

Thanks Jay. Unfortunately that did not fix the problem as the message and
form still spawn a second time.

Any other advice will be appreciated.

"The Hawk" wrote:

I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...

When you try to close the workbook from the UserForm, I believe it is still
loaded when the BeforeClose event fires. My suggestion would be to have a
public variable in the Userform that will indicate if it's closing. For
example, I created a user form named frmEntry and have this in the code
module:

Public bFormIsClosing As Boolean

Private Sub UserForm_Initialize()
bFormIsClosing = False
End Sub

Private Sub UserForm_Terminate()
bFormIsClosing = True
ThisWorkbook.Close
End Sub


Then, in the Thisworkbook module, the BeforeClose handler looks like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is closing
If Not frmEntry.bFormIsClosing Then
Cancel = True
MsgBox "You must close the application via the Exit button on the
Entry form."
frmEntry.Show
End If
End Sub





--
Hope that helps.

Vergel Adriano


"The Hawk" wrote:

To clarify, the message and form are now in a loop and you cannot exit the
Workbook or Excel without entering the VBA code and commenting out the
Cancel=True statement.

Thanks...

"The Hawk" wrote:

Thanks Jay. Unfortunately that did not fix the problem as the message and
form still spawn a second time.

Any other advice will be appreciated.

"The Hawk" wrote:

I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...

That was it Vergel! Thanks for your help!

"Vergel Adriano" wrote:

When you try to close the workbook from the UserForm, I believe it is still
loaded when the BeforeClose event fires. My suggestion would be to have a
public variable in the Userform that will indicate if it's closing. For
example, I created a user form named frmEntry and have this in the code
module:

Public bFormIsClosing As Boolean

Private Sub UserForm_Initialize()
bFormIsClosing = False
End Sub

Private Sub UserForm_Terminate()
bFormIsClosing = True
ThisWorkbook.Close
End Sub


Then, in the Thisworkbook module, the BeforeClose handler looks like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is closing
If Not frmEntry.bFormIsClosing Then
Cancel = True
MsgBox "You must close the application via the Exit button on the
Entry form."
frmEntry.Show
End If
End Sub





--
Hope that helps.

Vergel Adriano


"The Hawk" wrote:

To clarify, the message and form are now in a loop and you cannot exit the
Workbook or Excel without entering the VBA code and commenting out the
Cancel=True statement.

Thanks...

"The Hawk" wrote:

Thanks Jay. Unfortunately that did not fix the problem as the message and
form still spawn a second time.

Any other advice will be appreciated.

"The Hawk" wrote:

I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...

Hi 'Hawk' -

I'm sorry I wasn't able to follow-up in a timely fashion; glad to see you
and Vergel resolved your problem.

--
Jay

"The Hawk" wrote:

That was it Vergel! Thanks for your help!

"Vergel Adriano" wrote:

When you try to close the workbook from the UserForm, I believe it is still
loaded when the BeforeClose event fires. My suggestion would be to have a
public variable in the Userform that will indicate if it's closing. For
example, I created a user form named frmEntry and have this in the code
module:

Public bFormIsClosing As Boolean

Private Sub UserForm_Initialize()
bFormIsClosing = False
End Sub

Private Sub UserForm_Terminate()
bFormIsClosing = True
ThisWorkbook.Close
End Sub


Then, in the Thisworkbook module, the BeforeClose handler looks like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is closing
If Not frmEntry.bFormIsClosing Then
Cancel = True
MsgBox "You must close the application via the Exit button on the
Entry form."
frmEntry.Show
End If
End Sub





--
Hope that helps.

Vergel Adriano


"The Hawk" wrote:

To clarify, the message and form are now in a loop and you cannot exit the
Workbook or Excel without entering the VBA code and commenting out the
Cancel=True statement.

Thanks...

"The Hawk" wrote:

Thanks Jay. Unfortunately that did not fix the problem as the message and
form still spawn a second time.

Any other advice will be appreciated.

"The Hawk" wrote:

I am trying to force closure of an Excel 2003 workbook via VBA behind a user
form rather than with the normal File | Close or X and have tried several
different approaches; none of which solves the problem. When the user form
is hidden, the File | Save and X close options are available and if selected
cause a fatal crash. I want to retain the ability to save and close normally
for program maintenance, but want to present a normal user with a message and
process to exit the program via the user form. I have tried code in
Workbook_BeforeClose, but it seems that the routine gets called twice. It
works as expected the second time through. The code I'm using is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Check to see if frmEntry is loaded
If isFormLoaded(frmEntry) Then
MsgBox "You must close the application via the Exit button on the
Entry form."
'Restore form
frmEntry.Show
End If

End Sub

It calls the function below:

Function isFormLoaded(frmName As Object) As Boolean
Dim intI As Integer

'initialize the return value to false
isFormLoaded = False

'loop through all open forms
For intI = 0 To UserForms.Count - 1
'test to see if the current form has the
'same name as the frmName
If UserForms(intI) Is frmName Then
'return true
isFormLoaded = True
'exit the loop
Exit For
End If
Next
End Function

Any help you can provide with this code or any other options will be
sincerely appreciated.

Thanks...