Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

VBA Verweise erstellen

Frage Microsoft Microsoft Office

Mitglied: xaumichi

xaumichi (Level 1) - Jetzt verbinden

05.08.2010 um 17:44 Uhr, 5052 Aufrufe, 52 Kommentare

Hallo!

Ich habe folgendes (riesiges) Problem:

Der Aufbau meines Arbeitsblattes sieht wie folgt aus:

Ich habe ein Tabellenblatt ("Mitarbeiter"), in dem in Spalte A alle Mitarbeiter (alphabetisch) stehen.
Dazu kommt, dass jeder Mitarbeiter ein eigenes Tabellenblatt hat, welches den selben Namen trägt, der seinem Namen entspricht, sprich dem Wert in Spalte A vom Tabellenblatt "Mitarbeiter". (diese werden automatisch erstellt)

Wenn nun ein neuer Mitarbeiter erstellt wird, werden im TB "Mitarbeiter" in die Spalte D und E werden Daten eingetragen.

Nun möchte ich einen VERWEIS herstellen, dass die Daten vom TB "Mitarbeiter"/Spalte D in das neue Tabellenblatt Zelle L12 eingetragen werden (und wenn später im TB "Mitarbeiter" Veränderungen sind, sollen auch diese dann übernommen werden)
Das selbe soll auch mit TB "Mitarbeiter"/Spalte E in neue Tabellenblatt Zelle I12 passieren

zusätzlich sollen vom neuen Tabellenblatt Daten aus der Zelle F10 ins TB "Mitarbeiter"/Spalte G geschrieben werden (und wieder, wenn sich im neuen TB was ändert soll das im TB "Mitarbeiter" auch geändert werden)
das selbe soll soll auch mit der Zelle F9 ins ins TB "Mitarbeiter"/Spalte H passieren

Kann mir jemand bei dem Code helfen? ich komme gar nicht zurecht. =(

Lg Mike
52 Antworten
Mitglied: mathe172
05.08.2010 um 18:45 Uhr
Hallo xaumichi!

Es wäre vielleicht hilfreich, wenn du den Code für das Erstellen der TB posten würdest, dann könnte man diesen sofort abändern.

Mathe172
Bitte warten ..
Mitglied: xaumichi
05.08.2010 um 19:15 Uhr
kein Problem:

01.
 
02.
Private Sub CommandButton1_Click() 
03.
 
04.
Dim lngZeile As Long 
05.
Dim Jetztblatt As String 
06.
 
07.
If Userform2.TextBox1 = "" Or Userform2.TextBox2 = "" Or Userform2.TextBox3 = "" Or Userform2.TextBox4 = "" Or Userform2.TextBox5 = "" Or Userform2.TextBox6 = "" Then 
08.
Frame1.Caption = "Fehlende Felder" 
09.
Frame1.ForeColor = RGB(255, 0, 0) 
10.
 
11.
    If Userform2.TextBox1 = "" Then 
12.
        Userform2.Label2.ForeColor = RGB(255, 0, 0) 
13.
    End If 
14.
 
15.
    If Userform2.TextBox2 = "" Then 
16.
        Userform2.Label1.ForeColor = RGB(255, 0, 0) 
17.
    End If 
18.
 
19.
    If Userform2.TextBox3 = "" Then 
20.
        Userform2.Label4.ForeColor = RGB(255, 0, 0) 
21.
    End If 
22.
     
23.
    If Userform2.TextBox4 = "" Then 
24.
        Userform2.Label5.ForeColor = RGB(255, 0, 0) 
25.
    End If 
26.
 
27.
    If Userform2.TextBox5 = "" Then 
28.
        Userform2.Label3.ForeColor = RGB(255, 0, 0) 
29.
    End If 
30.
 
31.
    If Userform2.TextBox6 = "" Then 
32.
        Userform2.Label6.ForeColor = RGB(255, 0, 0) 
33.
    End If 
34.
 
35.
Else 
36.
 
37.
Sheets("Client").Visible = True 
38.
Sheets("Client").Select 
39.
Sheets("Client").Copy After:=Sheets(Sheets.Count) 
40.
ActiveSheet.name = Userform2.TextBox2 & " " & Userform2.TextBox1 
41.
ActiveSheet.Protect "test" 
42.
ActiveSheet.Range("C4") = ActiveSheet.name 'Name in Blatt einfügen 
43.
Frame1.Caption = "Anmeldung" 
44.
Frame1.ForeColor = RGB(0, 0, 0) 
45.
Sheets("Client").Visible = False 
46.
Userform2.Label1.ForeColor = RGB(0, 0, 0) 
47.
Userform2.Label2.ForeColor = RGB(0, 0, 0) 
48.
Userform2.Label3.ForeColor = RGB(0, 0, 0) 
49.
Userform2.Label4.ForeColor = RGB(0, 0, 0) 
50.
Userform2.Label5.ForeColor = RGB(0, 0, 0) 
51.
Userform2.Label6.ForeColor = RGB(0, 0, 0) 
52.
Userform2.Hide 
53.
 
54.
    
55.
   ' Schreibt den Inhalt der Userform in die Excel Tabelle History 
56.
   With Worksheets("Mitarbeiter") 
57.
      lngZeile = IIf(Len(.Cells(.Rows.Count, 1)), .Rows.Count, .Cells(.Rows.Count, 1).End(xlUp).Row) + 1 
58.
      If lngZeile > .Rows.Count Then 
59.
         MsgBox "VOLL!" 
60.
         Exit Sub 
61.
      End If 
62.
       
63.
     
64.
      Worksheets("Mitarbeiter").Cells(lngZeile, 1).Value = Userform2.TextBox2 & " " & Userform2.TextBox1 
65.
      Worksheets("Mitarbeiter").Cells(lngZeile, 2).Value = Userform2.TextBox3 
66.
      Worksheets("Mitarbeiter").Cells(lngZeile, 3).Value = Userform2.TextBox4 
67.
      Worksheets("Mitarbeiter").Cells(lngZeile, 4).Value = Userform2.TextBox5 
68.
      Worksheets("Mitarbeiter").Cells(lngZeile, 5).Value = Userform2.TextBox6 
69.
      Worksheets("Mitarbeiter").Cells(lngZeile, 6).Value = Userform2.TextBox6 
70.
     ' Worksheets("Mitarbeiter").Cells(lngZeile, 7).Formula = "=Worksheet(ActiveSheet.name)!F10"        --> funktioniert nicht  
71.
      'Worksheets("Mitarbeiter").Cells(lngZeile, 8).Formula = "=Worksheet(ActiveSheet.name)!F9"          --> funktioniert nicht 
72.
       
73.
      With ActiveWorkbook.Worksheets("Mitarbeiter").Sort 
74.
        .SetRange Range("A2:K21") 
75.
        .Header = xlNo 
76.
        .MatchCase = False 
77.
        .Orientation = xlTopToBottom 
78.
        .SortMethod = xlPinYin 
79.
        .Apply 
80.
    End With 
81.
   
82.
        Worksheets("Mitarbeiter").Select 
83.
        Range("A2").Select 
84.
    End With 
85.
             
86.
End If 
87.
End Sub 
88.
 
Bitte warten ..
Mitglied: mathe172
05.08.2010 um 20:32 Uhr
Hallo xaumichi!

Versuchs mal damit:
01.
 Worksheets("Mitarbeiter").Cells(lngZeile, 7).Formula = "=" & Worksheet(ActiveSheet.name) & "!F10"     --> Variable oder Wert ausserhalb der "" und die drei Blöcke ("="; Worksh....name); "!F10") mit " & " verbinden (ohne die "")  
Mathe172
Bitte warten ..
Mitglied: xaumichi
05.08.2010 um 20:45 Uhr
Danke...funktioniert aber leider nicht:

Fehler beim kompilieren:

Sub- oder Function nicht definiert


Lg Mike
Bitte warten ..
Mitglied: mathe172
05.08.2010 um 20:54 Uhr
Hallo xaumichi!

Sorry...
Vom Worksheets("Activesheet.name") brauchst du nur Activesheet.name, den Rest kannst du löschen.
Den Rest brauchst du nur wenn du Eigenschaften der Tabelle oder derer Inhalte definieren willst. Der Tabellenname steht wie der Name sagt in Activesheet.name. (Damit sagst du dem Programm: Gib mir den Namen der Aktuellen Tabelle; Das andere heisst in etwaIch will etwas in der Tabelle mit dem Namen der aktuellen Tabelle ändern)

Mathe172

P.S:Hoffe die erklärung ist verständlich
Bitte warten ..
Mitglied: xaumichi
05.08.2010 um 21:30 Uhr
Jup, verstanden! =)

So, nun steht zwar die richtige Formel in der Tabelle. allerdings bekomme ich als Ausgabe nur #Name?

Habe herausgefunden, dass das darum kommt, da ein Leerzeichen im Tabellennamen ist.
Wie kann ich dies noch hineinbringen?

LG

EDIT:

Habs geschafft, hab noch ein " ' " vor und nach dem ActiveSheet drangemacht! =)
Bitte warten ..
Mitglied: xaumichi
05.08.2010 um 21:49 Uhr
Noch was, wie schaff ich es, dass ich nicht eine Verweis mache von Personen-TB --> "Mitarbeiter" sonder umgekehrt??


LG
Bitte warten ..
Mitglied: mathe172
05.08.2010 um 22:10 Uhr
Hallo xaumichi!

Versuchs mal damit:
01.
 Worksheet("ActiveSheet.name").[L12].Formula = "=" & 'Mitarbeiter' & "!D" & 'lngZeile' 
Mathe172
Bitte warten ..
Mitglied: xaumichi
05.08.2010 um 22:16 Uhr
cool, muss ich mal ausprobieren! =)

EDIT: Hm....funktioniert leider nicht.
Das mit dem Tabellenblatt "Mitarbeiter" passen nicht und du hast wieder eine "Worksheet" vorm "Activesheet.name" ;)
wenn man im Debug-Modus mit der Maus über "Mitarbeiter" fährt, steht da "Mitarbeiter=leer". Hilft das vl?


Könnte mir noch wer helfen

=WENN(A1>A2;"-"&TEXT(A1-A2;"[hh]:mm");TEXT(A2-A1;"[hh]:mm")) in

01.
 Worksheets("Mitarbeiter").Cells(lngZeile, 7).Formula = "=" & "'" & ActiveSheet.name & "'" & "!F10"
( "=" & "'" & ActiveSheet.name & "'" & "!F10")
einzugeben?? :S
Bitte warten ..
Mitglied: mathe172
06.08.2010 um 11:46 Uhr
Hallo xaumichi!

Sorry, ich habs wohl ein bisschen zu schnell gemacht. Meine Fehler:
  • Es muss Worksheets und nicht Worksheet heissen (Diesmal will ich ja eine Eigenschaft eines "Dings" in der aktuellen Tabelle ändern: Die Formel aus L12)
  • Eine Variable darf nicht in "" stehen:-->Sheets(Activesheet.name)
  • Mitarbeiter ist keine Variable(darum auch "Mitarbeiter=leer"), sondern ein fixer Name einer Tabelle:-->nicht zwischen & schreiben
  • Das mit den ' brauchts bei mir nicht (probiers wenn nicht tut trotzdem mit)

Das sollte jetzt funktionieren(wenn nicht bin ich wahrscheinlich zu blöd):
01.
 Worksheets(ActiveSheet.name).[L12].Formula = "=Mitarbeiter!D" & lngZeile
Das andere schau ich noch an

Mathe172
Bitte warten ..
Mitglied: xaumichi
06.08.2010 um 11:55 Uhr
:D toll funktioniert! Danke!!!
Bitte warten ..
Mitglied: mathe172
06.08.2010 um 12:34 Uhr
Hallo xaumichi!

Beim zweiten hätte ich alles, nur bringt er einen Fehler wenn ich ="=Formel" schreibe. Aus irgendeinem Grund funktioniert aber ="Formel" oder sogar =" =Formel" (also mit Leerzeichen) schreibe. Aber in beiden funktionierenden Fällen funktioniert ja das Endprodukt nicht.

Die Vorläufige Lösung wäre:
01.
Worksheets("Mitarbeiter").Cells(lngZeile, 7).Formula = "=WENN(" & ActiveSheet.name & "!A1>" & ActiveSheet.name & "!A2;"-"&TEXT(" & ActiveSheet.name & "!A1-" & ActiveSheet.name & "!A2;""[hh]:mm"");TEXT(" & ActiveSheet.name & "!A2-" & ActiveSheet.name & "!A1;""[hh]:mm""))"
Hoffe ich habe keinen Tippfehler gemacht.

Mathe172
Bitte warten ..
Mitglied: xaumichi
06.08.2010 um 13:06 Uhr
Hallo!

Habs probiert. Leider hats es nicht geklappt!
Hab mich mit der Formel ansicht noch mal gespielt, und konnte sie ein bisschen vereinfachen:

=WENN(Q2<0;"-"&TEXT(Q2;"[hh]:mm");TEXT(Q2;"[hh]:mm"))
Bitte warten ..
Mitglied: mathe172
06.08.2010 um 13:12 Uhr
Hallo!

Auf welche Tabelle kommt die Formel, wo ist Q2?
Ist es wie oben die Formel auf Mitarbeiter, Q2 auf der ActiveSheet?

Mathe172
Bitte warten ..
Mitglied: xaumichi
06.08.2010 um 13:18 Uhr
Jup, es ist alles gleich, nur statt dem A1 und A2 gibts jetzt einen Vergleich mit Q2<0!
Bitte warten ..
Mitglied: mathe172
06.08.2010 um 14:11 Uhr
Also das Problem hab ich gefunden, aber keine Lösung. Vielleicht weiss sonst jemand wie man das verändern muss:
01.
Worksheets(Activesheet.name).[A1].Formula= "=;"
Bei mir kommt immer:
Laufzeitfehler '1004':

Anwendungs- oder Objektdefinierten Fehler

Mathe172
Bitte warten ..
Mitglied: xaumichi
06.08.2010 um 17:42 Uhr
Hm...schade, aber trotzdem danke für den Versuch! =)
Bitte warten ..
Mitglied: xaumichi
08.08.2010 um 00:54 Uhr
Hallo noch mal!

Hab jetzt die richtige Syntax!

01.
Dim ShName As String 
02.
 
03.
        ShName = ActiveSheet.name 
04.
 
05.
        '=IF(Tabelle2!Q2<0,"-"&TEXT(Tabelle2!Q2,"[hh]:mm"),TEXT(Tabelle2!Q2,"[hh]:mm")) 
06.
        Worksheets("Mitarbeiter").Cells(lngZeile, 7).Formula = _ 
07.
        "=IF(" & "'" & ShName & "'" & "!Q2/24<0,""-""&TEXT(" & "'" & ShName & "'" & "!Q2/24*-1,""[hh]:mm""),TEXT(" & "'" & ShName & "'" & "!Q2/24,""[hh]:mm""))"
hab ich von http://www.office-loesung.de/ftopic402717_0_0_asc.php

LG Mike
Bitte warten ..
Mitglied: 76109
09.08.2010 um 13:22 Uhr
Hallo xaumichi!

Das Problem in Codezeile 70 und 71 ist, dass Du in den Tabellennamen ein Lehrzeichen hast. Indem Fall musst Du den Namen in einfache Hochkommata setzen (siehe Code)

Bei der Gelegenheit habe ich Deinen Anfangs-Code etwas reduziert, wobei Du allerdings in Deiner UserForm im Eigenschaftsfenster, die Labelnamen der TextBoxen entsprechend anpassen musst (TextBox1 und Label1, TextBox2 und Label2....).

01.
Option Explicit 
02.
 
03.
Const FarbeRot = &HFF 
04.
Const FarbeSchwarz = 0 
05.
 
06.
Private Sub CommandButton1_Click() 
07.
    Dim lngZeile As Long, Jetztblatt As String, Fehler As Boolean, i As Integer 
08.
     
09.
    For i = 1 To 6 
10.
        UserForm2("Label" & (i)).ForeColor = FarbeSchwarz  'Falls nicht alle berichtigt wurden 
11.
        If UserForm2("TextBox" & i) = "" Then 
12.
            Fehler = True:  UserForm2("Label" & (i)).ForeColor = FarbeRot 
13.
        End If 
14.
    Next 
15.
     
16.
    If Fehler Then 
17.
        Frame1.Caption = "Fehlende Felder" 
18.
        Frame1.ForeColor = FarbeRot 
19.
    Else 
20.
        Application.ScreenUpdating = False 
21.
         
22.
        With Sheets("Client") 
23.
            .Visible = True 
24.
            .Copy After:=Sheets(Sheets.Count) 
25.
            .Visible = False 
26.
        End With 
27.
 
28.
        With ActiveSheet 
29.
            .Name = UserForm2.TextBox2 & " " & UserForm2.TextBox1 
30.
            .Range("C4") = .Name 
31.
            .Protect Password:="test" 
32.
        End With 
33.
         
34.
        'Farbe auf schwarz zurücksetzen ist unnötig, weil Userform geschlossen wird (Unload anstatt Hide) 
35.
        'Frame1.Caption = "Anmeldung" 
36.
        'Frame1.ForeColor = FarbeSchwarz 
37.
        'For i = 1 To 6:  UserForm2("Label" & i).ForeColor = FarbeSchwarz:  Next 
38.
         
39.
        With Worksheets("Mitarbeiter") 
40.
            lngZeile = .Cells(.Rows.Count, 1).End(xlUp).Row 
41.
             
42.
            If lngZeile = 1 Then 
43.
                If Not IsEmpty(.Cells(1, 1)) Then lngZeile = lngZeile + 1 
44.
            ElseIf lngZeile < .Rows.Count And IsEmpty(.Cells(.Rows.Count, 1)) Then 
45.
                lngZeile = lngZeile + 1 
46.
            Else 
47.
                MsgBox "VOLL!":  Exit Sub 
48.
            End If 
49.
             
50.
            With .Cells.Rows(lngZeile) 
51.
                .Columns(1) = TextBox2 & " " & TextBox1 
52.
                .Columns(2) = TextBox3 
53.
                .Columns(3) = TextBox4 
54.
                .Columns(4) = TextBox5 
55.
                .Columns(5) = TextBox6 
56.
                .Columns(6) = TextBox6 
57.
                .Columns(7).Formula = "='" & ActiveSheet.Name & "'!F10" 
58.
                .Columns(8).Formula = "='" & ActiveSheet.Name & "'!F9" 
59.
            End With 
60.
             
61.
            Range(.Range("A2"), .Cells(lngZeile, "K")).Sort Key1:=.Range("A2"), Header:=xlNo 
62.
          
63.
           .Activate:  Range("A2").Select 
64.
             
65.
            Application.ScreenUpdating = True 
66.
            
67.
            Unload Me 
68.
        End With 
69.
    End If 
70.
End Sub
Gruß Dieter

[edit]
Codezeile 61 von
01.
           .Range("A2:K21").Sort Key1:=.Range("A2"), Header:=xlNo
nach
01.
            Range(.Range("A2"), .Cells(lngZeile, "K")).Sort Key1:=.Range("A2"), Header:=xlNo
geändert.

Für den Fall, dass es mehr als als 20 Mitarbeiter werden. Jetzt bis letzte Zeile mit Inhalt.
[/edit]
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 14:23 Uhr
Hallo!

So, jetzt nach einer kurzen Testphase ist mir ein seltsamer Fehler aufgefallen:

Ansich funktioniert der Code ganz gut, NUR:

Kurze Beschreibung:
Diese "activeSheet.Name" bezieht sich ja auf einen neu erstellten Mitarbeiter, der in einer Tabelle hinzugefügt wird. Diese Liste wird nach dem Erstellen alphabtisch geordnet. Wird nun ein Mitarbeiter erstellt, der alphabetisch VOR allen aneren steht, da er alphabetisch er erste ist, so wird bei der Wert nicht von diesem Mitarbeiter verwendet, sondern die, des Vorgängener.

Warum ist dem so??

LG Mike
Bitte warten ..
Mitglied: 76109
09.08.2010 um 14:47 Uhr
Hallo Mike!

Zitat von xaumichi:
So, jetzt nach einer kurzen Testphase ist mir ein seltsamer Fehler aufgefallen:

Ansich funktioniert der Code ganz gut, NUR:

Kurze Beschreibung:
Diese "activeSheet.Name" bezieht sich ja auf einen neu erstellten Mitarbeiter, der in einer Tabelle hinzugefügt
wird. Diese Liste wird nach dem Erstellen alphabtisch geordnet. Wird nun ein Mitarbeiter erstellt, der alphabetisch VOR allen
aneren steht, da er alphabetisch er erste ist, so wird bei der Wert nicht von diesem Mitarbeiter verwendet, sondern die, des
Vorgängener.
Verstehe ich nicht ganz? Welcher Wert?

Und bei dieser Gelegenheit, ab welcher Zeile beginnt die Mitarbeiter-Liste, ab Zeile 2 (Zeile1 Überschrift?)?

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 15:49 Uhr
Naja, ich hab ja ein paar Eintragungen oberhalb eine funktionierende Syntax hereinkopiert, mit der man auf dem "ActiveSheet.Name" ein Verweis eingefügt wird.
Hier kommt der Ausdruck "lngZeile" vor (Def. siehe ganz oben, Zeile 56!). Hier wird eben ein Wert eingetragen, der dann auf dem "ActiveSheet.Name" eingetragen werden soll.
Jetzt passiert es aber, dass, wenn ein Name bereits in der Liste (im Tabellenblatt "Mitaribeiter") steht und nun ein weiterer Name hinzugefügt wird, der alphabetisch vor dem eingetragenen stehen wird, dass dann dessen Wert eingetragen wird und nicht der, des neu erstellen Mitarbeiters!

Jup, beginnen tuts bei Zeile 2, 1 sind Überschriften!

LG
Bitte warten ..
Mitglied: 76109
09.08.2010 um 16:56 Uhr
Hallo Mike!

Achso

Aber eigentlich sollte das nicht passieren, weil Du ja bis Spalte K sortierst und die Verweise sich mit Spalte 7 und 8 innerhalb dieses Bereichs befindet. Da habe ich leider keine Erklärung dafür

Gruß Dieter
Bitte warten ..
Mitglied: 76109
09.08.2010 um 19:46 Uhr
Hallo nochmal!

Also, ich habe mal mit meinem Code getestet und konnte keinen Sortier-Fehler erkennen

Im Code oben, habe ich den Sortiervorgang auf Range A2:K & "Letzt Zeile mit Inhalt" geändert.

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 20:10 Uhr
Hm...okey! dann versuch ich den deinen mal! =)

wieweit ersetz dein Code meinen Code?

LG
Bitte warten ..
Mitglied: 76109
09.08.2010 um 20:33 Uhr
Hallo Mike!

