Thema Datum  Von Nutzer Rating
Antwort
Rot Probleme mit Worksheet Variable / Versuch 2
13.01.2023 14:37:09 Marius
NotSolved
13.01.2023 14:49:36 Gast28365
NotSolved
13.01.2023 15:06:54 Marius
NotSolved
13.01.2023 21:18:25 ralf_b
Solved
16.01.2023 13:39:37 Marius
NotSolved
13.01.2023 16:19:27 Gast41486
NotSolved

Ansicht des Beitrags:
Von:
Marius
Datum:
13.01.2023 14:37:09
Views:
54
Rating: Antwort:
  Ja
Thema:
Probleme mit Worksheet Variable / Versuch 2

Hallo Zusammen,

leider habe ich den 1. Post versehentlich gelöscht...

Problem: ich kann nicht mit der Worksheet Variable arbeiten, weil Sie egal was ich machen will eine Fehlermeldung auslöst.

UserForm:


Code:
 

Option Explicit
Dim ws As Worksheet

'CHAT-PARTNER DURCH BUTTON FESTLEGEN
Private Sub btn_heidi_Click()
    Me.TextBox3.Value = "Heidi"
    Call lb_füllen
    Call set_worksheet
End Sub

Private Sub btn_martina_Click()
    Me.TextBox3.Value = "Martina"
    Call lb_füllen
    Call set_worksheet
End Sub

Private Sub btn_andreas_Click()
    Me.TextBox3.Value = "Andreas"
    Call lb_füllen
    Call set_worksheet
End Sub

Private Sub btn_marius_Click()
     Me.TextBox3.Value = "Marius"
     Call lb_füllen
     Call set_worksheet
End Sub

Private Sub btn_ulrike_Click()
    Me.TextBox3.Value = "Ulrike"
    Call lb_füllen
    Call set_worksheet
End Sub

Private Sub btn_angelika_Click()
    Me.TextBox3.Value = "Angelika"
    Call lb_füllen
    Call set_worksheet
End Sub

Private Sub btn_maximilian_Click()
    Me.TextBox3.Value = "Maximilian"
    Call lb_füllen
    Call set_worksheet
End Sub

Private Sub btn_fynn_Click()
    Me.TextBox3.Value = "Fynn"
    Call lb_füllen
    Call set_worksheet
End Sub


'NEUE NACHRICHT ERFASSEN
Private Sub btn_message_Click()
    Call new_message
End Sub

Private Sub user_a_Click()
    With Me.user_b
        .Visible = True
    End With
    With Me.user_btn
        .Visible = True
    End With
End Sub


'AUSWÄHLEN WELCHER USER MAN IST
Private Sub user_btn_Click()
    Me.user_a.Caption = Me.user_b.Value
    Call username
    
    With Me.user_b
        .Visible = False
    End With
    With Me.user_btn
        .Visible = False
    End With
End Sub

Private Sub UserForm_Activate()
'USER-AUSWAHL-BOX FÜLLEN
    With Me.user_b
        .Visible = False
        .AddItem "Heidi Bungert"
        .AddItem "Martina Scherf"
        .AddItem "Andreas Stein"
        .AddItem "Marius May"
        .AddItem "Ulrike Henrich"
        .AddItem "Angelika Müller"
        .AddItem "Maximilian Lehnen"
        .AddItem "Fynn Knippel"
    End With

'USER-AUWAHL BUTTON START-EIGENSCHAFT
    With Me.user_btn
        .Visible = False
    End With

'CHAT-PARTNER BUTTONS START-EIGENSCHAFT
    Call username
End Sub

'CHAT-PARTNER BUTTONS EIGENSCHAFT
Sub username()
    If Me.user_b = "Heidi Bungert" Then
        Me.btn_heidi.Enabled = False
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Martina Scherf" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = False
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Andreas Stein" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = False
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Marius May" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = False
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Ulrike Henrich" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = False
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Angelika Müller" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = False
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Maximilian Lehnen" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = False
        Me.btn_fynn.Enabled = True
    ElseIf Me.user_b = "Fynn Knippel" Then
        Me.btn_heidi.Enabled = True
        Me.btn_martina.Enabled = True
        Me.btn_andreas.Enabled = True
        Me.btn_marius.Enabled = True
        Me.btn_ulrike.Enabled = True
        Me.btn_angelika.Enabled = True
        Me.btn_maximilian.Enabled = True
        Me.btn_fynn.Enabled = False
    Else
        Me.btn_heidi.Enabled = False
        Me.btn_martina.Enabled = False
        Me.btn_andreas.Enabled = False
        Me.btn_marius.Enabled = False
        Me.btn_ulrike.Enabled = False
        Me.btn_angelika.Enabled = False
        Me.btn_maximilian.Enabled = False
        Me.btn_fynn.Enabled = False
    End If
End Sub

'NEW-MESSAGE EVENTS
Sub new_message()
    Dim i As Integer
    Dim ws As Worksheet
