AW: Menüleiste mit Anzeige Arbeitsblattname
14.10.2009 18:18:34
Nepumuk
Hallo,
ein Beispiel:
' **********************************************************************
' Modul: DieseArbeitsmappe Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************
Option Explicit
Private Sub Workbook_Activate()
Call Create_CommandbarButton
End Sub
Private Sub Workbook_Deactivate()
Call Delete_CommandbarButton
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim intIndex As Integer
For intIndex = 1 To 2
If Not gobjCommandBarButton(intIndex) Is Nothing Then _
gobjCommandBarButton(intIndex).Caption = Sh.Name
Next
End Sub
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Private Const BUTTON_TAG = "Namensanzeige"
Public gobjCommandBarButton(1 To 2) As CommandBarButton
Public Sub Create_CommandbarButton()
Dim intIndex As Integer
Call Delete_CommandbarButton
For intIndex = 1 To 2
Set gobjCommandBarButton(intIndex) = _
CommandBars(intIndex).Controls.Add(Type:=msoControlButton, Temporary:=True)
With gobjCommandBarButton(intIndex)
.Caption = ActiveSheet.Name
.Style = msoButtonCaption
.Tag = BUTTON_TAG
.Width = 100
End With
Next
End Sub
Public Sub Delete_CommandbarButton()
Dim intIndex As Integer
For intIndex = 1 To 2
Set gobjCommandBarButton(intIndex) = Application.CommandBars(intIndex).FindControl(Tag:=BUTTON_TAG)
If Not gobjCommandBarButton(intIndex) Is Nothing Then
gobjCommandBarButton(intIndex).Delete
Set gobjCommandBarButton(intIndex) = Nothing
End If
Next
End Sub
Gruß
Nepumuk