Von der Funktion her gesehen gleich, aber etwas zusammengefasst.

Aber vorher erst die ersten Zeilen nochmal durchlesen. UserForm-Labels namentlich (1,2,3,4,5,6) den TextBoxen anpassen

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 20:50 Uhr
Ich pack es nicht...!

Habs jetzt mal probiert....und jetzt passiert folgendes.

Erster Mitarbeiter funktioniert wieder einwandfrei, füge ich jetzt einen neuen Mitarbeiter ein, der VOR dem bestehenen kommt, so bekommt der bestehene Mitarbeiter den Wert des neuen und der neu den Wert des alten Mitarbeiters.

PS: ich habe die Zeile 57 noch geändert, damit die richtige Formel drinnen steht:

01.
 .Columns(7).Formula = "=IF(" & "'" & ActiveSheet.name & "'" & "!Q2/24<0,""-""&TEXT(" & "'" & ActiveSheet.name & "'" & "!Q2/24*-1,""[hh]:mm""),TEXT(" & "'" & ActiveSheet.name & "'" & "!Q2/24,""[hh]:mm""))" 
Ich verstehs nicht, was ich anderes mache....!

Lg

Könnte es damit zu tun haben, dass die Spalten E:H geschützt sind?

[/Edit]
Bitte warten ..
Mitglied: 76109
09.08.2010 um 22:28 Uhr
Hallo Mike!

Wie , die Zellen sind geschützt? Meinst Du damit, dass in der Tabelle "Mitarbeiter" während der Neu-Erstellung der Blattschutz aktiv ist. Wenn die Spalte(5) E bis Spalte(8) H gesperrte Zellen sind, dann können diese nicht beschrieben werden. Bekommst Du denn keine Fehlermeldung? Normalerweise bekommt man eine Fehlermeldung, wenn versucht wird, Werte in geschützte Zellen zu schreiben.

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 22:38 Uhr
Diese sind geschützt, ja.
Das Passwort für das Tabellenblatt ist unter "DiesesArbeitsblatt" definiert. Darum funktioniert auch die Eintragung.
Hab es auch schon probiert, den Schutz zu entfernen. Hat aber vom Ergebniss nichts geändert:

Der erste Eintrag funktioniert und jeder Eintrag, der alphabetisch vorher kommt (nach der Sortierung) bekommt in .Columns(7) den Wert des Vorängers und umgekehrt!
=(

LG
Bitte warten ..
Mitglied: 76109
09.08.2010 um 22:56 Uhr
Hallo nochmal!

Zitat von xaumichi:
Das Passwort für das Tabellenblatt ist unter "DiesesArbeitsblatt" definiert.
Nun, dass verstehe ich jetzt leider überhaupt nicht ?????????

Also, die Formel ist so in Ordnung, wenn man mal davon absieht, dass unnötige & drinnen sind. Könnte auch so lauten:
01.
.Columns(7).Formula = "=IF('" & ActiveSheet.Name & "'!Q2/24<0,""-""&TEXT('" & ActiveSheet.Name & "'!Q2/24*-1,""[hh]:mm""),TEXT('" & ActiveSheet.Name & "'!Q2/24,""[hh]:mm""))"
Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 22:59 Uhr
Naja, laut eines Formus, bewirkt diese Zeile:

01.
ThisWorkbook.Worksheets("Mitarbeiter").Protect Password:="test", UserInterFaceOnly:=True
das jetzt das Tabellenblatt "Mitarbeiter" durch Makros bearbeitet werden kann. Was ja auch soweit funktioniert.....
Bitte warten ..
Mitglied: 76109
09.08.2010 um 23:04 Uhr
Hallo Mike!

Aja, dass wusste ich jetzt nicht

ich werde mal mit Formeln testen Die habe ich ja bisher noch nicht in den Code integriert.

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 23:08 Uhr
Hui, das freut mich aber, dass "ich" dir auch was neues zeigen hab können!

Okey, danke!

LG Mike
Bitte warten ..
Mitglied: 76109
09.08.2010 um 23:20 Uhr
Hallo nochmal!

Zitat von xaumichi:
Hui, das freut mich aber, dass "ich" dir auch was neues zeigen hab können!
Ja, man kann halt nicht alles wissen

Also, ich habe jetzt meinen Code nochmal mit Formel getestet. Leider mit dem Ergebnis, dass alles stimmt

Gruß Dieter
Bitte warten ..
Mitglied: 76109
09.08.2010 um 23:25 Uhr
Und nochmal Hallo!

Mach mal vor dem Sort ein Kommentarzeichen und schau, ob die Einträge stimmen. Habe da gerade einen Verdacht

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
09.08.2010 um 23:33 Uhr
Naja, jetzt würde alles stimmen.

Wär eben so, als ob bei der Eintrag nachgestellt werden würde!

Hm...werden die die Zeilen vl nicht mitsortiert??

Lg mike
Bitte warten ..
Mitglied: 76109
09.08.2010 um 23:51 Uhr
Hallo Mike!

Das Problem liegt beim Sortieren. Und zwar werden Sortier-Einstellungen gespeichert und beim nächsten Sortier-Befehl eben diese Einstellungen übernommen, D.h. alle nötigen Einstellungen müssen explizit angegeben werden. Habe gerade mal mit mehreren Parameter herumexperementiert und da passieren ganz kommische Sachen. Am besten Du machst mal ein paar Eingaben ohne die Codezeile Sort und sortierst manuell über das <Menu><Daten> mit Makroaufzeichnung. Wenn falsch, dann kannst Du ja über die Undo-Funktion die Sortierung rückgängig machen....

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 00:00 Uhr
Okey, werde ich morgen dann probieren!

Danke für deine Bemühungen! =)

Schönen Abenden noch!

