UserForm not updating properly

D

Damian37

Active Member
Joined
Jun 9, 2014
Messages
301
Office Version
  1. 365
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.

VBA Code: 
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
There is nothing in this code that would close the form. Do you have any code in the module for worksheet AllData?
 
Upvote 0
6StringJazzer said:
There is nothing in this code that would close the form. Do you have any code in the module for worksheet AllData?
Click to expand...

I was toying with an add record userform, but it wasn't working the way I wanted to. The code I wrote was:
VBA Code: 
Private Sub UserForm_Click()

Dim lRow As Long
Dim lPart As Long
Dim WS As Worksheet
Set WS = Worksheets("AllData")

lRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'RecordEntryForm.Show
    
    
With WS
    .Cells(lRow, 1).Value = Me.StoreID.Value
    .Cells(lRow, 2).Value = Me.Division.Value
    .Cells(lRow, 3).Value = Me.Region.Value
    .Cells(lRow, 4).Value = Me.StoreType.Value
    .Cells(lRow, 5).Value = Me.Storestatus.Value
    .Cells(lRow, 6).Value = Me.DateVerify.Value
    .Cells(lRow, 7).Value = Me.StorePOC.Value
    .Cells(lRow, 8).Value = Me.POCEmail.Value
    .Cells(lRow, 9).Value = Me.StoreMgr.Value
    .Cells(lRow, 10).Value = Me.MgrEmail.Value
    .Cells(lRow, 11).Value = Me.QHContact.Value
    .Cells(lRow, 12).Value = Me.QHEmail.Value
    .Cells(lRow, 13).Value = Me.DistroBP.Value
    .Cells(lRow, 14).Value = Me.CloseDate.Value
    .Cells(lRow, 15).Value = Me.RSAName.Value
    .Cells(lRow, 16).Value = Me.RSAEmail.Value
    .Cells(lRow, 17).Value = Me.ASMName.Value
    .Cells(lRow, 18).Value = Me.ASMEmail.Value
    .Cells(lRow, 19).Value = Me.ROMName.Value
    .Cells(lRow, 20).Value = Me.ROMEmail.Value
    .Cells(lRow, 21).Value = Me.FMMName.Value
    .Cells(lRow, 22).Value = Me.FMMEmail.Value
    .Cells(lRow, 23).Value = Me.BPContact.Value
    .Cells(lRow, 24).Value = Me.City.Value
    .Cells(lRow, 25).Value = Me.State.Value
    .Cells(lRow, 26).Value = Me.BrandedPartner.Value
    .Cells(lRow, 27).Value = Me.PartnerPM.Value
    .Cells(lRow, 28).Value = Me.PMEmail.Value
    
End With
End Sub
 
Upvote 0
Hi,
looking at both codes you posted, you should be able to add ./ update records from one form

based of your first post, try following & see if helps

VBA Code: 
Private Sub CommandButton2_Click()
    Dim WS As Worksheet
    Dim RecordRow As Range
    Dim i As Integer
    Dim Search As String, msg As String
    Dim NewRecord As Boolean
    Dim arr(1 To 28) As Variant
  
    Search = Me.TextBox1.Value
  
    NewRecord = False
  
    If Len(Search) = 0 Then
        MsgBox "You must enter a store number", 48, "Entry Required"
        Exit Sub
    Else
        For i = 1 To 28
            With Me.Controls("TextBox" & i)
                arr(i) = .Value
                .Value = ""
            End With
        Next i
    End If
      
        Set WS = ThisWorkbook.Worksheets("AllData")
      
'search existing record
        Set RecordRow = WS.Columns(1).Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole)
'record not found - add new record
        If RecordRow Is Nothing Then
            Set RecordRow = WS.Cells(WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1, 1)
            NewRecord = True
        End If
      
      
'add / update record
      
        RecordRow.Resize(, UBound(arr)).Value = arr
      
        msg = IIf(NewRecord, "New Record Added", "Record Updated")
      
        MsgBox Search & Chr(10) & msg, 48, msg
      
End Sub

code untested but should first search for matching value in textbox1 in Column A & if found, record updated.
If no match found, new record should be added

Hope Helpful

Dave
 
