Office: Liste mit Formular füllen und weiter bearbeiten (VBA Projekt?)

Hi Bernd,

man kann nicht nur de letzte belegte Zeile (oder erste freie Zeile) in einer Spalte ermitteln sondern genau so die letzte belegte Spalte in einer Zeile. Den Code muss man dann nur ein kleinwenig anapssen:

Code:

   Dim lngErste As Long
   Dim intZeile As Integer
   Dim intSpalte As Integer
   Dim intLetzte As Integer
   intSpalte = 6
   If Range("D4") = "Erfassung" Then
      With Worksheets("Bewerber")
         lngErste = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count) + 1
         intLetzte = IIf(IsEmpty(.Cells(1, .Columns.Count)), _
            .Cells(1, .Columns.Count).End(xlToLeft).Column, .Columns.Count)
         Range(Cells(24, 1), Cells(24, intLetzte)).Copy
         .Cells(lngErste, 1).PasteSpecial Paste:=xlValues
         .ListObjects("Table2").Resize Range(Cells(1, 1), Cells(lngErste, intLetzte))
      End With
      ActiveSheet.Unprotect
      Union(Range("C8:E8"), Range("G8"), Range("O16:P16").Range("N17:N19")).ClearContents
      Range("J8:J13") = False
      Range("N8:N13") = False
      ActiveSheet.Shapes("DD_79").ControlFormat.Value = 0
      ActiveSheet.Protect

Mit dem Empfehlen von Literatur ist es immer etwas kompliziert, weil es - so wie das im Leben nun mal ist - dazu unterschiedliche Meinungen gibt. Aber vielelicht hilft dir zum Anfang ja schon mal dies: http://www.excel-center.de/excel/handbuch.php

Bis später,
Karin

 

Den Anhang RE_Muster_Stellen_V06.xlsm betrachten Hi Karin,

mittlerweile ist das Formular im Erfassungmosdus schon wirklich gut. Diese Art gefällt mir ausgesprochen gut und ich werde sie sicher öfter verwenden.
Ich glaube das zusammengefasste kopieren eienr Zeile mach das ganze wirklich einfach. Jetzt könnten wir das zurücklesen und editieren angehen. Meinst du dass mein Ansatz, die Datensätze per SVerweis/WVERWEIS in das Erfassungsblatt holen, dort die dann umgesetzten Felder per Formel wieder in Logik umwanden brauchbar ist. Ich habe mal in Zelle P5 den abzuholendenden Datensatz Key eingestellt und lese per Sverweis die Daten in Zeile 27 in Blatt Erfassung wieder zurück. Damit müssten die Felder die ausgefüllt wurden zurückgeschrieben werden und die Hacken der CheckBoxen gesetzt werden. Die Berechnung und Logik macht Excel. So brauchen die Felder mit Formeln nicht angefasst werden.

Gruß Bernd

 

Hi Bernd,

zuerst einmal müsstest du den Code für den ToggleButton ändern wegen des Blattschutzes:

Code:

Private Sub ToggleButton1_Click()
   Dim chkElement As CheckBox
   If ToggleButton1 Then
      ToggleButton1.Caption = "Daten erfassen"
      ActiveSheet.Unprotect
      Range("D4") = "Erfassung"
      With Range("M8:M13")
         .Locked = True
         .Value = False
      End With
      ActiveSheet.Protect
      For Each chkElement In ActiveSheet.CheckBoxes
         If chkElement.TopLeftCell.Column = 13 Then chkElement.Visible = False
      Next chkElement
   Else
      ToggleButton1.Caption = "Daten bearbeiten"
      ActiveSheet.Unprotect
      Range("D4").ClearContents
      Range("M8:M13").Locked = False
      ActiveSheet.Protect
      For Each chkElement In ActiveSheet.CheckBoxes
         If chkElement.TopLeftCell.Column = 13 Then chkElement.Visible = True
      Next chkElement
   End If
End Sub

Nun zum Erfassen: welche Zellen im Formular dürfen denn vom Bearbeiter verändert werden?

Bis später,
Karin

 

