Excel 2013: Benannte Bereiche auslesen / löschen

[sharky51]

Hallo zusammen,

in diesem tollen Forum findet man ja meist kompetente Hilfe, hoffe auch zum aktuellen Thema.
Von Uwe habe ich hier zwei Makros gefunden die fast schon alles machen, aber nur fast.

Meine Frage ist, wie kann ich alle definierten Bereichsnamen von nur dem aktuellen Tabellenblatt auslesen?

Dieser Code listet oder löscht alle Namen des Workbooks. Ich dachte ActiveWorkbook mit ActiveSheet zu ersetzen reicht aus...leider nicht.

Code:

Sub meineNamen()
Dim myNames As Names, StrTmp As String, i As Integer
Set myNames = ActiveWorkbook.Names
   For i = 1 To myNames.Count
      StrTmp = StrTmp & myNames(i).Name & myNames(i).RefersTo & Chr(10)
   Next
MsgBox StrTmp
End Sub

Sub AlleNamenLoeschen()
 Dim oName As Name
 For Each oName In ActiveWorkbook.Names
   oName.Delete
 Next oName
End Sub

Vielleicht hat jemand einen Tipp?

[RPP63]

Hallo!
Ganz so einfach ist es ja nun nicht ...  :21:
Teste mal:

Sub AlleNamenInAktSheetLoeschen()
 Dim oName As Name
 For Each oName In ThisWorkbook.Names
   If InStr(oName.RefersTo, ActiveSheet.Name) = 2 Then oName.Delete
 Next oName
End Sub

Du kannst natürlich ThisWorkbook durch ActiveWorkbook ersetzen, ganz nach Gusto.

Gruß Ralf

[sharky51]

Hallo!
Ganz so einfach ist es ja nun nicht ...  :21:
Teste mal:

Sub AlleNamenInAktSheetLoeschen()
 Dim oName As Name
 For Each oName In ThisWorkbook.Names
   If InStr(oName.RefersTo, ActiveSheet.Name) = 2 Then oName.Delete
 Next oName
End Sub

Du kannst natürlich ThisWorkbook durch ActiveWorkbook ersetzen, ganz nach Gusto.

Gruß Ralf

Hallo Ralf,

was bedeutet denn die "2" ?

[RPP63]

Hi!
Ist die Fundstelle des Tabellennamens im Namensbezug!

Beispiel:
ActiveSheet.Name ergibt Murks
ActiveWorkbook.Names(1).RefersTo ergibt =Murks!$A$1:$C$21

Du findest Murks an zweiter Stelle im Bezug.

Gruß Ralf

[sharky51]

Hallo zusammen,

ich hätte da noch eine Frage zu "Benannte Bereiche auslesen".

Ich hoffe ich beschreibe das nicht zu kompliziert...also....
In mehreren benannten Gesamt-Bereichen (SumErgebnis1, SumErgebnis2, SumErgebnis3) führe ich per Doppelklick (Makro) Berechnungen durch. Dabei prüfe ich in welchem benannten Bereich ich mich befinde. Die Ergebniszelle wiederum benenne ebenfalls mit einem Namen (z.B. November_Bereich1, November_Bereich2, November_Bereich2). Ich habe jetzt einen benannten Bereich in einem benannten Bereich.
Das funktioniert auch sehr gut. Wenn ich aber wieder in diese, jetzt benannte Ergebnis-Zelle, per Doppelklick eine neue Berechnung durchführe kommt es vor dass ich bei der Prüfung auf den Gesamt-Bereich  (SumErgebnis1, SumErgebnis2, SumErgebnis3) nicht das erwartete Bereichsergebnis zurück bekomme ...sondern den Ergebnis-Namen wie z.b.  "November_Bereich1". Ich benötige aber wieder den Gesamtbereich. Wie geht das in diesem speziellen Fall, geht das überhaupt? Und wie kann die Bereichsabfrage nur auf das aktuelle Tabellenblatt eingeschränkt werden?

Den Bereich prüfe ich mit diesem Makro:

Code:

Sub Bereich_ermitteln()
 Dim oName As Name
 For Each oName In ActiveWorkbook.Names
   If Not Application.Intersect(ActiveCell, ActiveSheet.Range(oName.RefersToRange.Address)) Is Nothing Then
     'MsgBox "Aktive Zelle gehört zu dem " & "' " & (oName.Name) & " '" & " mit Namen versehenen Bereich"
     Bereich = oName.Name
     Exit Sub
   End If
 Next
 MsgBox "Aktive Zelle gehört zu keinem mit Namen versehenen Bereich"
End Sub

Vielen Dank für Eure Hilfe!

[sharky51]

Hallo zusammen,

Wie geht das in diesem speziellen Fall, geht das überhaupt? Und wie kann die Bereichsabfrage nur auf das aktuelle Tabellenblatt eingeschränkt werden?

Edit:
Ich habe eine Lösung gefunden indem ich zuerst den Unterbereichs-Namen der aktiven Zelle lösche (wenn vorhanden) und dann dann wieder den Bereichsnamen abfrage. Ist vielleicht etwas umständlich aber es funktioniert.

Vielleicht habt Ihr mir trotzdem eine elegantere Lösung?

Code:

Sub Bereich_ermitteln()
 Dim oName As Name
 
 Call Zellenname_auslesen
 
 For Each oName In ActiveSheet.Names
   If Not Application.Intersect(ActiveCell, ActiveSheet.Range(oName.RefersToRange.Address)) Is Nothing Then
     MsgBox "Aktive Zelle gehört zu dem " & "' " & (oName.Name) & " '" & " mit Namen versehenen Bereich"
     Bereich = oName.Name
     'MsgBox Names(Bereich).RefersTo.Name
     Exit Sub
   End If
 Next
 MsgBox "Aktive Zelle gehört zu keinem mit Namen versehenen Bereich"
End Sub

Sub Zellenname_auslesen()
 ' Auslesen der aktiven oder einer bestimmten Zelle
 Dim Zellenname As String
 '
 Zellenname = NameDieserZelle(ActiveCell)
 If Zellenname = "" Then
   MsgBox "Kein Name vergeben"
   Exit Sub
 Else
   MsgBox Zellenname
   MsgBox ActiveCell.Name.Name & " wird gelöscht"
   ActiveCell.Name.Delete
 End If
End Sub

Public Function NameDieserZelle(ByVal Cell As Range) As String
 On Error Resume Next
 NameDieserZelle = ActiveCell.Name.Name
 On Error GoTo 0
End Function

[schauan]

Hallöchen,

eventuell reciht es schon, wenn Du im ersten Makro statt Call ... den Namen direkt löschst:
On Error Resume Next
ActiveCell.Name.Delete
On Error GoTo 0