Last edited:
Upvote 0
dmt32 said:
Hi,
looking at both codes you posted, you should be able to add ./ update records from one form

based of your first post, try following & see if helps

VBA Code: 
Private Sub CommandButton2_Click()
    Dim WS As Worksheet
    Dim RecordRow As Range
    Dim i As Integer
    Dim Search As String, msg As String
    Dim NewRecord As Boolean
    Dim arr(1 To 28) As Variant
 
    Search = Me.TextBox1.Value
 
    NewRecord = False
 
    If Len(Search) = 0 Then
        MsgBox "You must enter a store number", 48, "Entry Required"
        Exit Sub
    Else
        For i = 1 To 28
            With Me.Controls("TextBox" & i)
                arr(i) = .Value
                .Value = ""
            End With
        Next i
    End If
     
        Set WS = ThisWorkbook.Worksheets("AllData")
     
'search existing record
        Set RecordRow = WS.Columns(1).Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole)
'record not found - add new record
        If RecordRow Is Nothing Then
            Set RecordRow = WS.Cells(WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1, 1)
            NewRecord = True
        End If
     
     
'add / update record
     
        RecordRow.Resize(, UBound(arr)).Value = arr
     
        msg = IIf(NewRecord, "New Record Added", "Record Updated")
     
        MsgBox Search & Chr(10) & msg, 48, msg
     
End Sub

code untested but should first search for matching value in textbox1 in Column A & if found, record updated.
If no match found, new record should be added

Hope Helpful

Dave
Click to expand...
Hey Dave,
Thank you for the updated code. QQ, is this code supposed to replace my entire code, or is this in addition to the code I've already written? It appears as though this code could replace my entire code. Is that correct? Thanks

D
 
Upvote 0
correct - Idea is that you just have one userform that automatically, performs both functions (adds or updates) record

Idea may need to be adjusted to meet specific project need.

Dave
 
Last edited:
Upvote 0
Notwithstanding the suggestion, which is a very good one, I still don't see what could be causing the form to close, and my original question was not addressed:

Do you have any code in the module for worksheet AllData?
 
Upvote 0
dmt32 said:
correct - Idea is that you just have one userform that automatically, performs both functions (adds or updates) record

Idea may need to be adjusted to meet specific project need.

Dave
Click to expand...
Hey Dave, the code worked at updating an existing record, however the code is moving the record to the right by one cell. So, my Division cell moves over to the cell to the right of it, and the store status ends up in the POC last verified date column. The entire record doesn't move to the right one cell, just columns B - E, and the date in column F is replaced with store status. Does that make sense. I tried changing this line of code:

VBA Code: 
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1, 0)

However that didn't resolve the issue.
 
Upvote 0
6StringJazzer said:
Notwithstanding the suggestion, which is a very good one, I still don't see what could be causing the form to close, and my original question was not addressed:

Do you have any code in the module for worksheet AllData?
Click to expand...
I did answer your question, and I included the coding that I currently have within module 1. I was trying to use the first userform I created to add records to the data worksheet. If you scroll up, you will see where I answered you and included the code I used. The userform worked the way I intended. It added a new record to the data worksheet, and all fields would go blank when I clicked on the add record button. The userform would stay visible until I clicked on the close form button.
 
Upvote 0
As I posted, code searches column A for a match to value in TextBox1 if found, the record in that row is updated by all values in the 28 textboxes,.
If no match found, then a new record will be added to the next blank row from column A onward. No offset of the ranges should occur.

If still have an issue, place copy of your workbook with sample data in a dropbox & provide a link to it.

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
USERFORM button for UPDATE error "block if without end if"
2
Replies
16
Views
604
dmt32
D
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
421
dmt32
D
D
  • Solved
TextBox looping help required in VBA
Replies
3
Views
385
DanteAmor
DanteAmor
B
  • Question
populating the next available row in a shared workbook
Replies
1
Views
261
rondeondo
rondeondo
B
  • Question
Populating a worksheet based on a Combo Box value within a User Form
2 3 4
Replies
35
Views
1K
igold
igold

Forum statistics

Threads
1,214,935
Messages
6,122,337
Members
449,077
Latest member
Jocksteriom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top