An Matthias G - Bezug auf 06.10.2003 21:20:2

Bild

Betrifft: An Matthias G - Bezug auf 06.10.2003 21:20:2
von: Reservist
Geschrieben am: 08.10.2003 09:20:19

Hallo Matthias,

Mein Feedback kommt spät, da ich bei Erhalt deiner Super-Antwort
bereits das Licht ausgeknipst hatte.
Ersteinmal tausend Dank für dein VBA-Text.
Obwohl "0-Ahnung" davon, hab`ich es nach einiger Zeit hinbekommen,
mit kleiner Änderung des "Public Const Bereich".
Kam wo von mir falsch rüber.
Das Auswahlfeld liegt in Tabelle '0' der Bezug in Tabelle 'Adressen'.
Spalte „D“ hatte ich im Nachgang zu „B“ geändert.
-------------------------------------------------------------------------------------------
Option Explicit
Public Const Bereich = "'Adressen'!$B$2:'Adressen'!$B$2000"


Sub UpdateListe()
Const Hilfsspalte = 47 'Spalte AU
Dim z
Dim i As Long
i = 1
Application.EnableEvents = False
For Each z In Range(Bereich)
If z.Value <> "" Then
Cells(i, Hilfsspalte).Value = z.Value
i = i + 1
End If
Next z
Cells(i, Hilfsspalte).Value = ""
Application.EnableEvents = True
ActiveWorkbook.Names.Add Name:="Liste", RefersTo:= _
"=Adressen!" & Range(Cells(1, Hilfsspalte), Cells(i, Hilfsspalte)).Address
End Sub

------------------------------------------------------------------------------------------------
Die Aktualisierung erfolgt jetzt über Schaltfläche mit Makro „

Sub UpdateListe()“.
War das so gedacht? Denn von ganz alleine aktualisiert sich die Hilfsspalte
„AU“ in Tabelle ‚0’ nicht.
Die heißt für mich ich muss jetzt 2-mal aktualisieren. Die Tabelle ‚Adressen“
ist verknüpft mit einer Access Datenbank. In Spalte  B bis N sind 
entsprechende Adressdaten.
Zwar wird diese beim Öffnen aktualisiert, trotzdem ist zusätzliche Aktualisierung
erwünscht, da zwischenzeitlich Adressdaten ändern bzw. hinzukommen.
Toll wäre, wenn ich beide Aktualisierungen über 1 Schaltfläche vornehmen
könnte.
-------------------------------------------------------------------------------------------------
Und es hat sich noch ein weiteres Problem ergeben, wo ich mit Beginn
der Arbeit dachte dies bekomme ich hin (war wieder maßlos überschätzt).
Jetzt habe ich zwar in Tabelle ‚0’ über das Listenfeld meinen gewünschten 
Adressnamen aus Tabelle ‚Adressen’! „Spalte B“ aber ich brauche noch die 
zugehörigen Daten aus Spalte „C“ bis „N“
Bsp. liegt Listenfeld in ‚0’!D5  -  muss nach Auswahl des Adressnamens 
in G5 die zugehörige Strasse stehen.
Habe viel probiert aber bekomme es nicht hin. Ein Ansatz war:
'=WENN((D5)<>0;(Adressen!B&WERT(TEIL(ZELLE("dateiname";D5);FINDEN("]";ZELLE("dateiname";D5))+ ???   ;““
Ich stelle diese Frage ggf. heute Abend noch mal separat in`s Forum.
Du hast mir bereits beim größten Brocken geholfen – nochmals Danke
und Gruß
Thorsten
Bild


Betrifft: AW: An Matthias G - Bezug auf 06.10.2003 21:20:2
von: Matthias G
Geschrieben am: 08.10.2003 15:43:11

Hallo Thorsten,
erstmal Danke für die Rückmeldung.

Für die Aktualisierung habe ich zwei Prozeduren vorgesehen, die UNBEGINGT im Codemodul der Tabelle "Adressen" stehen müssen (Doppelklick auf "Tabelle(x) (Addressen)" im VBA-Editor im Projektexplorer):

Erstens, die Aktualisierung wird bei manueller Änderung des Zellbereichs durchgeführt:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Bereich), Target) Is Nothing Then Exit Sub
UpdateListe
End Sub

