Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Keyboard Shortcut - Protect/Unprotect the Current Worksheet in Excel
#1
Lightbulb 
It is a good habit to protect a worksheet against inadvertent changes when analyzing its data. The following Autohotkey code (script) lets you create a keyboard shortcut that will quickly protect an unprotected worksheet. The same keyboard shortcut will unprotect a protected worksheet. This shortcut works with any worksheet; no Excel add-in required.

Need help creating a keyboard shortcut with ShortKeeper using the following AutoHotkey code? This 5-minute tutorial will help!
Critical
if WinActive("ahk_class XLMAIN")
{
  ControlGet, hwnd, hwnd, , Excel71, A
  if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hwnd, "UInt", 0xFFFFFFF0, "Ptr", -VarSetCapacity(IID,16)+NumPut(0x46000000000000C0, NumPut(132096, IID, "Int64"), "Int64"), "Ptr*", pacc) = 0
    ExcelSheet := ComObject(9, pacc, 1).ActiveSheet
  if (ExcelSheet.ProtectContents)
  {
    ExcelSheet.Unprotect
    MsgBox ,,,% "Worksheet Unprotected.", 1
  }
  else
  {
    ExcelSheet.Protect
    MsgBox ,,,% "Worksheet Protected.", 1
  }
}
else
  MsgBox 0x40040, ShortKeeper, There is no Excel worksheet window active.

The setting will be applied if the Excel worksheet is the active window and is not busy (e.g. editing, selecting a command).

The number “1” at lines #10 and #15 sets a 1-second duration for a dialog box to appear indicating the change process. For the dialog box to remain longer, simply adjust the value (e.g., 2, 3, etc.).

This script is optimized to be used with ShortKeeper. As such, there is no exception handling, no “Return” at the end, and variable(s) don’t need to be cleared. ShortKeeper does all of this automatically. Just copy/paste the code in the PARAM field in ShortKeeper, set your hotkey, save, and you’re ready to use your new keyboard shortcut.
Enterpad: For those who need more than a few shortcuts.

Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)