Before Close Event

[poleary2000]

I have the following code in ThisWorkbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ExpenseErrors.Hide

Call EnablePrint

End Sub

Here is the code for the sub I am calling - EnablePrint().

Sub EnablePrint()

Dim cb As CommandBar
Dim bb As CommandBarControl

For Each cb In CommandBars
For Each bb In cb.Controls
If Left(bb.Caption, 5) = "Print" Then _
bb.Enabled = True
Next
Next

Application.CommandBars("Worksheet Menu Bar").Controls _
("File").Controls("Print Preview").Enabled = True

Application.CommandBars("Worksheet Menu Bar").Controls _
("File").Controls("Print...").Enabled = True

End Sub

MY PROBLEM -- As you can see in the Before Workbook Close, I am hiding a UserForm call ExpenseErrors before calling the EnablePrint sub. If this UserForm is NOT on the screen, I can close Excel without a problem. However, if the UserForm is on the screen when the user closes, I receive an Error from Excel that states

"MS Excel has encountered and error and must close. Sorry for the incovenience." There is a check box that asks you if you would like to start again.

Again, this ONLY happens if a UserForm is in its Show state when the user tries to close. I thought that adding the ExpenseErrors.Hide would alleviate the problem. Not so.

Any suggestions?

 

[poleary2000]

If I do NOT use the Call EnablePrint and instead copy and paste the code from the EnablePrint() to the ThisWorkbork.

For Each cb In CommandBars

It says RunTime Error 424 - Object Required and highlights the above section of the code.

I have no idea!

 

[poleary2000]

Any suggestions?

 

[Al Chara]

Just a thought, try using the unload command before you close:

unload ExpenseErrors

Not sure if it will work, but worth a try.