need help with VBA code

[zami]

I need a macro to run automatically when a workbook is saved/closed. Assuming this will need to be set up with VBA code?

I'm running Excel 2000 on XP. Thanks.

 

[Norie]

Well you probably want to look at the BeforeClose and BeforeSave events of the workbook.

To access the workbook module right click the small XL icon next to File on the main menu and select code.

You can now use the dropdowns to create the code stubs for these events.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

This would be where you would put whatever code you want.

 

[zami]

much thanks, norie.

forgive my ignorance, but what's the difference between setting the scope for private vs public?

 

[Norie]

What do you mean?

Are you talking about variable declaration or how the sub's are declared?

 

[zami]

how subs are declared. why private vs public?

 

[Norie]

I honestly couldn't tell you exactly why the sub's in the workbook module's are declared as Private.

Is it a problem?

It's best not to mess with the way Excel VBA declares event code, it will probably just cause you problems if you do.

 

[zami]

not a problem that I'm aware of (yet). I just wondered if declaring private mean it would only work for the author of the workbook and public might work for anyone using the workbook. I'd prefer the latter, so wondered if it made a difference.

thanks for all your assistance.

 

[just_jon]

Private subs

1] Are not visible when using Tools | Macro | Macros

2] Are accessible only to other subs in the module where it's declared.

Use it to restrict unwanted/erroneous running of code.

 

[Norie]

Private/Public doesn't actually have anything to do with who authored the workbook/code.

Event code in the ThisWorkbook module is for that workbook.

 

[zami]

I learn something new every day! thanks for the prompt replies and insight.