Call Worksheet_Change from diff module

[MCCCLXXXV]

So I have 5 sheets in my book and each of them have a Worksheet_Change function embedded. In the Thisworkbook module, I want to add a Workbook_BeforeClose function that calls each of these 5 Worksheet_Change subs. I know I can just make Workbook_BeforeClose change each sheet and then change it back, but is there a more professional way of calling these functions from within Thisworkbook?


Thanks

[shg]

Are all the worksheet change events the same? If so, you should use the Workbook_SheetChange event in the ThisWorkbook module.

If not, the worksheet change events should call routines in code modules, and those routines can be called in the before close events. In general, you don't want application-specific code in the sheet and workbook modules -- it unnecessarily limits code re-use.

[MCCCLXXXV]

aha. that makes a lot of sense.

on a somewhat separate note:

how do i refer to "thissheet"? in Worksheet_Change, I want to pull in the sheet's name

thx

[MCCCLXXXV]

nevermind, i figure that part out. now i have a new problem though.

I want excel to automatically run a macro BETWEEN the time it asks me if i want to save changes but BEFORE it closes. Workbook_BeforeClose runs before it asks me to save

[VBA Noob]

Maybe a before save event which goes in ThisWorkbook module

Please Login or Register  to view this content.

VBA Noob

[MCCCLXXXV]

bumping this. anyone know a solution??

I need my code to do this:

I have an excel book that updates a SQL database automatically anytime the user changes a cell. This part works fine. change a cell and the table in the database changes.

But, if the user decides that the changes he/she made are wrong and doesn NOT want to save the changes, I need to book upload to the database one more time right after the user clicks "no, don't save changes" but before the book closes. this would effectively upload whatever was in the book when it was opened. i tried using the BeforeClose and BeforeSave sections and they didnt work.