Sheet.Name begrenzen
07.12.2003 19:08:41
Lorenz K.
Mit dem folgenden Code werden alle Sheets in der befindlichen Mappe angesprochen außer des Activen.
Was ist zu machen um nur die in InStr angeführten, anzusprechen.
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If InStr(1, "1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.", Sh.Name) <> 0 Then
Dim wks As Worksheet
Dim oPopUp As CommandBarPopup
Dim oBtn As CommandBarButton
On Error Resume Next
Application.CommandBars("Cell").Controls("wähle Blatt mit selben Bereich").Delete
On Error GoTo 0
Set oPopUp = Application.CommandBars("Cell").Controls.Add(msoControlPopup)
oPopUp.Caption = "wähle Blatt mit selben Bereich"
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 If
End Sub
BasMain:
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
Danke und
Grüße Lorenz