Option Explicit
Private Sub prcControlEnableDisable(intId As Integer, bolStatus As Boolean)
Dim myCommandBar As CommandBar, myCommandBarControl As CommandBarControl
For Each myCommandBar In CommandBars
Set myCommandBarControl = myCommandBar.FindControl(ID:=intId, Recursive:=True)
If Not myCommandBarControl Is Nothing Then myCommandBarControl.Enabled = bolStatus
Next
End Sub
Public Sub prcOff()
Call prcControlEnableDisable(21, False)
End Sub
Public Sub prcOn()
Call prcControlEnableDisable(21, True)
End Sub
Option Explicit
Public Sub create_Id_list()
Dim myCommandBarControl As CommandBarControl, myCommandBar As CommandBar
Dim intColumn As Integer, intCount As Integer, lngRow As Long, intCbCount As Integer
Application.ScreenUpdating = False
lngRow = 1
Cells.ClearContents
For Each myCommandBar In Application.CommandBars
intCbCount = intCbCount + 1
Cells(lngRow, 1) = myCommandBar.Name
Cells(lngRow, 2) = myCommandBar.NameLocal
With Cells(lngRow, 3)
.Value = intCbCount
.Font.Bold = True
End With
For intCount = 1 To myCommandBar.Controls.Count
With myCommandBar.Controls(intCount)
Cells(lngRow + intCount, 1) = .ID
Cells(lngRow + intCount, 2) = .Caption
End With
Next intCount
lngRow = lngRow + intCount + 1
Next
On Error Resume Next
For lngRow = 2 To Cells(65536, 1).End(xlUp).Row
If IsNumeric(Cells(lngRow, 1)) And Trim(Cells(lngRow, 1)) <> "" Then
intColumn = 3
For Each myCommandBarControl In Application.CommandBars(Cells(Cells(lngRow, 1).End(xlUp).Row, 1).Value).Controls(Cells(lngRow, 2).Value).Controls
If Err.Number <> 0 Then GoTo nextone
With myCommandBarControl
Cells(lngRow, intColumn) = .ID
Cells(lngRow, intColumn + 1) = .Caption
End With
intColumn = intColumn + 2
Next
End If
nextone:
Err.Clear
Next
Columns.AutoFit
Application.ScreenUpdating = True
End Sub