Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

in Excel-Formeln verwendete Bereichsnamen finden

in Excel-Formeln verwendete Bereichsnamen finden
21.01.2015 01:09:38
Heiko
Hallo,
ich habe eine Frage zu folgendem Szenario:
Ich habe ein Workbook mit einem (Master-)'Eingabeblatt' in dem alle Eingabezellen mit Bereichs-Namen versehen sind.
Über dieses Eingabeblatt werden alle (Slave-)Blätter mit Werten gefüllt.
Nicht jedes dieser (Slave-)Blätter ist immer sichtbar, das wird je nach Eingabe auf dem Mastersheet via VBA gesteuert.
Jedes einzelne Slavesheet verwendet nur einen Teil der auf dem Mastersheet vorhandenen Bereichs-Namen, kann jedoch eigene (lokale) Bereichsnamen haben, die aber dann auch nur auf diesem Slavesheet verwendet werden.
Jetzt möchte ich, da ich nicht auswendig weiß, welche Felder auf dem Mastersheet bei welchselnden Kombinationen von eingeblendeten Slavesheets nun auch tatsächlich befüllt sein müssen, die Formeln der sichbaren Slavesheets nach Namen durchsuchen.
Wenn Namen in Formeln gefunden werden und der Ursprung des Bereichs-Namen ist das Mastersheet, dann soll der Zellhintergrund der BereichsNamenZelle auf dem Mastersheet farblich hervorgehoben werden.
Etwas schwierig zu erklären - ich hoffe trotzdem, daß Ihr mich versteht.
Über Lösungsansätze wäre ich echt dankbar.
Vielen Dank und beste Grüße
Heiko

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: in Excel-Formeln verwendete Bereichsnamen finden
21.01.2015 09:19:24
Ralf
Hallo Heiko,
Hilfe zur Selbsthilfe: schau mal in das Lokal/Direkt-Fenster, wenn der code stoppt, da findet sicher das richtige.
Gruß
Ralf
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nm As Name
For Each nm In ActiveWorkbook.Names
With nm
Debug.Print .Name, .Index, .RefersTo
Stop
End With
Next
End Sub

AW: in Excel-Formeln verwendete Bereichsnamen finden
21.01.2015 11:07:14
Heiko
Hallo Ralf,
Danke für Deine Antwort!
Soweit war ich schon, aber dabei werden ALLE Bereichsnamen ALLER Sheets gefunden bzw. ausgegeben.
Es soll jedoch nur auf den jeweils sichtbar geschalteten Sheets in den vorhandenen EXCEL-FORMELN nach Namen gesucht und mit den auf dem Mastersheet verankerten Bereichsnamen verglichen werden.
Statt 'ActiveWorkbook' einfach 'Sheets' oder 'Worksheets' zu verwenden endet mit Fehler!
Gruß
Heiko

Anzeige
AW: in Excel-Formeln verwendete Bereichsnamen finden
21.01.2015 11:18:07
yummi
Hallo Heiko,
da du nicht mit Activeworkbook arbeiten willst, nehme ich an, dass du nicht nur das sichtbare Sheet abfragen, sondern alle Sheets, die eingeblendet sind, also ausgewählt werden könnten.
Das kannst du so machen:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nm As Name
For Each nm In ActiveWorkbook.Names
With nm
if nm.visible = true then
Debug.Print .Name, .Index, .RefersTo
end if
End With
Next
Gruß
yummi
End Sub

AW: in Excel-Formeln verwendete Bereichsnamen finden
21.01.2015 11:56:21
Heiko
Hallo yummi,
das funktioniert so auch nicht, d.h. das Ergebnis ist das Gleiche wie vorher: Es werden alle Namen auf allen Blättern gefunden, egal ob sichtbar oder unsichtbar.
Es soll ausschließlich in den EXCEL-Formeln der sichtbaren SlaveSheets nach evtl. verwendeten NAMEN gesucht werden!
1. VBA muss also erkennen, ob ein oder mehrere Blatt sichtbar geschaltet ist
2. Wurde ein sichtbares Blatt gefunden, dann muss VBA alle 'nicht leeren' Zellen dieses Blattes auf Excel-Formeln z.B. '=WENN(blahblah = 10;nanana;soso)' prüfen. Möglicherweise geht da was mit .FormulaLokal?
3. Wurde eine Zelle mit Excel-Formel gefunden, dann muss VBA feststellen, ob darin Namen, also in diesem Fall 'blahblah, nanana und soso' vorkommen.
4. Haben diese Namen ihren Referenzpunkt auf dem Mastersheet (Tabelle1), dann soll VBA bei den entsprechenden Zellen auf dem Mastersheet die Hintergrundfarbe ändern (naja...das ist leicht zu machen)
Noch besser kann ich es jetzt nicht erklären.
Danke und Gruß
Heiko

Anzeige
AW: in Excel-Formeln verwendete Bereichsnamen finden
21.01.2015 15:37:19
Heiko
Ich hab's jetzt doch selbst hinbekommen!
Wenn es aber jemand noch eleganter oder schneller coden kann, dann nur her mit den Vorschlägen :-)
Danke und Gruß
Heiko
Sub HighlightRequiredCells()
Dim sht As Worksheet
Dim nm As Name
Sheets("Auftragsdaten").Range("A3:J36").Interior.Color = RGB(221, 217, 196)
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible = xlSheetVisible And sht.Name  "Auftragsdaten" Then
sht.Activate
sht.UsedRange.Select
For Each CELL In Selection
If CELL.HasFormula = True Then
For Each nm In ActiveWorkbook.Names
If Left(nm.RefersTo, 14) = "=Auftragsdaten" Then
If InStr(1, CELL.Formula, nm.Name) > 0 Then
Range(nm.RefersTo).Interior.Color = RGB(196, 189, 151)
End If
End If
Next nm
End If
Next CELL
End If
cells(1, 1).Select
Next sht
Sheets("Auftragsdaten").Activate
Sheets("Auftragsdaten").BtnCreateCSV.Activate
End Sub

Anzeige

303 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige