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.

Greetings Alex,

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

Br, Mani

"Alex" wrote:

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.

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.

"Mr_Mani" wrote:

Greetings Alex,

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

Br, Mani

"Alex" wrote:

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.

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



"Alex" wrote:

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.

"Mr_Mani" wrote:

Greetings Alex,

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

Br, Mani

"Alex" wrote:

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.

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



Alex wrote:

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.

"Mr_Mani" wrote:

Greetings Alex,

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

Br, Mani

"Alex" wrote:

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.

--

Dave Peterson