Help with Calling a subroutine in the ThisWorkbook Class

[freshforlife]

Hi All,

I have defined a subroutine in the ThisWorkbook class as:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

--- code

--- code

End Sub


Now I have another subroutine in Module 1 of my workbook:


Sub Userform()

-- Code

-- Code



If Err = 0

Then Call Workbook_BeforeClose(True)
Exit Sub
End If

End Sub


Now I want to call the subroutine Workbook_BeforeClose from within the
Userform() routine, if there is an Error is encountered -- basically i
want to close the entire workbook.

Can somebody shed light on how to achieve this ?

Thanks,
Ravi

 

[Jim Cone]

Instead of... Call Workbook_BeforeClose(True)
Use... ThisWorkbook.Close
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"freshforlife"
wrote in message
Hi All,
I have defined a subroutine in the ThisWorkbook class as:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
--- code
--- code
End Sub

Now I have another subroutine in Module 1 of my workbook:

Sub Userform()
-- Code
-- Code
If Err = 0 Then
Call Workbook_BeforeClose(True)
Exit Sub
End If
End Sub


Now I want to call the subroutine Workbook_BeforeClose from within the
Userform() routine, if there is an Error is encountered -- basically i
want to close the entire workbook.
Can somebody shed light on how to achieve this ?
Thanks,
Ravi

 

[freshforlife]

Thanks Jim for your advice,

I tried "ThisWorkbook.Close", but probably I want to quit the Excel
Application also and not just close the workbook. Do you think
Application.Quit would work, but I would like to invoke the
Workbook_BeforeClose also since I want to execute the code in that
subroutine ( it restores certain toolbars) before quitting excel.

Thanks,
Ravi

 

[Jim Cone]

Ravi,
Close all workbooks before using Application.Quit.
The BeforeClose event runs (as the name implies) before the
actual workbook closure takes place.

You also need to decide whether each workbook is to be saved...
Workbooks("Ravioli").Close savechanges: = True ' or False

If you don't specify whether to save the book then Excel will pop up
a message asking what to do.
That stops everything until the message is answered.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"freshforlife"
wrote in message
Thanks Jim for your advice,

I tried "ThisWorkbook.Close", but probably I want to quit the Excel
Application also and not just close the workbook. Do you think
Application.Quit would work, but I would like to invoke the
Workbook_BeforeClose also since I want to execute the code in that
subroutine ( it restores certain toolbars) before quitting excel.

Thanks,
Ravi

 

[Jim Thomlinson]

Why Application.Quit? One of the golden rules of programming is that you do
not want to effect what is going on outside of your little area. What if the
user is working on other things besides your spreadsheet? By quitting the
application you are going outside of your world and infringing on theirs.

Just my 2 cents but if you gave me that spreadsheet to use I would give it
right back to you and ask you to fix it so that your spreadsheet has no
effect on anything else I might be doing.

 

[freshforlife]

Thanks Jim.......

I kind of implemented your idea partially, even though I could get the
desired thing to run by just "Application.Quit" ----

In Fact i closed all other workbooks except the Workbook which
contained the Workbook_BeforeClose() and other VBA subroutines........

and then i invoked the Application.Quit

-- Although by writing application.Quit -- I could close all workbooks
and exit Excel also.

Regards,
Ravi

 

[freshforlife]

Thanks Jim T ............

Although I solved my problem with Closing Workbooks +
Application.Quit, I see that you have a valid point.

Thanks for your valuable 2 cents.