Macro to Protect / Unprotect Excel worksheet

Questions & Suggestions
1

Need some assistance here. Trying to create a macro that tests for a menu condition and then apply the appropriate Protect or Unprotect menu selection. The test for the menu item existenace always gives a FALSE no matter whether I have put in Protect Sheet… or Unprotect Sheet as per the menu item itself.

This is my macro:-

If All Conditions Met
Menu with name ‘Tools > Protection > Protect Sheet…’ exists
Execute the Following Actions:
Select Menu Item in Microsoft Excel
Select: Tools ⇢ Protection ⇢ Protect Sheet…
Stop macro and notify on failure.
Pause for 0.5 Seconds
Notify on failure.
Type the Return Keystroke
Otherwise, Execute the Following Actions:
Select Menu Item in Microsoft Excel
Select: Tools ⇢ Protection ⇢ Unprotect Sheet
Stop macro and notify on failure.

Appreciate any and all assistance. The alternative is to create two different macros but I wanted to simplify to one. Thanks in advance.

2

Since your workflow is entirely within Excel, why not use an Excel VBA macro to do this? IMO that would be much better than relying on the Excel Menu UI, which could change at any time.

3

That would be an option and I am familiar with VBA so should be fairly easy. Just thought that it would be “easier” to do via KM.

4

Ah well, bit the bullet and did it in Excel. Saved to the Personal.XLSB workbook so that it’s available across all open workbooks.

Sub Protect_UnProtect_Sheet()
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect Password:="", UserInterfaceOnly:=True
End If
End Sub

To make it relevant to KM, I might just map a KM hotkey macro to activate the Excel hotkey macro :smiley:

5

I have found it is best to use the MS Office VBA macros when the workflow is entirely within the Office apps. Anytime you introduce a 3rd party product you increase the chances for problems, now or later.

The VBA is the simplest:

Sub ToggleProtect()

If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect ""
    MsgBox "ActiveSheet is Now UN-PROTECTED"
    
Else
    ActiveSheet.Protect ""
    MsgBox "ActiveSheet is Now PROTECTED"
    
End If

End Sub

EDIT:  2017-04-25  8:36 PM CT

I see we cross-posted, but yours was first.
Well done!

[quote="dcklau, post:4, topic:6895"]
To make it relevant to KM, I might just map a KM hotkey macro to activate the Excel hotkey macro
[/quote]

If you do that, then you have made your Excel macro dependent on KM.
I guess if you are the only user, that's fine.  But what if you want to send the Excel file to someone else?

As you can tell, I prefer to keep things simple.  :wink:
6

I was just kidding about that. Thanks!!

1 Like