Excel 2013 VBA: Wo bin ich?

[sharky51]

Hallo zusammen,

trotz langem suchen in den Foren habe ich zu meinem Problem nichts richtiges gefunden.

In einem Workbook habe ich mehrere Tabellenblätter und in diesen sind viele Bereiche mit Namen definiert.
Klicke ich jetzt per Doppelklick in eine beliebige Zelle einer Tabelle möchte ich wissen ob und in welchem benannten Bereich ich mich befinde, oder ob die Zelle in keinem benannten Bereich liegt.

Wie müsste der Code mit einer VBA-Funktion oder einer Sub-Routine aussehen?
Wäre toll wenn jemand eine Idee hätte.

Vielen Dank für Eure Hilfe!

[shift-del]

Hallo Erich

Vielleicht reicht ja auch schon dies: Setze den Zoom auf einen Wert <=34%.

[Kuwer]

Hallo Erich,

folgender Code kommt in das schon vorhandene VBA-Modul der entsprechenden Tabelle:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Dim oName As Name
 Dim rngR As Range
 Dim strR As String
 
 On Error Resume Next
 For Each oName In ThisWorkbook.Names
   Set rngR = Nothing
   Set rngR = oName.RefersToRange
   If Not rngR Is Nothing Then
     If Not Application.Intersect(Target, rngR) Is Nothing Then
       strR = strR & vbNewLine & oName.Name
     End If
   End If
 Next oName
 On Error GoTo 0
 
 Cancel = True
 
 If Len(strR) Then
   strR = "Die Zelle """ & Target.Address(0, 0) & """ ist in folgenden benannten Bereichen enthalten:" & strR
 Else
   strR = "Die Zelle """ & Target.Address(0, 0) & """ ist in keinem benannten Bereich enthalten."
 End If
 
 MsgBox strR
End Sub

Gruß Uwe

[sharky51]

Hallo Uwe,

vielen Dank für diesen Code, er funktioniert so dass alle benannten Bereiche des Workbooks aufgelistet werden. nicht schlecht.
Das hilft mir aber nicht so ganz weiter.

Wissen möchte ich nur ob die aktuell doppelt angeklickte Zelle sich in einem "benannten Bereich" befindet und in welchem?

Hättest Du da noch eine Idee, bzw. wie Dein VBA-Code abgeändert werden müsste.

Vielen Dank!

[Kuwer]

Wissen möchte ich nur ob die aktuell doppelt angeklickte Zelle sich in einem "benannten Bereich" befindet und in welchem?

Hallo Erich,

genau das macht der Code (bei mir).
Vielleicht habe ich das aber auch falsch verstanden. Dann wäre eine Beispieldatei von Dir sehr hilfreich.

Gruß Uwe

[schauan]

Hallo Sharky,

wenn sich mehrere oder alle benannten Bereiche so überschneiden, dass es eine Zelle gibt, die in mehreren oder gar allen Bereichen enthalten ist, werden Dir auch mehrere oder alle Bereiche ausgegeben.

[sharky51]

Hallo André,

danke für die Rückmeldung aber so wie ich das sehe trifft das nicht zu.
Es überschneiden sich keine Bereiche, zumindest kann ich es nicht feststellen wenn ich sie einzeln aktiviere.
Gäbe es eine Möglichkeit das elegant herauszufinden und aufzulisten/protokollieren?

Danke für die Hilfe!

[schauan]

Hallo Sharky,

Du könntest in Uwe's Code nach dieser Zeile:
Set rngR = oName.RefersToRange
das einfügen:
Debug.Print rngR.Address, rngR.Parent.Name

Dann bekommst Du im Direktfenster (musst Du eventuell über das Menü "Ansicht" aktivieren) die Adressen und Blattnamen ausgegeben und kannst prüfen, ob sich Bereiche überschneiden.

[sharky51]

Hallo André

ok das hat mir jetzt ein wenig weitergeholfen.
Im Direktfenster werden alle Bereiche aufgelistet und sehe ich jetzt dass es keine Überschneidungen von Bereichen gibt.

Möchte aber trotzdem nochmals auf meine ursprügliche Frage zurückkommen.
Eine Auflistung aller Bereichsnamen benötige ich hier nicht.
Es soll nur der eine Bereichsname zurückgegeben werden von der aktuell doppelt angeklickten Zelle und die Zell-Adresse oder wenn es sich um verbundene Zellen handelt dann den Zellbereich.

[shift-del]

Hallo Erich

Mein Beitrag #2 war übrigens ernst gemeint.
Und es läßt sich auch mit Macro-Recorder aufzeichnen.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  ActiveWindow.Zoom = 34
End Sub