Office: VBA Textbox macht was sie will

Hallo,
ich bin ein Neuling bei VBA auch Excel habe ich bisher nie so intensiv benutzt wie in den letzten Wochen. Ich sitze jetzt schon länger vor einem Problem, welches ich weder mit Hilfe des Internets noch Chat gpt lösen konnte.
Ich habe bisher folgenden Code:

Code:

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Speichern der ursprünglichen Formel in B17
    originalFormula = Worksheets("Reporte").Range("B17").Formula

   
    Dim reportNumber As Variant
    reportNumber = Worksheets("Reporte").Range("B1").Value
    
    If WorksheetFunction.CountIf(Worksheets("Ausführliche Beschreibung").Columns(1), reportNumber) > 0 Then
        Dim targetRow As Long
        targetRow = WorksheetFunction.Match(reportNumber, Worksheets("Ausführliche Beschreibung").Columns(1), 0)
        
        Worksheets("Ausführliche Beschreibung").Activate
        Worksheets("Ausführliche Beschreibung").Cells(targetRow, 4).Select
        
        Cancel = True
    End If
End Sub

Private Sub TextBox1_Change()
    ' Nach jeder Änderung in der TextBox die ursprüngliche Formel in B17 wiederherstellen
    Worksheets("Reporte").Range("B17").Formulalokal = originalFormula
End Sub

Private Sub TextBox1_Click()
    ' Die Bearbeitung der TextBox verhindern
    TextBox1.Locked = True
    TextBox1.Enabled = False
End Sub

