Super frustrating! How to disable hotkeys for hidden ribbons

[ACommandLineKindaGuy]

Hi all,

I have a custom ribbon with all standard ribbons hidden, ie <ribbon startFromScratch="true">
There are locked cells and unlocked cells on the worksheet. Protection is on with only AllowFormattingCells:=True
I have disabled or remapped a number of hotkeys using the OnKey method.

Here's the issue:

Some hotkey combinations such as <Alt> + O + D open the conditional formatting dialog box regardless if the active cell is locked or not. I don't want this to happen!
Others such as <Alt> + D + L (data validation) do not work period
Both of these commands are accessed from the Home Ribbon which is not displayed, so I'm not understanding the inconsistent behavior

I get an error trying to disable the <Alt> alone using OnKey (as in Application.OnKey "%", "'KeyMessage""Alt""'"

Any suggestions?

TIA John

 

[johnnyL]

Might want to check here <---

 

[ACommandLineKindaGuy]

Yes, been there. The OnKey method works for Ctrl, Shift, and Alt with one other alphanumeric What I'm trying to capture are multi-key entries initiated with the Alt key

 

[johnnyL]

What code are you using and where are you putting it?

 

[ACommandLineKindaGuy]

Actually, that is exactly the question I'm asking

 

[Jaafar Tribak]

The following code should disable the ATL+ O key combination when the active selection is locked and re-enable it when unlocked.

The code assumes the target sheet is Sheet1. Change this as required.

Code in the ThisWorkbook Module:

Option Explicit

Private WithEvents AppEvents As Application

#If VBA7 Then
    Private Declare PtrSafe Function RegisterHotKey Lib "user32" (ByVal hWnd As LongPtr, ByVal ID As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Private Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hWnd As LongPtr, ByVal ID As Long) As Long
#Else
    Private Declare Function RegisterHotKey Lib "user32" (ByVal hWnd As Long, ByVal ID As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Private Declare Function UnregisterHotKey Lib "user32" (ByVal hWnd As Long, ByVal ID As Long) As Long
#End If


Private Sub Workbook_Activate()
    Set AppEvents = Application
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Set AppEvents = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DISABLE_ALT_O = False
End Sub

Private Sub AppEvents_WorkbookActivate(ByVal Wb As Workbook)
    Dim vLocked As Variant
    
    If Wb Is Me Then
        If ActiveSheet Is Sheet1 Then
            vLocked = ActiveWindow.RangeSelection.Locked
            If IsNull(vLocked) Or vLocked = True Then
                DISABLE_ALT_O = True
            End If
        End If
    Else
        DISABLE_ALT_O = False
    End If

End Sub

Private Sub AppEvents_NewWorkbook(ByVal Wb As Workbook)
    DISABLE_ALT_O = False
End Sub

Private Sub AppEvents_SheetActivate(ByVal Sh As Object)

    Dim vLocked As Variant
    
    If Sh.Parent Is Me Then
        If Sh Is Sheet1 Then
            vLocked = ActiveWindow.RangeSelection.Locked
            If IsNull(vLocked) Or vLocked = True Then
                DISABLE_ALT_O = True
            End If
        End If
    End If

End Sub

Private Sub AppEvents_SheetDeactivate(ByVal Sh As Object)
        DISABLE_ALT_O = False
End Sub

Private Sub AppEvents_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim vLocked As Variant
    
    If Sh.Parent Is Me Then
        If Sh Is Sheet1 Then
            vLocked = Target.Locked
            If IsNull(vLocked) Or vLocked = True Then
                DISABLE_ALT_O = True
            Else
                DISABLE_ALT_O = False
            End If
        End If
    End If

End Sub

Private Property Let DISABLE_ALT_O(ByVal bDisable As Boolean)

    Const MOD_ALT = &H1
    
    If bDisable Then
        Call RegisterHotKey(0, MOD_ALT, MOD_ALT, VBA.vbKeyO + VBA.vbKeyD)
    Else
        Call UnregisterHotKey(0, MOD_ALT)
    End If
    
End Property

 

[Jaafar Tribak]

Editing time up

Please, in the DISABLE_ALT_O Property, remove the + VBA.vbKeyD addition

 

[ACommandLineKindaGuy]

Jaafar--that's pretty impressive! After all that effort, I hate saying that I'm not at all comfortable using code that changes registry values. I have no idea what may happen with individual users.

That said, does this actually disable the Alt+O+D keystrokes pressed/released sequentially, or just the Alt+O keystrokes (pressed together)

More importantly, the issue really is that some speed key combinations work and some don't. I don't want to go through debugging for every contingency.

Thanks so much for your effort.

John

 

[johnnyL]

Jaafar--that's pretty impressive! After all that effort, I hate saying that I'm not at all comfortable using code that changes registry values. I have no idea what may happen with individual users.

Registry value changes?

 

[Jaafar Tribak]

Jaafar--that's pretty impressive! After all that effort, I hate saying that I'm not at all comfortable using code that changes registry values. I have no idea what may happen with individual users.

That said, does this actually disable the Alt+O+D keystrokes pressed/released sequentially, or just the Alt+O keystrokes (pressed together)

More importantly, the issue really is that some speed key combinations work and some don't. I don't want to go through debugging for every contingency.

Thanks so much for your effort.

John

No. The code does not change any registry values. the RegisterHotKey api function has nothing to do with the registry despite its name.
The code disables Alt+O only but can easily be extended to disable other ALT+ keys as well .