BeforeClose and BeforeSave interaction

**fredblogs** · 05-21-2010, 09:45 AM

Hi all,

I've written a macro for which works fine when the user saves the workbook the macro spits out a look up table into a second workbook. When the user closes the workbook I have another macro which runs and hides all the other sheets apart from the sheet that says Macros need to be enabled for this macro to work. It then does a save. This calls the before save macro into effect and it won't allow the second workbook to be set and therefore it crashes.

Please Login or Register  to view this content.

The code always crashes as the ExtRevenueBk will not set and is therefore nothing.

Anyone got any ideas as I'm currently going bald!!

Many thanks in advance.

**Wizz** · 05-21-2010, 10:23 AM

Hi fredblogs.
What error message do you get?
You have that code on the ' Private Sub Workbook_BeforeClose(Cancel As Boolean)' ?

Just an info:
On the following line

Please Login or Register  to view this content.

you are defining wkb as variant and only ExtRevenueBk as Workbook. The same happens your next Dim line. The 'Dim' statement is optional after the first one (in a row). But the 'as ....' MUST allways be written for each variable. Else, Excel will define it as type 'Variat'

**fredblogs** · 05-21-2010, 11:15 AM

Hi Wizz,

Thank you for the post and I will bear your advice in mind going forward. I've just made the changes you recommended and I'm still getting the same error. I'm getting an Object Variable or With Block Variable not set error message.

**fredblogs** · 05-21-2010, 11:24 AM

Hi Wizz... again,

The code is in a sub in a module and I'm calling it from the private before close sub. I have to say I can change it back very quickly it was one of my desperate attempts to try and get it to work.

**Wizz** · 05-21-2010, 12:14 PM

Fredblogs,
Having the code in a module is O.K. I just wanted to be sure you were calling it from the on workbook close event.

I tested the code you wrote and it worked fine.
Have you verified the path and name from file?
You are using Excel2007 or higher
Do you have more code lines between the procedure call and the line that pops the error?

**fredblogs** · 05-25-2010, 06:31 AM

Hi Wizz,

Thank you so much for you're help so far and apologies for the delayed response I was off yesterday. I'm using Excel 2007. The call is coming from the before save event. This is, I think, where my problem is lying as the line that saves the workbook in the before close event is firing the before save macro and creating the error. I've never been trained in excel so while I try to do things the best way these aren't necessarily the correct way (as you pointed out with the as statements). I've just had a think about it and I think you've cracked it I have changed the call to the before close event and it worked first time. It will work fine from here and I don't need it to happen every time the book is saved. I can do it when the book is closed. Thank you so much for your help. fb