Update WorkSheet from Userform

[charly1988]

I have item sheet which has a named range call "ItemList" and it has 3 fields "productID, productName and productDescription" . All i want is update a row in the named range using productID from userform. I have a update button in my userform.

 

[Anthony47]

Try adapting this snippet to your Update button code:

Range("ItemList").Cells(1, 1).Value = Me.TextBox1.Value
Range("ItemList").Cells(1, 2).Value = Me.TextBox2.Value
Range("ItemList").Cells(1, 3).Value = Me.TextBox3.Value

I assumed that ItemList develops horizontally; if it develop vertically then modify Cells(1, 2) to Cells(2, 1) and Cells(1, 3) to Cells(3, 1)

 

[charly1988]

Try adapting this snippet to your Update button code:

Range("ItemList").Cells(1, 1).Value = Me.TextBox1.Value
Range("ItemList").Cells(1, 2).Value = Me.TextBox2.Value
Range("ItemList").Cells(1, 3).Value = Me.TextBox3.Value

I assumed that ItemList develops horizontally; if it develop vertically then modify Cells(1, 2) to Cells(2, 1) and Cells(1, 3) to Cells(3, 1)

Thank you.., but i need complete set of code which checks any changes made to data if not let the user knows that he doesn't make any changes. If any changes made then update that unique row of data using ItemID

 

[Anthony47]

I think you should better describe how your data are organized, how the userform is organized, what you wish to achieve and about which area you need to receive support.

 

[charly1988]

i have a sheet name call "items" and have a name ranged call "itemlist" with has 4 columns (itemID, itemName, Country and Description). In my userform i have a label for itemID and 3 textboxes for other item name, country and Description. And Items will be shown in a listbox. If user select an Item from listbox, item details will be populated on textboxes (Item name, country and description). If user click the update button without making any changes to the data then a message has to be shown that there is no changes made or if he leave a field empty it has to be also been shown in a massage that "Can't have the textbox empty". If he has made proper changes and then click the update button, then it has to update the row where itemID populated in Label.

I think you should better describe how your data are organized, how the userform is organized, what you wish to achieve and about which area you need to

 

[Anthony47]

With some guessworking I can suggest the following:
-on top of the userform code module, define a common variable:

Dim Running As Boolean

-let's guess that your command button is named "UPDATE"
-I guess you have a ListBoc_Click macro; add these two lines at the beginning and at the end of your code

Private Sub ListBox1_Click()
If Running Then Exit Sub     '++ADD
'your code
'your code
'your code
Me.UPDATE.Enabled = False    '++ADD
End Sub

-for each of the three textbox add a TextBox_Change macro that contains

Private Sub TextBox1_Change()    'adapt with the name of the TextBoxes
Me.UPDATE.Enabled = True
End Sub

-finally, use this code for the UPDATE_Click:

Private Sub UPDATE_Click()
Dim oVal As Long
'
If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Then
    MsgBox ("Empty values not allowed, correct before updating")
    Exit Sub
End If
'
Running = True
oVal = Me.ListBox1.ListIndex + 1
Range("itemlist").Cells(oVal, 2).Value = Me.TextBox1.Value
Range("itemlist").Cells(oVal, 3).Value = Me.TextBox2.Value
Range("itemlist").Cells(oVal, 4).Value = Me.TextBox3.Value
Me.UPDATE.Enabled = False
Running = False
End Sub

Adapt as needed