LG Mike
Bitte warten ..
Mitglied: 76109
10.08.2010 um 00:04 Uhr
Versuchs mal damit:
01.
            Range(.Range("A2"), .Cells(lngZeile, "K")).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _ 
02.
                                 Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 05:55 Uhr
Hm...hat leider auch nicht funktioniert.
Er nimmt den Wert des Letzten in meiner Liste! =(

LG Mike

[EDIT:]
Hab jetzt versucht, ein Makro mit dieser Liste zu erstellen und dieses Makro dann einzufügen, hat jedoch auch kein andere Ergebnis geliefert....!
Bitte warten ..
Mitglied: 76109
10.08.2010 um 08:04 Uhr
Guten Morgen!

Zitat von xaumichi:
Schönen Abenden noch!
Da ist leider nicht viel von übrig geblieben

Hast Du die Nacht durchgemacht?

Mhm, es liegt ja eindeutig am Sort-Befehl und das der trotz umfangreicher Parameter-Einstellungen bei Dir immer noch nicht funktioniert, ist mir ein Rätsel, zumal es bei mir ja einwandfrei geht?

Und wenn Du manuell über's Menü sortierst, funktioniert es auch nicht? Und die Sortierdaten befinden sich alle im Bereich von Spalte A:K?

Gruß Dieter

PS. Hast Du auch mal in einer anderen Excel-Version getestet?
Bitte warten ..
Mitglied: 76109
10.08.2010 um 10:40 Uhr
Hallo Mike!

Versuchs mal mit diesem Code:
01.
Option Explicit 
02.
 
03.
Const FarbeRot = &HFF 
04.
Const FarbeSchwarz = 0 
05.
 
06.
Private Sub CommandButton1_Click() 
07.
    Dim Fehler As Boolean, NeuZeile As Long, i As Integer 
08.
     
09.
    For i = 1 To 6 
10.
        UserForm2("Label" & (i)).ForeColor = FarbeSchwarz  'Falls nicht alle berichtigt wurden 
11.
        If UserForm2("TextBox" & i) = "" Then 
12.
            Fehler = True:  UserForm2("Label" & (i)).ForeColor = FarbeRot 
13.
        End If 
14.
    Next 
15.
     
16.
    If Fehler Then 
17.
        Frame1.Caption = "Fehlende Felder" 
18.
        Frame1.ForeColor = FarbeRot 
19.
    Else 
20.
        Application.ScreenUpdating = False 
21.
         
22.
        With Sheets("Client") 
23.
            .Visible = True 
24.
            .Copy After:=Sheets(Sheets.Count) 
25.
            .Visible = False 
26.
        End With 
27.
 
28.
        With ActiveSheet 
29.
            .Name = UserForm2.TextBox2 & " " & UserForm2.TextBox1 
30.
            .Range("C4") = .Name 
31.
            .Protect Password:="test" 
32.
        End With 
33.
         
34.
        With Worksheets("Mitarbeiter") 
35.
            NeuZeile = GetLine(ActiveSheet.Name) 
36.
             
37.
            If NeuZeile = 0 Then MsgBox "VOLL!":  Exit Sub 
38.
             
39.
            With .Cells.Rows(NeuZeile) 
40.
                .Columns(1) = TextBox2 & " " & TextBox1 
41.
                .Columns(2) = TextBox3 
42.
                .Columns(3) = TextBox4 
43.
                .Columns(4) = TextBox5 
44.
                .Columns(5) = TextBox6 
45.
                .Columns(6) = TextBox6 
46.
                .Columns(7).Formula = "=IF('" & ActiveSheet.Name & "'!Q2/24<0,""-""&TEXT('" & ActiveSheet.Name & _ 
47.
                                      "'!Q2/24*-1,""[hh]:mm""),TEXT('" & ActiveSheet.Name & "'!Q2/24,""[hh]:mm""))" 
48.
                .Columns(8).Formula = "=" & "'" & ActiveSheet.Name & "'!F9" 
49.
            End With 
50.
             
51.
            Range("L12").Formula = "=VLOOKUP(C4,Mitarbeiter!A:D,4,FALSE)" 
52.
 
53.
           .Activate:  Range("A2").Select 
54.
             
55.
            Application.ScreenUpdating = True 
56.
            
57.
            Unload Me 
58.
        End With 
59.
    End If 
60.
End Sub 
61.
 
62.
Private Function GetLine(ByRef Mitarbeiter) As Long 
63.
    Dim i As Long, EndLine As Long 
64.
 
65.
    With Worksheets("Mitarbeiter") 
66.
        EndLine = .Cells(.Rows.Count, "A").End(xlUp).Row 
67.
             
68.
        GetLine = EndLine + 1 
69.
         
70.
        If EndLine = 1 Then 
71.
            If Not IsEmpty(.Cells(1, "A")) Then EndLine = 2:  GetLine = 2 
72.
        ElseIf Not IsEmpty(.Cells(.Rows.Count, "A")) Then 
73.
            GetLine = 0:  Exit Function 
74.
        End If 
75.
     
76.
        For i = 2 To EndLine 
77.
            If Not IsEmpty(.Cells(i, "A")) Then 
78.
                If StrComp(Mitarbeiter, .Cells(i, "A"), vbTextCompare) = True Then 
79.
                    Range(.Cells(i, "A"), .Cells(EndLine + 1, "K")).Cut .Cells(i + 1, "A") 
80.
                    GetLine = i:  Exit Function 
81.
                End If 
82.
            End If 
83.
        Next 
84.
    End With 
85.
End Function
Die Funktion GetLine gibt entweder 0 (Fehler voll) oder die Zeilennumer, in der der Mitarbeiter eingefügt wird, zurück. In der Mitarbeiterliste wird die entsprechende Zeile nach alphabetischer Reihenfolge gesucht und dem entsprechend eine Leerzeile für den neuen Mitarbeiter eingefügt.

Jetzt bin ich mal gespannt, ob das bei Dir funktioniert Wenn nicht, dann hast Du irgendwo anders einen Bock drinnen?

Ist das Mitarbeiterblatt das Blatt mit der Worksheet_Change-Routine?

Gruß Dieter

[edit]
Code entsprechend den Bedingungen der nachfolgenden Kommentare geändert:
SVerweisFormel für das neue Mitarbeiterblatt hinzugefügt.
In Funktion GetLine(...) - Verschiebung der Zellinhalte nur von Spalte A-K
[/edit]
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 19:42 Uhr
Hallo!

Nein, habe nicht durchgemacht, sondern hab früh beginnen müssen heute!

So, hab jetzt deinen Code ausprobiert.

Leider wird jetzt der Wert (der vorher immer falsch eingetragen wurde) gar nicht eingetragen.

Hab mir den Code noch mal angesehen und habe festgestellt, dass irgendwie die Zeile:

01.
Worksheet("ActiveSheet.name").[L12].Formula = "=" & 'Mitarbeiter' & "!D" & 'lngZeile' 
fehlt, oder hab ich die wo übersehen?

LG Mike

[Edit]

So, hab jetzt die Zeile noch hinzugefügt! =)
Funktioniert!! :D

