CODEWITHSUNDEEP
excelvbatextbox
Leon S
Leon S

Reputation: 103

Value of Textbox gets reset

I tried to reuse the code for searching a value in a sheet, but I seem to do something wrong. I have a Userform with a Textbox called "KD_ID" which i need to get the data that has been put into it and searched on a sheet ("Stammdaten") in Excel, then combine some cells next to it into one string. (KD_RNG -> Global Variable)

I use this code:

 Private Sub KD_Search_Click()
        Dim KD_Search As String

       KD_Search = Packzettelinfo.KD_ID

        Set KD_RNG = ThisWorkbook.Sheets("Stammdaten").Range("A:A").Find(KD_Search, , xlValues, xlWhole)
        If Not KD_RNG Is Nothing Then
    Customer_Combination.Value = KD_RNG.Offset(0, 1).Value & vbNewLine & KD_RNG.Offset(0, 2).Value & KD_RNG.Offset(0, 3).Value & vbNewLine & KD_RNG.Offset(0, 5).Value & vbNewLine & KD_RNG.Offset(0, 6).Value & KD_RNG.Offset(0, 7).Value




        Else
        MsgBox "Kundennummer konnte nicht gefunden werden (Fehler #002)"
        Packzettelinfo.KD_ID.SetFocus
        Exit Sub
        End If

    End Sub

The thing is that I search in another sub for "PZ_ID" (Label PZ-Nr. in the screenshot below) and the textbox "KD_ID" (Label KD-Nr.) is prefilled, before the next search.

This shows the problem

So if I change "12345" to "23456", 

 Customer_Combination.Value = KD_RNG.Offset(0, 1).Value & vbNewLine & KD_RNG.Offset(0, 2).Value & KD_RNG.Offset(0, 3).Value & vbNewLine & KD_RNG.Offset(0, 5).Value & vbNewLine & KD_RNG.Offset(0, 6).Value & KD_RNG.Offset(0, 7).Value

will return nothing and KD_ID is reset to "12345"...

Upvotes: 0

Views: 77

Answers (2)

Variatus
Variatus

Reputation: 14373

I reformatted your code in order to be able to understand it. Now that you have solved your problem I thought you might make use of it. Note that neither Packzettelinfo (presumably the name of your userform), nor the Me keyword that can take its place, are required while the code is on the userform's own code sheet.

Private Sub KD_Search_Click()

    Dim KD_Search   As String
    Dim KD_Rng      As Range

    KD_Search = KD_ID.Value
    Set KD_Rng = ThisWorkbook.Sheets("Stammdaten").Range("A:A") _
                             .Find(KD_Search, , xlValues, xlWhole)
    If KD_Rng Is Nothing Then
        MsgBox "Kundennummer konnte nicht gefunden werden (Fehler #002)"
        KD_ID.SetFocus
    Else
        With KD_Rng.Resize(1, 8)
            Customer_Combination.Value = .Cells(2).Value & vbNewLine & _
                                         .Cells(3).Value & .Cells(4).Value & vbNewLine & _
                                         .Cells(6).Value & vbNewLine & _
                                         .Cells(7).Value & .Cells(8).Value
        End With
    End If
End Sub

Upvotes: 1

Viktor West
Viktor West

Reputation: 574

I tried to simulate your application. (I cannot comment, so I am writing an answer). The code below running well at least without any problem in my computer. I have not found any big problem. I included an alternative line with label Me.Customer_Combination.Caption, since you are not using it for input (as far as I understood), but just displaying the result. Note: Using by textbox the text of value property is the same. I also commented out two lines, since they are not necessary.

Dim KD_RNG As Range
Private Sub KD_Search_Click()
    Dim KD_Search As String
    KD_Search = Me.KD_ID.Text
    Set KD_RNG = ThisWorkbook.Sheets("Stammdaten").Range("A:A").Find(KD_Search, , xlValues, xlWhole)
    If Not KD_RNG Is Nothing Then
        Me.Customer_Combination.Caption = KD_RNG.Offset(0, 1).Value & vbNewLine & KD_RNG.Offset(0, 2).Value & KD_RNG.Offset(0, 3).Value & vbNewLine & KD_RNG.Offset(0, 5).Value & vbNewLine & KD_RNG.Offset(0, 6).Value & KD_RNG.Offset(0, 7).Text
        Me.Customer_CombinationTextBox.Text = KD_RNG.Offset(0, 1).Value & vbNewLine & KD_RNG.Offset(0, 2).Value & KD_RNG.Offset(0, 3).Value & vbNewLine & KD_RNG.Offset(0, 5).Value & vbNewLine & KD_RNG.Offset(0, 6).Value & KD_RNG.Offset(0, 7).Text
    Else
        MsgBox "Kundennummer konnte nicht gefunden werden (Fehler #002)"
        'Me.KD_ID.SetFocus
        'Exit Sub
    End If
End Sub

Upvotes: 1

Related Questions