AW: Blattregistrierkarten als Add Inn
29.11.2020 19:22:37
Nepumuk
Hallo Udo,
in das Modul "DieseArbeitsmappe":
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Saved Then
Select Case MsgBox("Sollen Ihre Änderungen in '" & Name & _
"' gespeichert werden", vbExclamation Or vbYesNoCancel)
Case vbYes
Save
Case vbNo
Saved = True
Case vbCancel
Cancel = True
End Select
End If
If Not Cancel Then Call DeleteCommandBar
End Sub
Private Sub Workbook_Open()
Call CreateCommandBar
End Sub
In ein Standardmodul (Menüleiste im VBA-Editor - Einfügen - Modul):
Option Explicit
Option Private Module
Private Const COMMANDBAR_NAME As String = "MyCommandBar"
Public Sub CreateCommandBar()
Dim objCommandBar As CommandBar
Dim objCommandBarButton As CommandBarButton
Dim objSheet As Object
Call DeleteCommandBar
Set objCommandBar = CommandBars.Add(Name:=COMMANDBAR_NAME, Temporary:=True)
For Each objSheet In ThisWorkbook.Sheets
Set objCommandBarButton = objCommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With objCommandBarButton
.Caption = objSheet.Name
.OnAction = "'CommandBarButtonClick " & CStr(objSheet.Index) & "'"
.Style = msoButtonCaption
.Visible = True
End With
Next
objCommandBar.Visible = True
Set objCommandBar = Nothing
Set objCommandBarButton = Nothing
End Sub
Public Sub DeleteCommandBar()
Dim objCommandBar As CommandBar
For Each objCommandBar In CommandBars
If objCommandBar.Name = COMMANDBAR_NAME Then Call objCommandBar.Delete
Next
End Sub
Public Sub CommandBarButtonClick(ByVal pvlngIndex As Long)
Sheets(pvlngIndex).Select
End Sub
Gruß
Nepumuk
