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.

On Jan 23, 11:08*am, "dman" wrote:
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

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" wrote in message
...
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.

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

Thanks!!

"Otto Moehrbach" wrote in message
...
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" wrote in message
...
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.

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 wrote:

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.

--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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 wrote:

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.

--

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.



dman wrote:

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

"Dave Peterson" wrote in message
...
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 wrote:

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.

--

Dave Peterson

--

Dave Peterson