ich möchte in meiner Arbeitsmappe gewisse Menüpunkte deaktivieren und beim Schließen wieder aktivieren. Wie kann ich das machen?
Danke vorab.
Marc
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