时间戳

首页 > 解决方案 > 如何禁用 Excel 上下文菜单项

excel - 如何禁用 Excel 上下文菜单项

问题描述

我有一些代码用于在工作表保护关闭时禁用某些 Excel 上下文菜单项。我让它运行了几次，现在它抛出一个错误，“运行时错误 424，需要对象”。变量“ctrl”什么都不是，所以我认为这就是问题所在。我检查了对象浏览器，我认为代码是正确的，并且运行了几次都没有错误。但是，当我关闭工作簿并重新打开它时，我收到了错误消息。

Public Sub Set_Menus(Optional CloseUp As Boolean = False)
' Set the visibility of some context menu items to prevent issues
' Use the app BuiltInControlsScanner.exe for ids (in /etc)
' Sheet1.Range("T16") saves the value of the workbook protected or not

Dim ctrl As Office.CommandBarControl
Dim arrIdx(13) As Long
Dim idx As Variant

    ' Assign values to each element of the array - arrIdx, this can be added to as needed ;)
    arrIdx(0) = 292       ' Cell Delete
    arrIdx(1) = 293       ' Row Delete
    arrIdx(2) = 294       ' Column Delete
    arrIdx(3) = 3181      ' Cell Insert
    arrIdx(4) = 3183      ' Row & Column Insert
    arrIdx(5) = 3125      ' Clear Contents
    arrIdx(6) = 31402     ' Cell Filter
    arrIdx(7) = 31435     ' Cell Sort
    arrIdx(8) = 541       ' Row Height
    arrIdx(9) = 542       ' Column Height
    arrIdx(10) = 883      ' Row Hide
    arrIdx(11) = 884      ' Row Unhide
    arrIdx(12) = 886      ' Column Hide
    arrIdx(13) = 887      ' Column Unhide

    If Sheet1.Range("T16").value = True Or CloseUp = True Then
    ' If the workbook is protected or we want to close it, set them back to Visible...
        For Each idx In arrIdx
            For Each ctrl In Application.CommandBars.FindControls(ID:=idx)
                ctrl.Enabled = True
            Next ctrl
        Next idx

    Else ' Hide the menu items
        For Each idx In arrIdx
            For Each ctrl In Application.CommandBars.FindControls(ID:=idx)
                ctrl.Enabled = False
            Next ctrl
        Next idx
    End If

End Sub

标签： excelvba

解决方案

您需要检查是否FindControls没有返回任何内容。

请注意，我引入了一个变量ShowItems，因此您无需重复循环代码两次。

Public Sub Set_Menus(Optional CloseUp As Boolean = False)
' Set the visibility of some context menu items to prevent issues
' Use the app BuiltInControlsScanner.exe for ids (in /etc)
' Sheet1.Range("T16") saves the value of the workbook protected or not

Dim ctrl As Office.CommandBarControl
Dim arrIdx(13) As Long
Dim idx As Variant
Dim FoundControls As CommandBarControls

    ' Assign values to each element of the array - arrIdx, this can be added to as needed ;)
    arrIdx(0) = 292       ' Cell Delete
    arrIdx(1) = 293       ' Row Delete
    arrIdx(2) = 294       ' Column Delete
    arrIdx(3) = 3181      ' Cell Insert
    arrIdx(4) = 3183      ' Row & Column Insert
    arrIdx(5) = 3125      ' Clear Contents
    arrIdx(6) = 31402     ' Cell Filter
    arrIdx(7) = 31435     ' Cell Sort
    arrIdx(8) = 541       ' Row Height
    arrIdx(9) = 542       ' Column Height
    arrIdx(10) = 883      ' Row Hide
    arrIdx(11) = 884      ' Row Unhide
    arrIdx(12) = 886      ' Column Hide
    arrIdx(13) = 887      ' Column Unhide

    Dim ShowItems As Boolean
    If Sheet1.Range("T16").Value = True Or CloseUp = True Then
        ShowItems = True
    Else ' Hide the menu items
        ShowItems = False
    End If

    ' If the workbook is protected or we want to close it, set them back to Visible...
    For Each idx In arrIdx
        Set FoundControls = Application.CommandBars.FindControls(ID:=idx)
        If Not FoundControls Is Nothing Then 'if no CommandBarControls were found skip the following
            For Each ctrl In FoundControls
                ctrl.Enabled = ShowItems
            Next ctrl
        End If
    Next idx
End Sub

推荐阅读