Hallo Karin,

den Code für den Blattschutz habe ich eingebaut. So wie bis jetzt sehen kann funktioniert das auch.

Für den Editiermodus sollen die folgenden Felder freigegeben sein:

L17
O16:P16
N17

Checkboxen: 7-12 (erledigt)
Checkboxen: 13-18 (Ort_1-Ort_6) wenn noch nicht angehakt (also Nachmeldemöglichkeit)

Feld für Übergabe: P5?


Für die Vorbereitung des Erfassungsformulars für den nächsten Datensatz sollten die Checkboxen wieder ohne Haken sein.

Grüsse
Bernd

 

Hi Bernd,

P5 hat jetzt ein DropDown-Listenfeld, in dem man die LFN auswählen kann, wenn man Daten bearbeiten will. Bei Auswahl werden die betreffenden Daten in das Formular geholt.

Bis später,
Karin

 

[Hallo Karin,

In der Version 08 treten noch folgende Effekte. So gehen in der Spalte L8:L13 im Erfassungsformular nach dem Wegschreiben die Formeln in den Zellen verloren. Ich habe die Formel welche dort reingehört in L6 geparkt. Die Formel [=IF(AND(Eingang_x;I6);Eingang+Zeit_Wochen*7;"")] prüft ob ein Eingangsdatum vorhanden ist, dann ob ein Ort angehakt ist und errechnet daraus das Rückgabe Datum welches in die Liste gelegt wird. Ich denke wir nähern uns langsam dem Ziel und ich kann dann mal die Arbeit auf die Userin loslassen. Wenn das zu aufwändig ist das Formular umzuschalten könnten wir auch für das Editieren ein zweites Blatt anlegen.

Zelle L17, O16, O17 und N17 kommen nicht im Daten Bearbeiten Modus nicht zurück. Die Felder 016 und P16 haben eine Datumsformatierung und zeigen 00.01.1900. Hier sollten natürlich etwas drinstehen wenn auch ein Datum eingetragen wurde.

Du machts das ganze echt toll. Ich mich habe bislang mit VBA noch nicht beschäftigt, habe jetzt aber einiges gelernt und werde sicher das eine oder andere Mal VBA nutzen. Wie ist eure Arbeit eigenlich organisiert und wie werdet bezahlt? Wie werden die Fälle aufgeteilt?


LG
Bernd

 

Hi Bernd,

wenn beim Bewerber in gewissen Zellen nichts drin steht, dann kann auch nichts geholt werden - Zellen O16, P16 und N17 sind im Code berücksichtigt, O17 ist nicht erforderlich, weil es eine verbundene Zelle ist (was ich persönlich als schlechte Lösung ansehe). Damit O16/P16 leer bleiben falls kein Datim vorhanden ist, kannst du den Code im Makro DatenHolen nach der Zeile Next intSpalte so umzuschreiben:

Code:

   If Range("O16") > 0 Then
      Range("O16") = Range("Y27")
   Else
      Range("O16").ClearContents
   End If
   If Range("P16") > 0 Then
      Range("P16") = Range("Z27")
   Else
      Range("P16").ClearContents
   End If

Im Makro DatenHolen habe ich extra einen Kommentar geschrieben, welche Zellen geleert werden - wenn die Formeln in L8:L13 überschrieben werden, dann musst du einfach diese Zeile im Code löschen.

Bis später,
Karin

 

Hallo Karin,

vielen Dank für die Infos. Den letzten Code habe ich eingebaut. Das das Abholen der Daten nicht in die richtigen Felder geschreiben wurde lag daran dass ich in der in Version 8 in Spalte AA ein Lehrzeichen im Erfassungsformular eingebaut hatte welches in Table1 nicht vorhanden ist. Deswegen hatte sich der Eintrag um eine Zeile verschoben. dadurch konnte der Sverweis welcher auf die Spaltennummer welche ich eingetragen habe zurückgreift nicht korrekt zuordnen.
Ich habe den Fehler in der Tabelle bereinigt, kann jetzt aber nicht sagen in wieweit sich das jetzt auf das Makro auswirkt. Durch diese Part blicke ich noch nicht richtig durch.

Dan habe ich solange an der Tabelle gebastelt und am Code rumprobiert das nun das Dropdown für das auswählen des zu bearbeitenden Satzes nicht mehr geht. Vielleicht siehst Du das ja schneller als ich was ich da gemacht habe.

Ich habe deine Anregung aufgenommen und die verbundene Zelle N17 aufgelöst. Dies ist inhaltlich nicht notwendig, wenn es Probleme bereitet können wir es weg lassen.

Beim Probieren habe ich bemerkt das es sinnvoll wäre aus dem laufenden Makro auszusteigen.

Das löschen der Formel in L8:L13 konnte ich nicht lokalisieren. vielelicht schaust Du da auch noch mal.

Viele Grüsse
und vielen, vielen Dank für deine Klasse Gedult mit mir.

Bernd

 

Hi Bernd,

DropDown-Listenfeld: du hast etwas gelöscht, auf das sich der definierte Name LFN bezog, der die Grundlage für das DropDown-Listenfeld bildet.

Was du damit meinst, dass du aus dem Makro "aussteigen" willst verstehe ich nicht. Man kann ein Makro vorzeitig beenden, indem man die betreffende Bedingung einfügt - z.B. If Range("A1") > 0 Then Exit Sub.

Formel in L8:L13: suche im genannten Modul nach dem Kommentar ' Rückgabe bis

Bis später,
Karin

 

Hi Karin,

Ich hatte nochmal von Version 08 aus versucht meinen Fehler zu reproduzieren. Das das Abholen der Daten nicht in das richtige Feld Weiterleitung geschreiben wurde liegt daran dass ich in der in Version 8 in Spalte AA ein Lehrzeichen im Erfassungsformular eingebaut hatte welches in Table1 nicht vorhanden ist. Der Eintrag landet im mit "Reserve" bezeichneten Feld. Deswegen hatte sich der Eintrag um eine Spalte verschoben. Dadurch konnte der Sverweis welcher auf die Spaltennummer welche ich eingetragen habe zurückgreift nicht korrekt zuordnen. Das Datum Weitergeleitet wird in die falsche Spalte geschrieben. Das löschen der Formel habe ich rauskommentiert. So wie es aussieht geht das so.

Ich habe den Fehler mit der in der Tabelle wieder belassen und versuche das zu verstehen in wieweit sich das auf das Makro auswirkt. Ich habe die Zieltabel1 angepasst und dort die Spalte eingefügt welche auch im Lesebereich vorhanden ist. Es sollten jetzt alle Tabellen den gleichen Aufbau haben. Durch diese Part blicke ich leider noch nicht richtig durch. Falls Du mir noch eine Tipp mitgeben kannst wäre ich Dir Dankbar.

Ich fahre dann nach Hause und melde mich Montag wieder.

Vielen Dank
Bernd

 

Hi Bernd,

ersetze den gesamten Code nach der Zeile Next intSpalte bis vor End Sub durch diesen:

Code:

   ' nur etwas übertragen wenn die betreffenden Zellen nicht leer sind
   If Range("Y27") > 0 Then
      Range("O16") = Range("Y27")
   Else
      Range("O16").ClearContents
   End If
   If Range("Z27") > 0 Then
      Range("P16") = Range("Z27")
   Else
      Range("P16").ClearContents
   End If
   If Range("AC27") > 0 Then
      Range("L17") = Range("AC27")
   Else
      Range("L17").ClearContents
   End If
   ' Stellenkennzeichen in Tabelle Stellen gefunden
   If Not rngZelle Is Nothing Then
      Range("C14") = rngZelle.Row - 1
   ' Stellenkennzeichen in Tabelle Stellen nicht gefunden
   Else
      Range("C14").ClearContents
      MsgBox "Stellenkennzeichen " & Range("D27") & " nicht gefunden"
   End If
   ActiveSheet.Protect
   Application.EnableEvents = True

Die Formeln in Zeile 27 ab Spalte Y musst du so schreiben, dass sie leer dargestellt werden, falls im Formular nichts eingetragen ist - also nach diesem Prinzip: =WENN(O16="";"";O16), damit beim Übertragen der Daten nach Bewerber kein Unsinn in die betreffende Zelle geschrieben wird, allerhöchstens 0, denn das sollte mit dem neuen Code abgefangen werden.

Bis später,
Karin

 

Hallo Karin,

hat diesmal etwas gedauert bis ich wieder aktiv werden konnte. Bin auch diese Woche nur wegen eines Trauerfalls 3 Tage im Büro.
Nun zur Tabelle. Habe einige Korrekturen eingebaut und mich auch daran versucht die noch auftretenen Ereignisse zu finden.
Folgende Dinge sind noch nicht 100%, im Daten Bearbeiten Modul: Rückschreiben des Formularfeld L17 abgelegt in AA24, Die Ceckboxen 7-12 werden nicht zurückgeschrieben. Brauchen wir das Feld A24 (LFN) aus dem Anlegebereich nicht zum zurückschreiben? Das wird nicht zurückgeholt. Du kannst Dir das ja mal anschauen.

Dann ist mir aufgefallen, dass wenn die Zeilen ber Liste [Bewerber] endfernt werden, die Auswahlbox nicht mehr funktioniert. Ich konnte im Code nicht lokalisieren wo der Bereich der Dropbox definiert ist.

Gruß
Bernd

 

Hi Bernd,

1. beim Übernehmen wird Zeile 24 komplett kopiert - was also deine Formeln dort ermitteln wird auch so in Tabelle Bewerber als Wert übertragen

2. die CheckBoxen werden zurückgeschrieben - und zwar in der For-Schleife, mit dieser Codezeile (ich hatte extra zum besseren Verständnis einen Kommentar darüber geschrieben)

Code:

      ' erledigt
      Cells(lngZeile, 13) = Cells(27, intSpalte + 1)

Und das funktioniert auch, wenn man die Bewerber 65-73 auswählt, denn nur bei ihnen gibt es in erledigt Ort_1 den Eintrag WAHR, die Einträge aller anderen erledigt-Felder aller Bewerber stehen auf FALSCH

3. das DropDown-Listenfeld wird nicht per VBA gesteuert sondern per definiertem Namen. Wenn kein Eintrag in Spalte A von Bewerber vorhanden ist, können logischerweise auch keine Daten angezeigt werden.

Bis später,
Karin

 

Hi Karin,

Vielen Dank für die Infos.Das zurücklesen der gehakten Felder funktionitert auch wenn ich diese in der Bewerbertabelle auf True setzte.
Leider haut der Funktionsmodus Daten bearbeiten noch nicht richtig hin. Ich habe jetzt versucht die Datensätze zu editieren und dann zurück zu schreiben. Das scheitert. Im Editiermodus aus der Tabelle in das Formular lesen klappt einwandfrei. In Zeile 24 von Formular [Erfassung] war der Eintrag LFN [A24] falsch. Das habe ich korrigiert so das dort im Editiermodus auch der korrekte Datensatz LFN steht. Der Datensatz müsste wieder komplett mit dem selben Modul aus der Erfassung zurückgeschreiben werden? Dann wäre das Teil fertig zum finalen Erproben mit echten Daten.

Ich denke wir liegen jetzt in den letzten Zügen. Hat sich doch als ganz schön aufwändig herausgestellt, und VBA ist stellenweise nicht so gut zu verstehen. An dieser Stelle nochmals vielen Dank für deine Geduld mit mir :D. Ich hexte das Tool hier immer zwischendurch neben meiner eigendlichen Arbeit hin.

LG
Bernd

 

Hi Bernd,

ändere diesen Teil im Modul Uebernehmen:

Code:

   Else
      With Worksheets("Bewerber")
         Set rngZelle = .Columns(1).Find(Range("A27"), lookat:=xlWhole)
         Range("A24:AD24").Copy
         .Cells(rngZelle.Row, 1).PasteSpecial Paste:=xlValues
      End With
      Application.CutCopyMode = False
   End If

Bis später,
Karin