NUR:

durch diese Methode wird die Formatierung der erse Zeile (mit den Überstunden) übernommen, daass ist schlecht, da hier alle Zellen gesperrt sind, andere Schriftart,...!
Und es werden dadurch ActiveX-Element verschoben...!
Bitte warten ..
Mitglied: 76109
10.08.2010 um 21:31 Uhr
Hallo Mike!

Kommentar entfernt, steh im Moment irgendwie auf der Leitung

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 21:43 Uhr
Okey!

Ich/wir probiern jetzt schon so lange herum, dass es auf ein paar stunden mehr oder weniger auch nicht mehr drauf ankommt!
Bitte warten ..
Mitglied: 76109
10.08.2010 um 22:24 Uhr
Hallo nochmal!

Zitat von xaumichi:
Leider wird jetzt der Wert (der vorher immer falsch eingetragen wurde) gar nicht eingetragen.
Hab mir den Code noch mal angesehen und habe festgestellt, dass irgendwie die Zeile:
01.
Worksheet("ActiveSheet.name").[L12].Formula = "=" & 'Mitarbeiter' & 
02.
> "!D" & 'lngZeile' 

fehlt, oder hab ich die wo übersehen?
Diese Formel ist mir gänzlich unbekannt und syntaktisch auch falsch.

Wenn ich es aber richtig verstehe, dann soll in den neuen Mitarbeiterblätter eine Formel mit einem Bezug auf das Sheet("Mitarbeiter") eingefügt werden und in diesem Fall wäre es dann auch erklärbar, warum der Sortierbefehl nicht so funktioniert, wie Du Dir das vorstellst. Die Bezüge in den Mitarbeitblättern werden beim sortieren logischerweise willkürlich verschoben

Indem Fall darf entweder nicht sortiert werden, oder in allen Mitarbeiterblättern, muss der Bezug neu angepasst werden

Bei meiner Einfügmethode, werden die Bezüge automatisch angepasst. Das sollte aber eigentlich nur einem Test dienen, ob da irgendetwas anderes schief läuft. Und da mir die Formel bisher nicht bekannt war, konnte ich natürlich auch keine Erklärung für das Disaster finden.

Gruß Dieter

PS. Das mit meiner Einfügfunktion die Spalten ab Spalte L nicht verschoben werden, muss ich erst noch austesten.
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 22:38 Uhr
Okey.
Diese Formel wird im Thread weiter oben abgehandelt!
Hm....aber hast du einen Weg gefunden, dass die neuen Mitarbeiter die selbe Formatierung haben, wie die anderen, bzw. dass die ActiveX-Elemente (M3, M6, M8) nicht verschoben oder gezerrt werden?

Lg Mike

PS:Okey, habs erst jetzt gelesen!
Bitte warten ..
Mitglied: 76109
10.08.2010 um 22:52 Uhr
Hallo Mike!

Füge nach Codezeile 30 (.Range("C4") = .Name ) diese Codezeile ein.
01.
            .Range("L12").Formula = "=VLOOKUP(C4,Mitarbeiter!A:D,4,FALSE)"
Das ergibt dann in den Mitarbeiterblätter einen SVerweis auf das Sheet("Mitarbeiter"). SVerweis sucht im Sheet("Mitarbeiter") in Spalte A nach dem Namen, der in den Mitarbeiterblättern in der Zelle C4 (siehe Codezeile) eingetragen wurde und zeigt den Wert aus Spalte D an. Dadurch kann dann wieder der Sortierbefehl verwendet werden

Gruß Dieter
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 23:00 Uhr
Kann es sein, dass das dieser Code für Zeile 31 noch "zu früh" ist?
Den das Problem ist, wenn ich den Code bereit in Zeile 31 stehen haben, soll der Wert vom Tabellenblatt "Mitarbeiter" übertragen werden, wo noch gar kein Eintrag steht. Darum bekomm ich im neu erstellten Tabellenblatt auf L12 einen #NV und eine Fehlermeldung :

Laufzeitfehler:

Unverträgliche Typen


:-S

Lg mike
Bitte warten ..
Mitglied: 76109
10.08.2010 um 23:18 Uhr
Hallo Mike!

Diesen Code, wieder mit Sort-Befehl und SVerweis-Formel:
01.
Option Explicit 
02.
 
03.
Const FarbeRot = &HFF 
04.
Const FarbeSchwarz = 0 
05.
 
06.
Private Sub CommandButton1_Click() 
07.
    Dim Fehler As Boolean, NeueZeile As Long, i As Integer 
08.
     
09.
    For i = 1 To 6 
