interactive userform (read/write)

[lezawang]

Hi
I want to learn how to make simple interactive form like the one below which can upload data from excel sheet to the form if user entered an existing ID# or if the ID is not existed then that would be a new record to be added to the form. ExcelEasy website has the code but I have found it very difficult to follow. I wonder if there is a simpler code that I can read to understand the idea and how it can be done?
Would appreciate any help very much. Thanks you

https://www.excel-easy.com/vba/examples/interactive-userform.html

 

[My Aswer Is This]

Let me see if I understand

Your wanting to load data into the Userform

So in this example when you enter a value into the Textbox Named Id you want the Name and City loaded into the Userform Textboxes

Now I do not under your other question where you say:

or if the ID is not existed then that would be a new record to be added to the form

We do not add records to a Userform. A record refers to a row of data on a Sheet.

If there is no record we cannot load it's values into the Userform
Or do you mean take the values from a Userform and create a new record on the sheet.
Record is a sheet row.


So do you want to start by loading values into the userform?

Now after you load values into the userform what do you plan to do next?

​

 

[lezawang]

Thanks a lot for your reply. Sorry I was not clear.
I wrote an explanation using my own words but then after reading it, I found it not that clear. So I went to the website and copied exactly what this form suppose to do which is better explaintion than mine. Thank you very much once again. Here is the explanation

"Explanation: whenever you enter a value in the ID text box, Excel VBA loads the corresponding record. When you click the Edit / Add button, Excel VBA edits the record on the sheet or adds the record when the ID does not yet exist"

 

[My Aswer Is This]

I'm not really interested in what the wed site says.

I want to know what you want.

What do you want?

 

[lezawang]

Ok thanks. If the user entered ID# and if this ID existed in the sheet, then excel will put other values from the sheet to the form for example Name and the City. Then user might change the city name for example and hit Edit/Add button then that row will be updated accordingly with these new values (in this case City name)

If the user entered ID which is not in the sheet, then Name and City boxes will stay empty. In this case if the user entered values in the Name and City boxes and hit Edit/Add then a new row will be created in the sheet with this ID, Name, and the City.

Thank you once again.

 

[My Aswer Is This]

If you have a textbox on your userform named id
And a Textbox named MyName and a Textbox Named City

And you put this script in a command button on your userform

This script will look down column A of the active sheet for the id number entered in id textbox

If found the name in column B and the city in column C will be loaded in the textbox named MyName and City

Now what you plan to do next is not clear.

Private Sub CommandButton3_Click()
'Modified  11/28/2018  7:56:08 PM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
ans = id.Value
Set SearchRange = Range("A1:A" & Lastrow).Find(ans)
If SearchRange Is Nothing Then MsgBox ans & "  Not Found": Exit Sub
MyName.Value = SearchRange.Offset(, 1).Value
City.Value = SearchRange.Offset(, 2).Value
End Sub

 

[My Aswer Is This]

I now see your last post.
Try my script to see if it does the first part you want.
And this assumes all your data is on the active sheet.

 

[lezawang]

The code above works very well. Thank you. Yes that is the first part is exactly what I expected. Wow your code is so easy to understand. Thank you once again.

 

[My Aswer Is This]

So now you want to modify the name or city and the reload those values back into the sheet is that correct?

 

[lezawang]

Yes correct. Thank you once again.