Protect or hide a worksheet

Protect worksheets

Worksheet protection exists to prevent users from inadvertently changing cells. It is not a security method, as the protection can be bypassed in seconds by freely available methods. Here's how it's done in Visual Basic for Applications (VBA):

ActiveSheet.Protect Password:="drowssap"

In AppleScript, there are separate commands, protect worksheet and protect workbook, depending on what you're protecting, so:

tell application "Microsoft Excel"
	protect worksheet (active sheet) password "drowssap"
end tell

Note that the converse, unprotect, works on both sheets and workbooks:

unprotect (active sheet) password "drowssap"

There is a minor error in the dictionary. Although it correctly shows that the command has a direct parameter (sheet / workbook) the formatting in the dictionary for the latter indicates that these are enumeration constants, but in fact they are not. They actually indicate the class of the object required: sheet or workbook.

To easily protect all sheets in a workbook using the same password, do the following:

Const sPWORD  As String = "drowssap"
Dim ws As Worksheet
For Each ws In
ActiveWorkbook.Worksheets
	ws.Protect
Password:=sPWORD
Next ws

Once again, you can scrap the looping:

set sPWORD to "drowssap"
protect worksheet (every worksheet in active workbook) password sPWORD

It's not just Standard Suite commands like open and delete that can act on multiple items: so do protect worksheet, unprotect, and many others in the Microsoft Excel dictionary. Excel has evidently implemented most of its own commands to act on multiple items, too. But you need to check with every command that's new to you, because sometimes you hit one that does not do so.

Hide worksheets

You can hide worksheets from the user. In VBA, to hide all worksheets with (hide) in their name:

Const sHIDEINDICATOR As String = "(Hide)"
Dim ws As Worksheet
For Each ws In Worksheets
	ws.Visible = Not (LCase(ws.Name) Like _
		LCase("*" & sHIDEINDICATOR & "*"))
Next ws

Here's an easy way to do it in AppleScript:

tell application "Microsoft Excel"
	repeat with ws in (get every worksheet in active workbook)
		set visible of ws to not (name of ws contains "(Hide)")
	end repeat
end tell

None of AppleScript's text operators, except AppleScript's text item delimiters, are case sensitive unless you've placed them inside a considering case block. So you don't have to use additional code, as in the VBA example, to compare any word with "(Hide)" or (hide).

Although standard AppleScript without scripting additions does not have wildcards or regular expressions, it does have contains, which works well.

Now here's a case where every worksheet whose... does not work, because Excel can't handle a property (for example, visible) of such a construction.

set visible of (every worksheet in active workbook ¬
	whose its name contains "(Hide)") to false
--> ERROR: Can't set visible of every worksheet of active
workbook whose name contains "(Hide)" to false.

So here you need to use a repeat loop.

But there's a subtle twist you need to be aware of. The VBA code above used Not — that is, ws.Visible = False — as a synonym for the enumeration xlHidden. To prevent the worksheet from appearing even in the Format/Sheet/Unhide dialog box, it would instead set the Visible property to xlVeryHidden:

ActiveSheet.Visible = xlVeryHidden

In AppleScript, that's:

set visible of active sheet to sheet very hidden

In AppleScript, the correct enumeration for the visible property is sheet hidden, sheet very hidden, and sheet visible, which is the equivalent of VBA's xlHidden, xlVeryHidden, and xlVisible. You can use true and false as synonyms for sheet visible and sheet hidden respectively if you want, just as in VBA.

This is a bit clearer in the Object Browser of Microsoft Visual Basic Editor if you check the actual Longs (numbers) of the enumerated constants. But it works in AppleScript, too.

This is very convenient in making these VBA-to-AppleScript conversions. If the VBA macro writer used numbers instead of the enumerated xl or vb constants, you can generally just transcribe the same numbers into your AppleScript code, even though the AppleScript dictionary doesn't explicity define which enumerated constants they refer to, or even that these "number equivalents" exist for the constants.

Your feedback was not submitted because the network connection was lost. Verify that you have an Internet connection, and then try submitting your feedback.

If you are still unsuccessful, the feedback server might be temporarily disconnected. Try submitting your feedback later.

Developers

Protect or hide a worksheet

Protect worksheets

Hide worksheets