'    Call set_worksheet
    
    ws = "marius-ulrike" '<- Fehlermeldung "Objektvariable oder With-Blockvariable nicht definiert."
    
    With ws '<- Fehlermeldung "Objektvariable oder With-Blockvariable nicht definiert."
        i = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
        .Cells(i, 1).Value = Me.TextBox4.Text
        .Cells(i, 2).Value = Me.user_a.Caption & " schrieb am: "
        .Cells(i, 3).Value = Date & " um: "
        .Cells(i, 4).Value = Time & " Uhr: "
        .Cells(i, 5).Value = Me.TextBox2.Text
        Me.TextBox2.Text = ""
    End With
End Sub

'WORKSHEET-AUSWAHL NACH CHAT-PARTNER
Sub set_worksheet()
    Dim ws As Worksheet

' -----  USER HEIDI -----
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("heidi-martina")
    End If
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("heidi-andreas")
    End If
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("heidi-marius")
    End If
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("heidi-ulrike")
    End If
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("heidi-angelika")
    End If
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("heidi-maximilian")
    End If
    If Me.user_a.Caption = "Heidi Bungert" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("heidi-fynn")
    End If

' -----  USER MARTINA -----
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-martina")
    End If
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("martina-andreas")
    End If
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("martina-marius")
    End If
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("martina-ulrike")
    End If
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("martina-angelika")
    End If
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("martina-maximilian")
    End If
    If Me.user_a.Caption = "Martina Scherf" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("martina-fynn")
    End If

' -----  USER ANDREAS -----
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-andreas")
    End If
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("martina-andreas")
    End If
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("andreas-marius")
    End If
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("andreas-marius")
    End If
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("andreas-angelika")
    End If
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("andreas-maximilian")
    End If
    If Me.user_a.Caption = "Andreas Stein" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("andreas-fynn")
    End If
    
' -----  USER MARIUS -----
    If Me.user_a.Caption = "Marius May" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-marius")
    End If
    If Me.user_a.Caption = "Marius May" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("martina-marius")
    End If
    If Me.user_a.Caption = "Marius May" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("andreas-marius")
    End If
    If Me.user_a.Caption = "Marius May" And Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("marius-ulrike")
    End If
    If Me.user_a.Caption = "Marius May" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("marius-angelika")
    End If
    If Me.user_a.Caption = "Marius May" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("marius-maximilian")
    End If
    If Me.user_a.Caption = "Marius May" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("marius-fynn")
    End If
    
' -----  USER ULRIKE -----
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-ulrike")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("martina-ulrike")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("andreas-ulrike")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("marius-ulrike")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("ulrike-angelika")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("ulrike-maximilian")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("ulrike-fynn")
    End If
    
' -----  USER ANGELIKA -----
    If Me.user_a.Caption = "Angelika Müller" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-angelika")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("martina-angelika")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("andreas-angelika")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("marius-angelika")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("ulrike-angelika")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("angelika-maximilian")
    End If
    If Me.user_a.Caption = "Ulrike Henrich" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("angelika-fynn")
    End If
    
' -----  USER MAXIMILIAN -----
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-maximilian")
    End If
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("martina-maximilian")
    End If
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("andreas-maximilian")
    End If
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("marius-maximilian")
    End If
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("ulrike-maximilian")
    End If
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("angelika-maximilian")
    End If
    If Me.user_a.Caption = "Maximilian Lehnen" & Me.TextBox3.Value = "Fynn" Then
        Set ws = Sheets("maximilian-fynn")
    End If
    
' -----  USER FYNN -----
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Heidi" Then
        Set ws = Sheets("heidi-fynn")
    End If
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Martina" Then
        Set ws = Sheets("martina-fynn")
    End If
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Andreas" Then
        Set ws = Sheets("andreas-fynn")
    End If
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Marius" Then
        Set ws = Sheets("marius-fynn")
    End If
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Ulrike" Then
        Set ws = Sheets("ulrike-fynn")
    End If
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Angelika" Then
        Set ws = Sheets("angelika-fynn")
    End If
    If Me.user_a.Caption = "Fynn Knippel" & Me.TextBox3.Value = "Maximilian" Then
        Set ws = Sheets("maximilian-fynn")
    End If
End Sub

'LISTBOX-FÜLLEN
Sub lb_füllen()
'    Dim ws As Worksheet
'    Dim i As Long
'
'    Call set_worksheet
'
'    Me.Caption = "Chat zwischen " & Me.user_a.Caption & " und " & Me.TextBox3.Value
'    With Worksheets(ws) '--Blattname anpassen
'        ListBox1.Clear
'        For i = 1 To .Cells(Rows.Count, "B").End(xlUp).Row '--Zeile 1 bis letzten benutzten Zelle in SpalteE
'        If .Cells(i, "B") = Me.user_a And .Cells(i, "C") = Me.TextBox3.Value Then '-- wenn Null dann
'            ListBox1.AddItem .Cells(i, "A") '--Statt A die Spalte deren Inhalt aufgefuehrt werden soll
'        End If
'        Next
'    End With
End Sub


