Save VBA without saving the workbook?

[Guest]

I have some vba code that requires 4 cells to be completed on BeforeSave.
The code works great, but I need to be able to save the code without having
to complete the 4 fields in the worksheet because I want users to open the
worksheet with the 4 cells empty. I originally created some WorksheetOpen
code that emptied the 4 fields. But, I need users to be able to complete all
4 fields and save and reopen again with the fields complete so that code
won't work. This is driving me nutty. Any suggestions? Thanks.

 

[Guest]

Greetings Alex,

How about opening the Excel with Macros disabled and clearing out the cells?

Br, Mani

 

[Guest]

I originally did that, but then I realized that someone may want to save all
4 completed cells and then open the saved file at a later date. I wouldn't
want them to open the file and clear out the cells.

 

[Guest]

disable events while you save the file.

in the immediate window in VBA type

Application.EnableEvents = False

save the file, then set events to True to turn them back on. I've got a
toggle button on my toolbar assigned to a macro to do this.

Sub ToggleEvents()
With Application
.EnableEvents = Not .EnableEvents
If .EnableEvents Then
.StatusBar = False
Else: .StatusBar = "Events Are Disabled"
End If
End With
End Sub

 

[Dave Peterson]

You could put a key in to check first:

if worksheets("sheet99").range("a1").value = "Alex" then
'do nothing
else
'your code to verify the cells are non-empty
end if

or

if lcase(application.username) = "alex lastname" then

or you could cheat...

Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:

application.enableevents = false

Then back to excel and save the workbook the way you want. You've disabled
events, so _beforesave won't fire.

Toggle it back with:
application.enableevents = true