having trouble disabling "save", "save as" (2003)

[dman]

Hi.

I have a workbook that I do not want the user to be able to save or save as.
The code I have disables the proper icons, however, when I start Excel up
again
with a different workbook, the icons are still disabled.

If I remove the .application, I get object var or with block var not set.
How do I
disable the save/save as for only the workbook I need? Here is my code int
he the
Workbook_Open sub.

ThisWorkbook.Application.CommandBars("Worksheet menu bar").Controls _
("&file").Controls("&Save").Enabled = False

ThisWorkbook.CommandBars("Worksheet menu bar").Controls _
("&file").Controls("&Save").Enabled = False

Help. Thanks.

 

[GTVT06]

Hi.

I have a workbook that I do not want the user to be able to save or save as.
The code I have disables the proper icons, however, when I start Excel up
again
with a different workbook, the icons are still disabled.

If I remove the .application, I get object var or with block var not set.
How do I
disable the save/save as for only the workbook I need? Here is my code int
he the
Workbook_Open sub.

  ThisWorkbook.Application.CommandBars("Worksheet menu bar").Controls _
          ("&file").Controls("&Save").Enabled = False

  ThisWorkbook.CommandBars("Worksheet menu bar").Controls _
          ("&file").Controls("&Save").Enabled = False

Help. Thanks.

Try re-enableing it before the workbook closes. add the code below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Application.CommandBars("Worksheet menu bar").Controls
_
("&file").Controls("&Save").Enabled = True


ThisWorkbook.CommandBars("Worksheet menu bar").Controls _
("&file").Controls("&Save").Enabled = True

End Sub

 

[Otto Moehrbach]

Use something like this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SaveAsUI = False
Cancel = True
End Sub
This macro must be placed in the workbook module. To access that module,
right-click the Excel icon that is immediately to the left of the word
"File" in the menu across the top of your screen, and select View Code.
Paste this macro into that module. "X" out of the module to return to your
sheet.
Note that with this macro in place you will not be able to save the file at
all. That means that you cannot save the file with this macro in place.
You will have to add some code to this macro to make the canceling of the
Save command conditional. Come back if you need help with doing so. HTH
Otto

 

[dman]

Wow. That's much easier! And it works on 'save as' and the X, too.

Thanks!!

 

[Dave Peterson]

If you really want to disable all saves, you could try this workbook level
event:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "Cannot be saved!"
End Sub

If macros are disabled (or events are disabled), this will fail.

And remember that you have to disable events before you can update the file,
too.

 

[dman]

How can I close my workbook, or prevent if from opening, if the user
selects disable marcos at startup?

 

[Dave Peterson]

If the user doesn't allow macros to run, then none of your macros will run.

You could password protect the workbook and then provide a helper workbook that
would open that workbook.
If macros are disabled for the helper workbook, then the real workbook won't
open.

If macros are enabled for the helper workbook, then the real workbook will have
macros enabled.