Die Prozedur "Worksheet_Change" wird immer dann ausgeführt, wenn das Tabellenblatt verändert wurde (manuell oder per VBA). Der Name ist festgelegt und darf nicht geändert werden!

Zweitens, die Aktualisierung wird bei Änderung der Zellbereichs durch Neuberechnung durchgeführt:

Private Sub Worksheet_Calculate()
UpdateListe
End Sub

"Worksheet_Calculate" wird immer bei Neuberechnung des Blattes durchgeführt, z.B. bezieht Zelle "A1" sich in seiner Formel auf Tabelle5, Zelle B6, wenn diese Zelle B6 geändert wird, ändert sich der Inhalt von Zelle A1. Dies löst jedoch kein Worksheet_Change-Ereignis aus, sonder das Worksheet_Calculate-Ereignis.

WICHTIG nochmal, o.g. Prozeduren müssen ins Codemodul der Tabelle "Adressen", und NICHT in ein Standardmodul, dort werden sie bei den o.g. Ereignissen nicht automatisch ausgeführt.

Je nachdem, wie sich deine Daten verändern, kannst du auf die Change- oder Calculate-Prozedur verzichten. Wenn Du die Daten ausschließlich von einer Datenbank beziehst, kannst Du wohl auf das Change-Ereignis verzichten (musst du mal testen).

---------------------------------------------------

Ein kleines Problem bei meiner Prodedur "UpdateListe" ist mir noch aufgefallen:
Die Hilfsliste wird immer im Aktiven Arbeitsblatt angelegt. Wenn dies beim Aufruf immer das Blatt "Adressen" ist, ist das kein Problem (war durch meine Ereignisprozeduren auch so vorgesehen). Wenn du aber auf Tabelle "0" eine Schaltfläche legst, wird die Hilfsspalte in Tabelle "0" angelegt.
Um Eindeutigkeit herzustellen, hier der leicht veränderte Code von UpdateListe:
Option Explicit
Public Const Bereich = "'Adressen'!$B$2:'Adressen'!$B$2000"
Sub UpdateListe()
Const Hilfsspalte = 47 'Spalte AU
Dim z
Dim i As Long
i = 1
Application.EnableEvents = False
With Sheets("Adressen")
For Each z In Range(Bereich)
If z.Value <> "" Then
.Cells(i, Hilfsspalte).Value = z.Value
i = i + 1
End If
Next z
.Cells(i, Hilfsspalte).Value = ""
End With
Application.EnableEvents = True
ActiveWorkbook.Names.Add Name:="Liste", RefersTo:= _
"=Adressen!" & Range(Cells(1, Hilfsspalte), Cells(i, Hilfsspalte)).Address
End Sub

Wie du vielleicht siehst, bezieht sich Cells(..) jetzt immer auf die Tabelle "Adressen" (With Sheets("Adressen") ... End With)


Dein neues Problem schau ich mir heute abend an, dann habe ich mehr Zeit.
Muss mich jetzt um die Kinder kümmern! :-)

Viele Grüße,
Matthias


Bild


Betrifft: AW: An Matthias G - Bezug auf 06.10.2003 21:20:2
von: Matthias G
Geschrieben am: 08.10.2003 23:24:11

Hallo Thorsten,
zu deinem neuen Problem:
Benutze die Funktion SVERWEIS. neben den Zellen mit der Listenauswahl (hier: D5) folgende Formel:

=SVERWEIS(D5;Adressen!B2:N2000;2;FALSCH)

wobei sich die "2" auf die zweite Spalte im angegebenen Bereich bezieht, also Spalte C.
für Spalte D verwnde 3, für E 4 usw.

Ich hoffe, ich habe Dich richtig verstanden.

Grüße,
Matthias


Bild


Betrifft: AW: An Matthias G - Bezug auf 06.10.2003 21:20:2
von: Resevist
Geschrieben am: 10.10.2003 09:40:38

Hallo Matthias G.

war 2 Tage auf Montage. Gestern Abend habe ich dann mit Freude versucht
Deine neuen Hilfen umzusetzen.

Formel funktioniert - war genau das was ich wollte - Danke
Sieht jetzt so aus:

=WENN((D5)<>0;SVERWEIS(D5;Adressen!B$2:N$2001;2;FALSCH);"")

