interactive userform (read/write)

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,559
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

 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 27, 2016
Messages
1,559
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
I'm not really interested in what the wed site says.

I want to know what you want.

What do you want?
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,559
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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.

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 27, 2016
Messages
1,559
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
So now you want to modify the name or city and the reload those values back into the sheet is that correct?
 

Forum statistics

Threads
1,089,492
Messages
5,408,596
Members
403,217
Latest member
dmcmaste

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top