AW: Namen mit INDIRECT ?
11.05.2009 11:11:59
Boris
Hallo Daniel,
den Einsatz von Namen, die per VBA gesteuert werden, halte ich für eine sinnvolle Lösung. Damit kann ich auch das Verwenden von Indirekt und Bereich.Verschieben vermeiden. Allerdings weiß ich nicht genau, wie ich das am besten umsetze, vielleicht kannst Du mir hier helfen...
Hier mein erstes Gedankenkonstrukt: In Zeile 1 stehen die Spaltenüberschriften, in Zeile 2 stehen (dynamische) Referenzen als Text (z.B. RR!A10:A2000). Folgende Funktionen müssen gewährleistet werden:
- Wenn man in Zeile 1 in einer leeren Zelle eine Überschrift eingibt, soll ein Name erstellt werden, mit RefersTo = Inhalt der darunter liegenden Zelle.
- Wenn sich der Bezug in Zeile 2 ändert, z.B. weil Zeilen hinzugefügt wurden, müssen alle Namen angepasst werden.
- Wenn man eine Zelle in Zeile 1 löscht, muss der Name gelöscht werden.
- Wenn man eine Zelle in Zeile 1 ändert, muss der vorherige Name gelöscht und ein neuer erstellt werden.
- Das Einfügen und Löschen ganzer Spalten muss auch funktioneren.
Habe bisher folgende Lösung, die aber noch nicht sehr ausgereift ist:
In ein Worksheet, in dem die Namen automatisch angepasst werden, steht folgendes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row 1 And Target.Row 2 Then Exit Sub
Call NamenUpdaten
End Sub
Sub NamenUpdaten()
Dim Name As String
Dim Bezug As String
Dim rng As Range
Dim n As Name
For Each n In ThisWorkbook.Names
If Split(ThisWorkbook.Names.Item(n.Name).RefersTo, "!")(0) = "=" & CStr(ActiveSheet.Name) Then n.Delete
Next
For Each rng In ActiveSheet.Rows(1).SpecialCells(xlCellTypeConstants)
Name = ActiveSheet.Name & "." & rng.Value
Bezug = rng.Offset(1, 0).Value
ActiveWorkbook.Names.Add Name:=Name, RefersTo:="=" & Bezug
Next
End Sub
Blöd ist, dass ich ActiveSheet verwende. Ich weiß allerdings nicht, wie ich mit Hilfe des Targets das entsprechende Sheet ermittle...?
Mein Makro löscht also alle Namen, die sich auf das entsprechende Blatt beziehen und erstellt für jeden Wert in Zeile 1 die Namen neu. Nicht allzu elegant. Kann man das auch Spaltenweise lösen? Das Einfügen und Löschen von Spalten könnte hier Probleme bereiten.
Ein andere Sache, die nicht funktioniert ist: Wenn sich die Werte der Zeile 2 verändern, z.B. weil man eine Zeile eingefügt hat, wird das WorkSheet_Change-Ereignis nicht ausgelöst, da in Zeile 2 Formeln stehen. Sollte ich also parallel ein WorkSheet_Calculate-Ereignis einbauen?
So viele Fragen....
Gruß, Boris
PS: Was muss ich hier im Editor tun, um nach eingefügtem Code wieder normal weiterschreiben zu können?