I have about 15 macro shortcut keys in Personal.xlsb to do simple tasks, but they are slowly but surely starting to be disabled. Last week, Ctrl-Shift-d (change pivot table detail table to range) stopped working; yesterday, Ctrl-Shift-z (change format of highlighted cells to "#,##0") stopped working.
I tried all of the suggestions in the various threads on MrExcel and elsewhere to no avail. So I ran a test (TLDR: demonstrating each step I took to solve problem):
1. Closed Excel, backed up personal.xlsb, erased it from the Excel Startup directory.
2. Rebooted.
3. Opened Excel, confirmed that there were no macros.
4. Recorded a new macro, setting the shortcut key to Ctrl Shift z (i.e. Shift z in the option box)
5. Created a simple macro:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Range("E4").Select
ActiveCell.FormulaR1C1 = "Hi there .. Isn't this fun"
Range("E6").Select
ActiveCell.FormulaR1C1 = "Yes"
Range("E7").Select
End Sub
6. Confirmed the macro works by using Macro window Run command on "PERSONAL.XLSB!Macro1"
7. Select blank sheet
8. Select Ctrl Shift z .... Nothing.
I ran through the usual sequence of confirming something was wrong:
- deleted shift z in the Macro Options window
- closed Excel (saving Personal Macro Workbook)
- opened Excel, confirmed no shortcut was assigned to Macro1 in Macro Options window
- closed Excel
- opened Excel
- assigned Ctrl Shift z to Macro1 in Macro Options window
- closed Macro window.
- Selected Ctrl Shift z .... Nothing.
- Confirmed that Macro1 works by running Personal.xlsb!Macro1 from the Macro window.
- Closed Excel (saving Personal Macro Workbook)
- Opened Excel
- Confirmed Ctrl Shift z assigned to Macro1 in Macro Options window
- Closed Macro window
- Selected Ctrl Shift z .... Nothing
What about a different letter?
- opened Macro Options window
- assigned Ctrl Shift s to Macro1
- closed Macro window
- Select Ctrl Shift s .... Macro Works!
- opened Macro Options window
- assigned Ctrl Shift z to Macro1
- closed Macro window
- Selected Ctrl Shift z .... Nothing
- I confirmed again that Ctrl Shift s runs the macro, Ctrl Shift z will not.
So something is preventing Ctrl Shift z from working (Ctrl Shift d, too), I just don't know where to look. Registry key, maybe?
Settings, etc:
Trust Center Macro Security Setting: Enable All Macros
Windows 10
Office 2016
Excel 64-bit
Thank you.
I tried all of the suggestions in the various threads on MrExcel and elsewhere to no avail. So I ran a test (TLDR: demonstrating each step I took to solve problem):
1. Closed Excel, backed up personal.xlsb, erased it from the Excel Startup directory.
2. Rebooted.
3. Opened Excel, confirmed that there were no macros.
4. Recorded a new macro, setting the shortcut key to Ctrl Shift z (i.e. Shift z in the option box)
5. Created a simple macro:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Range("E4").Select
ActiveCell.FormulaR1C1 = "Hi there .. Isn't this fun"
Range("E6").Select
ActiveCell.FormulaR1C1 = "Yes"
Range("E7").Select
End Sub
6. Confirmed the macro works by using Macro window Run command on "PERSONAL.XLSB!Macro1"
7. Select blank sheet
8. Select Ctrl Shift z .... Nothing.
I ran through the usual sequence of confirming something was wrong:
- deleted shift z in the Macro Options window
- closed Excel (saving Personal Macro Workbook)
- opened Excel, confirmed no shortcut was assigned to Macro1 in Macro Options window
- closed Excel
- opened Excel
- assigned Ctrl Shift z to Macro1 in Macro Options window
- closed Macro window.
- Selected Ctrl Shift z .... Nothing.
- Confirmed that Macro1 works by running Personal.xlsb!Macro1 from the Macro window.
- Closed Excel (saving Personal Macro Workbook)
- Opened Excel
- Confirmed Ctrl Shift z assigned to Macro1 in Macro Options window
- Closed Macro window
- Selected Ctrl Shift z .... Nothing
What about a different letter?
- opened Macro Options window
- assigned Ctrl Shift s to Macro1
- closed Macro window
- Select Ctrl Shift s .... Macro Works!
- opened Macro Options window
- assigned Ctrl Shift z to Macro1
- closed Macro window
- Selected Ctrl Shift z .... Nothing
- I confirmed again that Ctrl Shift s runs the macro, Ctrl Shift z will not.
So something is preventing Ctrl Shift z from working (Ctrl Shift d, too), I just don't know where to look. Registry key, maybe?
Settings, etc:
Trust Center Macro Security Setting: Enable All Macros
Windows 10
Office 2016
Excel 64-bit
Thank you.