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
 
Sort by date Sort by votes
dmt32 said:
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
Click to expand...

1585940389233.png


I enter a store #, then click on Search button:

All information within the record appear in the userform except for the Update date:

1585940482602.png


I change status from Open to Closed, and click on the Update button, and receive the "Record Updated" message, However when I check the record in the AllData tab, this is what I see:

1585940598351.png


Not sure why this is happening.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Damian37 said:
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.
Click to expand...
You showed the code in your UserForm module, but did not say whether there is any code in the module for worksheet AllData.
 
Upvote 0
6StringJazzer said:
You showed the code in your UserForm module, but did not say whether there is any code in the module for worksheet AllData.
Click to expand...
This is the code that is in Module 1 for worksheet AllData:

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
dmt32 said:
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
Click to expand...
Hi Dave,
Would you happen to have any idea why cells B thru E would move over one cell when an update is made to the record. Could it have anything to do with the number 48 within the code you provided me. I find it strange how only those cells seem to be affected, but none of the other cells within the record are affected. I also updated the code to include the update date, and that is now populating in column B instead of column F where it was originally. It's as though the form is taking the information from the record, and rearranging just those cells. It's very strange.

D.
 
Upvote 0
Damian37 said:
This is the code that is in Module 1 for worksheet AllData:
Click to expand...
If you have a module called Module1, it is not the code for the sheet AllData. The code for AllData would be in a module called something like "Sheet1 (AllData)". The code you showed here is for the UserForm and if it is not in the UserForm module it won't do anything at all. Maybe we have a disconnect on how to refer to modules.
 
Upvote 0
Damian37 said:
I change status from Open to Closed, and click on the Update button, and receive the "Record Updated" message, However when I check the record in the AllData tab, this is what I see:

View attachment 10501

Not sure why this is happening.
Click to expand...

from this neither am I - if as I asked, can place copy of your workbook in a dropbox & provide link to it - I will have a look for you

Dave
 
Upvote 0
Damian37 said:
Hi Dave,
Would you happen to have any idea why cells B thru E would move over one cell when an update is made to the record. Could it have anything to do with the number 48 within the code you provided me. I find it strange how only those cells seem to be affected, but none of the other cells within the record are affected. I also updated the code to include the update date, and that is now populating in column B instead of column F where it was originally. It's as though the form is taking the information from the record, and rearranging just those cells. It's very strange.

D.
Click to expand...

Number 48 is a value in the msgbox to Display Warning Message icon

At this stage, I can only conclude that there is a coding miss-match between the values in the database and the textbox controls they are displayed in & code I posted when updating.

If unable to place a copy of your workbook in a dropbox then post the search code you have that returns a record to the form

Dave
 
Upvote 0
dmt32 said:
Number 48 is a value in the msgbox to Display Warning Message icon

At this stage, I can only conclude that there is a coding miss-match between the values in the database and the textbox controls they are displayed in & code I posted when updating.

If unable to place a copy of your workbook in a dropbox then post the search code you have that returns a record to the form

Dave
Click to expand...

Morning Dave,
I don't have a dropbox to link to, so I am going to paste the code I'm using to search for records:
VBA Code: 
Private Sub CommandButton1_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
    TextBox3.Text = WS.Range("B" & rFind.Row)
    TextBox4.Text = WS.Range("C" & rFind.Row)
    TextBox5.Text = WS.Range("D" & rFind.Row)
    TextBox6.Text = WS.Range("E" & rFind.Row)
    TextBox2.Text = WS.Range("F" & rFind.Row)
    TextBox7.Text = WS.Range("G" & rFind.Row)
    TextBox8.Text = WS.Range("H" & rFind.Row)
    TextBox9.Text = WS.Range("I" & rFind.Row)
    TextBox10.Text = WS.Range("J" & rFind.Row)
    TextBox11.Text = WS.Range("K" & rFind.Row)
    TextBox12.Text = WS.Range("L" & rFind.Row)
    TextBox13.Text = WS.Range("M" & rFind.Row)
    TextBox14.Text = WS.Range("N" & rFind.Row)
    TextBox15.Text = WS.Range("O" & rFind.Row)
    TextBox16.Text = WS.Range("P" & rFind.Row)
    TextBox17.Text = WS.Range("Q" & rFind.Row)
    TextBox18.Text = WS.Range("R" & rFind.Row)
    TextBox19.Text = WS.Range("S" & rFind.Row)
    TextBox20.Text = WS.Range("T" & rFind.Row)
    TextBox21.Text = WS.Range("U" & rFind.Row)
    TextBox22.Text = WS.Range("V" & rFind.Row)
    TextBox23.Text = WS.Range("W" & rFind.Row)
    TextBox24.Text = WS.Range("X" & rFind.Row)
    TextBox25.Text = WS.Range("Y" & rFind.Row)
    TextBox26.Text = WS.Range("Z" & rFind.Row)
    TextBox27.Text = WS.Range("AA" & rFind.Row)
    TextBox28.Text = WS.Range("AB" & rFind.Row)
    
End If

End Sub

Textbox2 is the date, but it is the seventh column within the AllData tab. After making a couple of changes, now instead of there being a blank cell for Division, the cells still move to the right, however, the date within textbox2 is in Column B which is Division information. I hope that makes sense.

D
 
Upvote 0
Damian37 said:
Textbox2 is the date, but it is the seventh column within the AllData tab. After making a couple of changes, now instead of there being a blank cell for Division, the cells still move to the right, however, the date within textbox2 is in Column B which is Division information. I hope that makes sense.

D
Click to expand...

just to be clear, TextBox2 in your code posts data to Column F - is this still correct? If not, post copy of updated code you are using.

Dave
 
Upvote 0
dmt32 said:
just to be clear, TextBox2 in your code posts data to Column F - is this still correct? If not, post copy of updated code you are using.

Dave
Click to expand...
Yes it does. When I click search, all the information within all of the fields are correct. It isn't until I hit the second command button to update that I hit a snag. Once I click on the update button. The change I made shows in my data, however, now there's a date in column B - Division. Another strange thing is it appears only columns B-F are affected. I tried to change a different field (I.e. the POC and the email). The update was successful, however the values from B-F still moved to the right, and the date was again in column B instead of column F where it should be.
 
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
618
dmt32
D
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
450
dmt32
D
D
  • Solved
TextBox looping help required in VBA
Replies
3
Views
401
DanteAmor
DanteAmor
B
  • Question
populating the next available row in a shared workbook
Replies
1
Views
275
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,215,657
Messages
6,126,065
Members
449,286
Latest member
Lantern

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