10.
        UserForm2("Label" & (i)).ForeColor = FarbeSchwarz  'Falls nicht alle berichtigt wurden 
11.
        If UserForm2("TextBox" & i) = "" Then 
12.
            Fehler = True:  UserForm2("Label" & (i)).ForeColor = FarbeRot 
13.
        End If 
14.
    Next 
15.
     
16.
    If Fehler Then 
17.
        Frame1.Caption = "Fehlende Felder" 
18.
        Frame1.ForeColor = FarbeRot 
19.
    Else 
20.
        Application.ScreenUpdating = False 
21.
         
22.
        With Sheets("Client") 
23.
            .Visible = True 
24.
            .Copy After:=Sheets(Sheets.Count) 
25.
            .Visible = False 
26.
        End With 
27.
 
28.
        With ActiveSheet 
29.
            .Name = UserForm2.TextBox2 & " " & UserForm2.TextBox1 
30.
            .Range("C4") = .Name 
31.
            .Protect Password:="test", UserInterFaceOnly:=True 
32.
        End With 
33.
         
34.
        With Worksheets("Mitarbeiter") 
35.
            NeueZeile = .Cells(.Rows.Count, "A").End(xlUp).Row 
36.
             
37.
            If NeueZeile = 1 Then 
38.
                If Not IsEmpty(.Cells(1, "A")) Then NeueZeile = 2 
39.
            ElseIf Not IsEmpty(.Cells(.Rows.Count, "A")) Then 
40.
                MsgBox "VOLL!":  Exit Sub 
41.
            Else 
42.
                NeueZeile = NeueZeile + 1 
43.
            End If 
44.
             
45.
            With .Cells.Rows(NeueZeile) 
46.
                .Columns(1) = TextBox2 & " " & TextBox1 
47.
                .Columns(2) = TextBox3 
48.
                .Columns(3) = TextBox4 
49.
                .Columns(4) = TextBox5 
50.
                .Columns(5) = TextBox6 
51.
                .Columns(6) = TextBox6 
52.
                .Columns(7).Formula = "=IF('" & ActiveSheet.Name & "'!Q2/24<0,""-""&TEXT('" & ActiveSheet.Name & _ 
53.
                                      "'!Q2/24*-1,""[hh]:mm""),TEXT('" & ActiveSheet.Name & "'!Q2/24,""[hh]:mm""))" 
54.
                .Columns(8).Formula = "=" & "'" & ActiveSheet.Name & "'!F9" 
55.
            End With 
56.
             
57.
            Range(.Range("A2"), .Cells(NeueZeile, "K")).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _ 
58.
                                 Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal 
59.
            
60.
            Range("L12").Formula = "=VLOOKUP(C4,Mitarbeiter!A:D,4,FALSE)" 
61.
             
62.
           .Activate:  Range("A2").Select 
63.
             
64.
            Application.ScreenUpdating = True 
65.
            
66.
            Unload Me 
67.
        End With 
68.
    End If 
69.
End Sub
Wenn der jetzt nicht endlich funktioniert, dann springe ich vom Balkon

Gruß Dieter

[edit] Auf Anregung von xaumichi, die SVerweis-Formel nach Zeile 60 verschoben [/edit]
Bitte warten ..
Mitglied: xaumichi
10.08.2010 um 23:29 Uhr
Naja, wär doch schade!

Also, ich habe nun die Zeile 31 (aus deinem gerade eben geposteten Code) in die Zeile 57 mit
01.
Activesheet....
zu Beginn verschoben und ....
ES FUNKTIONIERT!!!

Danke, viel mals!!!

Lg Mike
Bitte warten ..
Mitglied: 76109
10.08.2010 um 23:48 Uhr
Hallo Mike!

Zitat von xaumichi:
Naja, wär doch schade!
Also, ich habe nun die Zeile 31 (aus deinem gerade eben geposteten Code) in die Zeile 57 mit
Naja, ist ja nur ne Kleinigkeit und im Eifer des Gefechts passieren solche Unachtsamkeiten schon mal
Freut mich aber, dass Du dieses Problem von selbst lösen konntest

Und Gottseidank funktioniert es endlich!!!!!!

Gruß Dieter

PS.
Hab's im letzten Code geändert, allerdings nach Sort in Codezeile 60 verschoben. Neues Sheet ist noch das ActiveSheet, insofern reicht nur Range ohne Punkt.
Und den Code mit der Einfüg-Funktion GetLine(..) habe ich auch entsprechend angepasst. Verschiebt jetzt nur noch Spalte A-K und würde somit jetzt auch seinen Zweck erfüllen.
Bitte warten ..
Neuester Wissensbeitrag
Humor (lol)

Linkliste für Adventskalender

(3)

Information von nikoatit zum Thema Humor (lol) ...

Ähnliche Inhalte
VB for Applications
gelöst Mehrere Charts per VBA erstellen lassen, Titel wird nicht angezeigt

Frage von Aximand zum Thema VB for Applications ...

VB for Applications
Aus VBA E-Mail und HTML Dokument erstellen lassen (3)

Frage von Pago159 zum Thema VB for Applications ...

VB for Applications
Mit Excel 2010 (VBA) eine Mail erzeugen und als Anlage einen Termin erstellen

Frage von Armin-K zum Thema VB for Applications ...

Heiß diskutierte Inhalte
Windows Server
DHCP Server switchen (24)

Frage von M.Marz zum Thema Windows Server ...

SAN, NAS, DAS
gelöst HP-Proliant Microserver Betriebssystem (14)

Frage von Yannosch zum Thema SAN, NAS, DAS ...

Grafikkarten & Monitore
Win 10 Grafikkarte Crash von Software? (13)

Frage von Marabunta zum Thema Grafikkarten & Monitore ...

Erkennung und -Abwehr
Spam mit eigener Domain (12)

Frage von NoobOne zum Thema Erkennung und -Abwehr ...