Kurz zum Aufbau der Mappe
--------------------------------------
'Adressen'! = Import aus einer Access-Datenbank (Hier werden keine Eingaben gemacht).
'LF'! = enthält zahlreiche Auswahlkriterien für Listenfelder (Hier werden keine Eingaben gemacht).
'1'! bis '99'! = 99 identische Tabellen (5-seitiges-A4-Formulare)
'0' = enthält Vorgaben für alle Tabellen und wertet diese aus. (Hier werden außer Auswahl aus Listenfeld keine weiteren Eingaben gemacht).

Tabelle ‚0’ enthält also 99 Datensätze
Wenn ich in „D5“ (3-ter Datensatz) über das Listenfeld den Firmennamen auswähle,
müssen alle zusätzlichen Daten, welche die Importtabelle ‚Adressen’ zu den ausgewählten Namen
liefert in Tabelle ’3’ erscheinen.
Bei Auswahl von „D6“ dann in Tabelle ’4’ u.s.w.
Die Tabellen 1-99 geben nach Ausfüllung, Werte an ‚0’ zurück.
---------------------------------------------------------------------------------------------------------------------------------

1.) Im VBA-Editor unter Tabelle53 (Adressen) steht nun:


Private Sub Worksheet_Caculate()
UpdateListe
End Sub


In Tabelle ‘0’ habe habe ich eine Schaltfäche angelegt und über Macro “UpdateListe” ausgewählt.
Bei Klick wird das Listenauswahlfeld aktualisiert.
Dies funktioniert - dauert aber 1:20 min. Ist das Normal?

2.) Gleiches Vorgehen unter Tabelle53 (Adressen) steht:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Bereich), Target) Is Nothing Then Exit Sub
UpdateListe
End Sub


Aktualisierung dauert 2:45 min. mit Ergebnis „nicht aktualisiert“



Im „Modul1“ steht:

Option Explicit
Public Const Bereich = "'Adressen'!$B$2:'Adressen'!$B$2000"
Sub UpdateListe()
Const Hilfsspalte = 47 'Spalte AU
Dim z
Dim i As Long
i = 1
Application.EnableEvents = False
'With Sheets("Adressen")
For Each z In Range(Bereich)
If z.Value <> "" Then
Cells(i, Hilfsspalte).Value = z.Value
i = i + 1
End If
Next z
Cells(i, Hilfsspalte).Value = ""
End With
Application.EnableEvents = True
ActiveWorkbook.Names.Add Name:="Liste", RefersTo:= _
"=Adressen!" & Range(Cells(1, Hilfsspalte), Cells(i, Hilfsspalte)).Address
End Sub


Bei deiner neuen Variante funktioniert bei mir kein Update, weder mit o. g. Variante 1 noch Variante 2
--------------------------------------------------------------------------------------------------------------------------------------
Die Mappe soll demnächst in Arbeitseinsatz gehen, womit auch andere Mitarbeiter arbeiten müssen.
Es kann durchaus sein, dass beim Einsatz z.B. eine Firma noch nicht enthalten ist.
Dies heißt dann wechseln in Access-Datenbank und Daten eingeben und zurück.

Jetzt müssen sie erst in Tabelle ‚Adressen’ und externe Daten aktualisieren, danach Wechsel in Tabelle ‚0’ und erneut aktualisieren (Schaltfläche UpdateListe), damit die Daten in die Liste übernommen werden.
Ich möchte gern ein Arbeitsgang einsparen, sodass man nie in Tabelle „Adressen“ wechseln muss.

In Tabelle ‚0’ wollte ich eine Schaltfläche „Aktualisieren“, welche bei Klick beide Vorgänge auslöst,
also externe Daten und anschließend Listenfeld, bzw. die Hilfspalte ‚0’!AU aktualisieren.
--------------------------------------------------------------------------------------------------------------------------------------
Wenn du Lust hast freue ich mich auf deine Antwort – ansonsten muss es halt auch so gehen.
Bin auch so zufrieden.

Danke noch mal für deine viele Mühe, welche mir sehr geholfen hat.

Gruß aus Leipzig
Thorsten


 Bild

Beiträge aus den Excel-Beispielen zum Thema " Fußzeile Variabel mit Datum aus Tabelle bestimmen"