Was ich damit erreichen möchte: Ich möchte in einer VBA Textbox den Text der Zelle B17 darstellen, die den Text mit Hilfe der Formel =SVERWEIS($B$1;AusführlicheBeschreibung;SPALTEN(AusführlicheBeschreibung[[#Kopfzeilen];[Reportnummer]:[Ausführliche Beschreibung]]);FALSCH) ausgibt. Die Zelle B17 bezieht sich also auf die Reportnummer und gibt den entsprechenden Text aus. Ich möchte das ganze allerdings Scrollbar machen, deshalb das Textfeld. Bei einem Doppeltklick kommt man auf die Zelle, in die der Text eingepflegt/bearbeitet werden kann.

Soweit die Vorstellung.
Das Problem:
Wenn ich in die Textbox Klicke, kann ich sie bearbeiten. Ich kann also den Text anpassen. Was aber dem Benutzer nicht möglich sein sollte.
Das Fette Problem: mir wird immer die Formel der Originalzelle gelöscht. Mal funktioniert es und es greift denn richtigen Text und dann funktioniert es wieder nicht. Und wenn ich den Text in der Textbox bearbeite, was ja eigentlich nicht möglich sein sollte, ist die vollkatastrophe komplett und es wird gar nichts mehr angezeigt.
Außerdem funktioniert das erste Makro in dem Arbeitsblatt übersicht nicht mehr (Doppelklick auf Reportnummer)

 

Hi,

wenn ich dein Problem richtig verstanden habe versuche es auf diesem Weg: die TextBox hat die Eigenschaft Enabled und normalerweise steht sie auf True - setze sie auf False, dann ist die TextBox "gesperrt".

---

 

Hi,
das ist leider nicht wirklich was ich meine. Wenn Enable false ist, wird die Textbox ausgegraut, das ist leider nicht so wie ich mir das vorstelle, aber Danke.

Ich versuche es nochmal zu erklären (Fällt mir etwas schwer in Textform)
Tabellenblatt übersicht: Wenn ich auf eine Reportnummer doppeltklicke, soll die Nummer auf die ich geklickt habe in die Zelle B1 in das Tabellenblatt Reporte geschrieben werden. Dafür verwende ich folgenden Code

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim wsOverview As Worksheet
    Dim wsReports As Worksheet
    Dim sourceColumn As Long
    Dim sourceValue As Variant

    ' Überprüfen, ob der Doppelklick innerhalb der Tabelle "Übersicht" erfolgt
    Set wsOverview = ThisWorkbook.Sheets("Übersicht")
    If Target.Worksheet.Name = wsOverview.Name Then
        ' Überprüfen, ob der Doppelklick in der Spalte A erfolgt
        sourceColumn = wsOverview.Columns("A").Column
        If Target.Column = sourceColumn And Target.Row > 1 Then
            ' Wert der ursprünglichen Zelle erhalten
            sourceValue = Target.Value

            ' Arbeitsblatt "Reporte" aktivieren und zur Zelle B1 springen
            Set wsReports = ThisWorkbook.Sheets("Reporte")
            wsReports.Activate

            ' B1 mit dem Wert der ursprünglichen Zelle füllen (als Zahl interpretieren)
            On Error Resume Next
            wsReports.Range("B1").Value = CDbl(sourceValue)
            On Error GoTo 0

            ' Doppelklick-Aktion abbrechen
            Cancel = True
        End If
    End If
End Sub

Jetzt taucht hier aber durch meinen oben in der Frage angegebenen Code ein Laufzeitfehler auf. Davor hat das zumindest funktioniert.

In dem Tabellenblatt Reporte:
Dort ist der Code den ich oben in der Frage eingefügt habe angegeben:
Ich möchte den Text in der Textbox darstellen, der in Zelle B17 mithilfe des Sverweises dort ausgegeben wird. Mit einem Doppeltklick in das Textfeld soll man in die Tabelle kommen, in der der Text hinterlegt ist, um ihn direkt bearbeiten zu können. Man soll den Text in der Textbox nicht bearbeiten können, das geht aber im Moment, mit einem einfachen klick. Enabled will ich nicht auf false stellen, weil es sonst ausgegraut wird. Eigentlich sollte dafür ja die Funktion Locked da sein oder?

Das komische ist, wenn man den Text in der Textbox abändert, dann geht die Formel in der Zelle B17 verloren und passt sich dem geänderten Text in der Textbox an.
Ich vermute dass es vor allem an diesem Part liegt:

Code:

Private Sub TextBox1_Change()
    ' Nach jeder Änderung in der TextBox die ursprüngliche Formel in B17 wiederherstellen
    Worksheets("Reporte").Range("B17").Formulalokal = originalFormula
End Sub

Ein weiteres Problem ist, dass es manchmal funktioniert mit der Textübernahme aus Zelle B17 und manchmal nicht. Manchmal ist dann einfach wieder die Formel in Zelle B17 gelöscht und ich habe keine Ahnung wieso

Ich hoffe es ist ungefähr verständlich was ich meine. Tut mir leid wenn nicht, aber ich beantworte gerne jede Frage.

Vielen Dank :)

 

Hi,

wenn dir das nicht passt dass die TextBox ausgegraut ist durch Enabled, dann nimm eben Locked - wo ist das Problem? Dein eigentliches Problem war doch, dass eine Änderung des TextBox-Inhaltes deine Formel überschreibt (ist ja auch logisch weil du ein Change-Ereignis in der TextBox hast) - und sowohl Enabled als auch Locked verhindern das Ändern des TextBox-Inhaltes.

---

 

Hallo,

du hast einen Syntaxfehler im Code an der rot gekennzeichneten Stelle: statt "k" gehört "c".
Private Sub TextBox1_Change()
' Nach jeder Änderung in der TextBox die ursprüngliche Formel in B17 wiederherstellen
Worksheets("Reporte").Range("B17").FormulaLocal = originalFormula
End Sub

Solche Fehler wären leicht zu vermeiden, wenn du als allererste Zeile in jedem Codemodul folgende Zeile einfügen würdest:
Option Explicit

 

Ich habe Locked drin, aber es ist nicht wirklich locked. Ich verstehe nicht wieso und ich verstehe nicht, warum es die Zelle B17 ändert, wenn man die Textbox ändert.
Mit einem einfachen klick soll nichts passieren.(funktioniert nicht)
Mit einem Doppelklick möchte ich auf die nächste Tabelle springen. (funktioniert)

Das Change Ereignis hat damit nicht direkt was zu tun. Das soll ja praktisch nur befehlen, dass es wieder auf den sverweis umgestellt wird. Aber das funktioniert leider nicht. Aber du hast recht, die Change funktion kann man auf jeden Fall draußen lassen und es sorgt für weniger Probleme. Das Kernproblem bleibt aber bestehen.

 

Hi Exl121150,
vielen Dank für den Hinweis, das ist wirklich Hilfreich.
Ich habe jetzt den Code wie folgt abgeändert:

Code:

Option Explicit
Dim originalFormula As Variant
Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


    ' Speichern der ursprünglichen Formel in B17
    originalFormula = Worksheets("Reporte").Range("B17").Formula

    ' Dein bisheriger Code bleibt unverändert
    Dim reportNumber As Variant
    reportNumber = Worksheets("Reporte").Range("B1").Value
    
    If WorksheetFunction.CountIf(Worksheets("Ausführliche Beschreibung").Columns(1), reportNumber) > 0 Then
        Dim targetRow As Long
        targetRow = WorksheetFunction.Match(reportNumber, Worksheets("Ausführliche Beschreibung").Columns(1), 0)
        
        Worksheets("Ausführliche Beschreibung").Activate
        Worksheets("Ausführliche Beschreibung").Cells(targetRow, 4).Select
        
        Cancel = True
    End If
End Sub
Private Sub TextBox1_Change()
    ' Nach jeder Änderung in der TextBox die ursprüngliche Formel in B17 wiederherstellen
    Worksheets("Reporte").Range("B17").FormulaLocal = originalFormula
End Sub

Private Sub TextBox1_Click()
    ' Die Bearbeitung der TextBox verhindern
    TextBox1.Locked = True
    TextBox1.Enabled = False
End Sub

Aber ich das Problem ist, dass der sverweis aus der Zelle B17 gelöscht wird. Ich verstehe ehrlich nicht wieso

 

Also ok, wenn ich jetzt die change Funktion ausklammere funktioniert es besser, aber ich kann die Textbox halt immer noch bearbeiten. Haha ich werde noch wahnsinnig

 

Für Leute, die ein ähnliches Problem haben:

Code:

Option Explicit
Dim originalFormula As Variant
Dim updatingTextBox As Boolean

Private Sub UserForm_Initialize()
    ' Initialisiere die TextBox mit dem Inhalt von B17
    TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    TextBox1.Locked = True ' TextBox sperren
End Sub

Private Sub TextBox1_Click()
    ' Die Bearbeitung der TextBox verhindern
    TextBox1.Locked = True
End Sub

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Speichern der ursprünglichen Formel in B17
    originalFormula = Worksheets("Reporte").Range("B17").Formula

 
    Dim reportNumber As Variant
    reportNumber = Worksheets("Reporte").Range("B1").Value
 
    ' Überprüfen, ob die Reportnummer in der Ausführlichen Beschreibung vorhanden ist
    If WorksheetFunction.CountIf(Worksheets("Ausführliche Beschreibung").Columns(1), reportNumber) > 0 Then
        Dim targetRow As Long
        targetRow = WorksheetFunction.Match(reportNumber, Worksheets("Ausführliche Beschreibung").Columns(1), 0)
     
        ' Zum Ziel-Zellenbereich in der Ausführlichen Beschreibung springen
        Worksheets("Ausführliche Beschreibung").Activate
        Worksheets("Ausführliche Beschreibung").Cells(targetRow, 4).Select
     
        Cancel = True
        TextBox1.Locked = True ' TextBox sperren
    End If
End Sub

Private Sub TextBox1_Change()
    ' Überprüfen, ob die TextBox gerade aktualisiert wird
    If updatingTextBox Then
        Exit Sub ' Beenden, um eine Endlosschleife zu verhindern
    End If
 
    ' Wiederherstellen des vorherigen Textes in der TextBox
    updatingTextBox = True
    TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    updatingTextBox = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Überprüfen, ob sich der Wert in B1 ändert
    If Target.Address = "$B$1" Then
        ' Den Wert in der TextBox aktualisieren
        TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    End If
End Sub

Mit dem Code funktioniert es bei mir jetzt. Man muss auf jeden Fall LinkedCell aus den Eigenschaften rausnehmen. Zur Eläuterung, immer wenn dich B1 ändert, auf den sich der Wert der Zelle B17 mit sverweis bezieht, wird die Textbox aktualisiert und der aktuelle Wert aus B17 wird in die Textbox eingetragen.

 

Kleider Nachtrag: Das einzige was jetzt natürlich stört: Wenn ich z.B. zu einem Report eine Beschreibung hinzufüge, dann wird diese im Anschluss nicht automatisch in der Textbox angezeigt, solange ich B1 (Die Zelle in der die Reportnummer steht) noch nicht verändert habe. Das ist ok, nervt mich aber trotzdem. Vielleicht fällt mir noch was ein.

 

Der folgende Code löst jetzt auch das Problem. Die Textbox wird jetzt aktualisiert, sobald das Arbeitsblatt geöffnet wird.

Code:

Option Explicit
Dim originalFormula As Variant
Dim updatingTextBox As Boolean

Private Sub UserForm_Initialize()
    ' Initialisiere die TextBox mit dem Inhalt von B17
    TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    TextBox1.Locked = True ' TextBox sperren
End Sub

Private Sub TextBox1_Click()
    ' Die Bearbeitung der TextBox verhindern
    TextBox1.Locked = True
End Sub

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Speichern der ursprünglichen Formel in B17
    originalFormula = Worksheets("Reporte").Range("B17").Formula
    
    Dim reportNumber As Variant
    reportNumber = Worksheets("Reporte").Range("B1").Value
    
    If WorksheetFunction.CountIf(Worksheets("Ausführliche Beschreibung").Columns(1), reportNumber) > 0 Then
        Dim targetRow As Long
        targetRow = WorksheetFunction.Match(reportNumber, Worksheets("Ausführliche Beschreibung").Columns(1), 0)
        
        Worksheets("Ausführliche Beschreibung").Activate
        Worksheets("Ausführliche Beschreibung").Cells(targetRow, 4).Select
        
        Cancel = True
        TextBox1.Locked = True ' TextBox sperren
    End If
End Sub

Private Sub TextBox1_Change()
    ' Überprüfen, ob die TextBox gerade aktualisiert wird
    If updatingTextBox Then
        Exit Sub ' Beenden, um eine Endlosschleife zu verhindern
    End If
    
    ' Wiederherstellen des vorherigen Textes in der TextBox
    updatingTextBox = True
    TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    updatingTextBox = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Überprüfen, ob sich der Wert in B1 ändert
    If Target.Address = "$B$1" Then
        ' Den Wert in der TextBox aktualisieren
        TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    End If
    
    ' Überprüfen, ob sich etwas in der Tabelle "Ausführliche Beschreibung" ändert
    If Target.Worksheet.Name = "Ausführliche Beschreibung" Then
        ' Den Wert in der TextBox aktualisieren
        TextBox1.Value = Worksheets("Reporte").Range("B17").Value
    End If
End Sub

Private Sub Worksheet_Activate()
    ' Aktiviere das Blatt: Den Wert in der TextBox aktualisieren
    TextBox1.Value = Worksheets("Reporte").Range("B17").Value
End Sub