UserForm not updating properly

[Damian37]

I've created a userform to perform a search based on store number in order to make updates to the record associated with that store number. The search portion for the first command button works just fine, however, when I attempt to only update one field within the user form, I click on the second command button "Update", all of the data within the fields of the userform disappear and the userform itself closes. When I look at the code, I cannot find where the issue is. Here's the code for the second command button.

Private Sub CommandButton2_Click()
Dim WS As Worksheet:    Set WS = Worksheets("AllData")
Dim rFind As Range

If TextBox1.Value = "" Then
    MsgBox ("You must enter a store number")
    Exit Sub
End If

Set rFind = WS.Range("A1:A" & WS.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)

If Not rFind Is Nothing Then
    WS.Range("B" & rFind.Row) = TextBox3.Text
    WS.Range("C" & rFind.Row) = TextBox4.Text
    WS.Range("D" & rFind.Row) = TextBox5.Text
    WS.Range("E" & rFind.Row) = TextBox6.Text
    WS.Range("G" & rFind.Row) = TextBox7.Text
    WS.Range("H" & rFind.Row) = TextBox8.Text
    WS.Range("I" & rFind.Row) = TextBox9.Text
    WS.Range("J" & rFind.Row) = TextBox10.Text
    WS.Range("K" & rFind.Row) = TextBox11.Text
    WS.Range("L" & rFind.Row) = TextBox12.Text
    WS.Range("M" & rFind.Row) = TextBox13.Text
    WS.Range("N" & rFind.Row) = TextBox14.Text
    WS.Range("O" & rFind.Row) = TextBox15.Text
    WS.Range("P" & rFind.Row) = TextBox16.Text
    WS.Range("Q" & rFind.Row) = TextBox17.Text
    WS.Range("R" & rFind.Row) = TextBox18.Text
    WS.Range("S" & rFind.Row) = TextBox19.Text
    WS.Range("T" & rFind.Row) = TextBox20.Text
    WS.Range("U" & rFind.Row) = TextBox21.Text
    WS.Range("V" & rFind.Row) = TextBox22.Text
    WS.Range("W" & rFind.Row) = TextBox23.Text
    WS.Range("X" & rFind.Row) = TextBox24.Text
    WS.Range("Y" & rFind.Row) = TextBox25.Text
    WS.Range("Z" & rFind.Row) = TextBox26.Text
    WS.Range("AA" & rFind.Row) = TextBox27.Text
    WS.Range("AB" & rFind.Row) = TextBox28.Text
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
    TextBox13.Value = ""
    TextBox14.Value = ""
    TextBox15.Value = ""
    TextBox16.Value = ""
    TextBox17.Value = ""
    TextBox18.Value = ""
    TextBox19.Value = ""
    TextBox20.Value = ""
    TextBox21.Value = ""
    TextBox22.Value = ""
    TextBox23.Value = ""
    TextBox24.Value = ""
    TextBox25.Value = ""
    TextBox26.Value = ""
    TextBox27.Value = ""
    TextBox28.Value = ""

End If

End Sub

I was expecting the data within the fields to disappear once I click on the Update button, however, the data within the worksheet doesn't update, and the userform closes.
All help is much appreciated!

D

 

[dmt32]

The code I posted works in sequential order where

TextBox1 = Column A

TextBox2 = Column B

Etc. etc.

The simple solution would be to re-number your textboxes to match the columns they post their data to if that would be possible?

 

[Damian37]

The code I posted works in sequential order where

TextBox1 = Column A

TextBox2 = Column B

Etc. etc.

The simple solution would be to re-number your textboxes to match the columns they post their data to if that would be possible?

That did it Dave. Thank you very much for your patience with me on this issue. The form is updating properly, and I will now test for creating a new record. I've also written some code regarding setting up an automatic email. When I click on the button for automatic email s to be created, instead of having individual email box appearing, I'm redirected to the AllData tab. I'm not sure if you're familiar with automatic e-mails, but I started a new thread for this email issue. If you're familiar with automatic emails, I can paste the code I'm using on this thread, or provide the link for the new thread I started. Thank you again for all of your help!!

D

 

[dmt32]

That did it Dave. Thank you very much for your patience with me on this issue.

your welcome glad resolved

As suggestion, you could use same approach in your find code

Private Sub CommandButton1_Click()
    Dim WS As Worksheet
    Dim rFind As Range
    Dim Search As String
    
    Search = Me.TextBox1.Value
    
    If Len(Search) = 0 Then
        MsgBox ("You must enter a store number")
        Exit Sub
    End If
    
    Set WS = Worksheets("AllData")
    
    Set rFind = WS.Columns(1).Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rFind Is Nothing Then
        For i = 1 To 28
            Me.Controls("TextBox" & i).Value = rFind.Offset(, i - 1).Value
        Next i
            
        Else
            
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
        
End Sub

Dave