Excel - prevent File Save and File Print 5

[LIMSman]

Can an Excel workbook be secured via VBA to prevent a user saving or printing from the File menu ('Save As' is OK)?

Also, how can you prevent macros/VBA code from being disabled by a user?

Thanks
LIMSman

 

[rmikesmith]

LIMsman,

Q1. Check out the BeforeSave and BeforePrint events for the Workbook object (look in the Object Browser).

Q2. No. This is a security issue. Preventing a user from disabling macros would allow malicious code to run upon opening an infected workbook.


Regards,
Mike

 

[roncurrie]

To disable file save and print menu items, in the "thisworkbook" area of code use the following:

Private Sub Workbook_Open()

Application.CommandBars("Worksheet Menu Bar&quot.Controls.Item("File&quot.Controls.Item("Save&quot.Enabled = False
Application.CommandBars("Worksheet Menu Bar&quot.Controls.Item("File&quot.Controls.Item("Print...&quot.Enabled = False

End Sub


I don't believe you can force a user to enable macros. You could force him to enable macros to use your workbook, however by hiding relevant worksheets (Format->Sheets->Hide) and turning on the "protect windows" (Tools->Protection->Workbook) feature and including the code necessary to unhide the worksheet in your macro (see below) in the same area as the code above. Replace "aaa" with the password for the workbook.

ActiveWorkbook.Unprotect ("aaa&quot
Worksheets("Sheet1&quot.Visible = True

Thus, if the macro isn't enabled the user won't have access to the worksheet. This won't keep out determined users, but should slow down most.

 

[LIMSman]

Mike

Thanks for the advice.

I hadn't thought of the security implications of forcing macros to be enabled!

LIMSman

 

[LIMSman]

Ron

Many thanks. Incidentally, if you disable a menu item should the associated shortcut e.g. Ctrl+P also be disabled? It doesn't appear to be.

LIMSman

 

[DaleWatson123321]

LIMSman,

Here's a method of disabling <Control> P...

1) Use the &quot;Macro&quot; window - <Alt> <F8> to activate

2) Create a &quot;dummy&quot; routine such as...

Sub DoNothing()

End Sub

3) In the Macro window, click &quot;Options&quot;, and under &quot;Shortcut key&quot;, enter the letter &quot;p&quot;.

4) Close out of the Macro window, and go to the VBA Editor - <Alt> <F11>.

5) Right-click on the Module where the &quot;DoNothing&quot; routine is located.

6) Choose &quot;VBAProject Properties...&quot;

7) Click the &quot;Protection&quot; tab.

8) Click &quot;Lock project for viewing&quot;

9) Enter a password

10) Exit out, and Save the file (required) before the full protection takes effect.

11) When you open the file, the user will now NOT be able to:
a) Use <Control> P
b) View the contents of the Module via the &quot;Macro&quot; menu <Alt> <F8>

Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rmikesmith]

Ron,

That's Dale's way of say 'yes'


My apologies, Dale. It's Friday and I'm feeling a bit impish.


Regards,
Mike

 

[roncurrie]

Great! I thought there might be other back doors. Hopefully there are no others. Glad you got the solution.

...Ron

 

[lambuhere1]

Ron

I was also trying the same concept as LISMAN was trying, But when I use your code,

Private Sub Workbook_Open()

Application.CommandBars(&quot;Worksheet Menu Bar&quot.Controls.Item(&quot;File&quot.Controls.Item(&quot;Save&quot.Enabled = False
Application.CommandBars(&quot;Worksheet Menu Bar&quot.Controls.Item(&quot;File&quot.Controls.Item(&quot;Print...&quot.Enabled = False

End Sub


I still see the save button and I am abke to save the file and print the file. What do you think I am doing wrong?

Thanks

Ram P

 

[LIMSman]

Dale

Your approach works fine! We have some very 'innovative' (thankfully not malicious) users! - need to try to lock everything down.

Many thanks

LIMSman

 

[Loomah]

LINSman
This is a way of ensuring all command bar save options (ie menu and button) are disabled. It should also work for print but I've just tested it and the button remains active. Can't think of any reason why!!

Sub NoPrintOrSave()
Dim myControls, ctl
Set myControls = CommandBars.FindControls _
    (Type:=msoControlButton, ID:=3) 'Disable Save
For Each ctl In myControls
    ctl.Enabled = 0 '1 to enable
Next ctl
Set myControls = CommandBars.FindControls _
    (Type:=msoControlButton, ID:=4) 'Disable Print
For Each ctl In myControls
    ctl.Enabled = 0 '1 to enable
Next ctl

End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[LIMSman]

Loomah

Many thanks for your suggestion.

I couldn't get it to work for print either!


LIMSman

 

[roncurrie]

Ram,

The only thing that comes to mind is that your code is not in the &quot;this workbook&quot; area of Excel Objects.

...Ron

 

[Loomah]

Ram
If you use the code that I posted it will also disable the save button. It should work if you have a toolbar full of save buttons and a menu full of save commands too. Not surprisingly though, I haven't tested that! Still won't disable the prnt button but using the beforeprint event will.

The difference is that the code Ron posted specified the save command on the &quot;Worksheet Menu Bar&quot;. This can be adapted by adding the same line and substituting &quot;standard&quot;.

I've also noticed and extra space in the name on the posting which may affect the code if you copied it and pasted.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[lambuhere1]

Ron, Loomah

Thanks for your guidance. I did notice the error now. It should work with your suggestions.

Thanks

Ram P