PDA

View Full Version : Disabling save/as in a specific workbook



londy
08-16-2007, 07:05 AM
Hi,

I have tried to disable save and save as options for a specific file by using this code under Workbook_Open event (private)



With Application.CommandBars("File")
.Controls(4).Enabled = False
.Controls(5).Enabled = False
End With
Application.CommandBars("Standard").Controls(3).Enabled = False



Unfortunately the code applies to all of the workbooks I open while the coded workbook is opened

I tried to replace the Application object with ThisWorkbook but I get an error...

YellowLabPro
08-16-2007, 07:07 AM
Londy,
You are referencing the entire Excel program w/ Application.xxx

You need to reference the workbook that this targets.

Application.Workbooks(Thisworkbook.xls).CommandBars("File")
For an example

londy
08-16-2007, 07:13 AM
Let's say my file name is londy.xls, I should type the following?




With Application.WorkBook("Londy.xls")CommandBars("File")
.Controls(4).Enabled = False
.Controls(5).Enabled = False
End With

rory
08-16-2007, 07:17 AM
It would be easier to put Cancel = True in the Workbook_BeforeSave event.
Obviously none of these solutions will work if the user disables macros.

londy
08-16-2007, 07:26 AM
I wasn't aware of this option since I am new to VBA (self taught), it is much more simple
:)

rory
08-16-2007, 07:51 AM
If you wanted to go the commandbar route, you would want code in the Workbook_Deactivate and Activate events to disable/re-enable the commandbar controls so that when you switch to other workbooks, the commands are available again.