Zu einem Vorschlag eines Gastes aus dem 1. Post:
Methode 1:
statt
 

'NEW-MESSAGE EVENTS
Sub new_message()
    Dim i As Integer
    Dim ws As Worksheet
'    Call set_worksheet
    
    With Sheets(ws)
        i = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
        .Cells(i, 1).Value = Me.TextBox4.Text
        .Cells(i, 2).Value = Me.user_a.Caption & " schrieb am: "
        .Cells(i, 3).Value = Date & " um: "
        .Cells(i, 4).Value = Time & " Uhr: "
        .Cells(i, 5).Value = Me.TextBox2.Text
        Me.TextBox2.Text = ""
    End With
End Sub

zu verwenden

'NEW-MESSAGE EVENTS
Sub new_message()
    Dim i As Integer
    Dim ws As Worksheet
'    Call set_worksheet
    
    With ws '<- Fehlermeldung "Objektvariable oder With-Blockvariable nicht definiert."
        i = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
        .Cells(i, 1).Value = Me.TextBox4.Text
        .Cells(i, 2).Value = Me.user_a.Caption & " schrieb am: "
        .Cells(i, 3).Value = Date & " um: "
        .Cells(i, 4).Value = Time & " Uhr: "
        .Cells(i, 5).Value = Me.TextBox2.Text
        Me.TextBox2.Text = ""
    End With
End Sub

 

Methode 2:
statt

'NEW-MESSAGE EVENTS
Sub new_message()
    Dim i As Integer
    Dim ws As Worksheet
'    Call set_worksheet
    
    With Sheets(ws)
        i = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
        .Cells(i, 1).Value = Me.TextBox4.Text
        .Cells(i, 2).Value = Me.user_a.Caption & " schrieb am: "
        .Cells(i, 3).Value = Date & " um: "
        .Cells(i, 4).Value = Time & " Uhr: "
        .Cells(i, 5).Value = Me.TextBox2.Text
        Me.TextBox2.Text = ""
    End With
End Sub



zu verwenden

 

'NEW-MESSAGE EVENTS
Sub new_message()
    Dim i As Integer
    Dim ws As Worksheet
'    Call set_worksheet
    
    ws = "marius-ulrike" '<- Fehlermeldung "Objektvariable oder With-Blockvariable nicht definiert."
    
    With Sheets(ws)
        i = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
        .Cells(i, 1).Value = Me.TextBox4.Text
        .Cells(i, 2).Value = Me.user_a.Caption & " schrieb am: "
        .Cells(i, 3).Value = Date & " um: "
        .Cells(i, 4).Value = Time & " Uhr: "
        .Cells(i, 5).Value = Me.TextBox2.Text
        Me.TextBox2.Text = ""
    End With
End Sub

Dabei kommen bei mir o.g. Fehlermeldungen bei rum.


Gruß


Ihre Antwort
  • Bitte beschreiben Sie Ihr Problem möglichst ausführlich. (Wichtige Info z.B.: Office Version, Betriebssystem, Wo genau kommen Sie nicht weiter)
  • Bitte helfen Sie ebenfalls wenn Ihnen geholfen werden konnte und markieren Sie Ihre Anfrage als erledigt (Klick auf Häckchen)
  • Bei Crossposting, entsprechende Links auf andere Forenbeiträge beifügen / nachtragen
  • Codeschnipsel am besten über den Code-Button im Text-Editor einfügen
  • Die Angabe der Emailadresse ist freiwillig und wird nur verwendet, um Sie bei Antworten auf Ihren Beitrag zu benachrichtigen
Thema: Name: Email:

 
 

  • Bitte beschreiben Sie Ihr Problem möglichst ausführlich. (Wichtige Info z.B.: Office Version, Betriebssystem, Wo genau kommen Sie nicht weiter)
  • Bitte helfen Sie ebenfalls wenn Ihnen geholfen werden konnte und markieren Sie Ihre Anfrage als erledigt (Klick auf Häckchen)
  • Bei Crossposting, entsprechende Links auf andere Forenbeiträge beifügen / nachtragen
  • Codeschnipsel am besten über den Code-Button im Text-Editor einfügen
  • Die Angabe der Emailadresse ist freiwillig und wird nur verwendet, um Sie bei Antworten auf Ihren Beitrag zu benachrichtigen

Thema Datum  Von Nutzer Rating
Antwort
Rot Probleme mit Worksheet Variable / Versuch 2
13.01.2023 14:37:09 Marius
NotSolved
13.01.2023 14:49:36 Gast28365
NotSolved
13.01.2023 15:06:54 Marius
NotSolved
13.01.2023 21:18:25 ralf_b
Solved
16.01.2023 13:39:37 Marius
NotSolved
13.01.2023 16:19:27 Gast41486
NotSolved