Thema: Feste Fenster- und Cursorpositionen bei Blattwechsel

ClassModule: DieseArbeitsmappe

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   On Error Resume Next
   Application.CommandBars("Cell").Controls("GoTo").Delete
   On Error GoTo 0
End Sub

Private Sub Workbook_Open()
   Call Workbook_SheetBeforeRightClick(ActiveSheet, ActiveCell, False)
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   Dim wks As Worksheet
   Dim oPopUp As CommandBarPopup
   Dim oBtn As CommandBarButton
   On Error Resume Next
   Application.CommandBars("Cell").Controls("GoTo").Delete
   On Error GoTo 0
   Set oPopUp = Application.CommandBars("Cell").Controls.Add(msoControlPopup)
   oPopUp.Caption = "GoTo"
   For Each wks In Worksheets
      If wks.Name <> ActiveSheet.Name Then
         Set oBtn = oPopUp.Controls.Add
         With oBtn
            .Caption = wks.Name
            .OnAction = "GoToWks"
            .Style = msoButtonCaption
         End With
      End If
   Next wks
End Sub
StandardModule: Modul1

Sub GoToWks()
   Dim iRow As Integer, iCol As Integer
   Dim sRange As String
   iRow = ActiveWindow.ScrollRow
   iCol = ActiveWindow.ScrollColumn
   sRange = ActiveCell.Address
   Worksheets(CommandBars.ActionControl.Caption).Select
   ActiveWindow.ScrollRow = iRow
   ActiveWindow.ScrollColumn = iCol
   Range(sRange).Select
End Sub

Beiträge aus dem Excel-Forum zu den Themen Menue und Context