interactive userform (read/write)

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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

interactive-userform-1.png
 
This script will load the values from the Userform to the sheet.
You will need a different command button for now. Put this script in a different button
I would have to think some if you need the same button to perform both tasks
Code:
Private Sub CommandButton2_Click()
'Load values back into sheet
'Modified  11/28/2018  8:31:29 PM  EST
Dim ans As String
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ans = id.Value
Set SearchRange = Range("A1:A" & Lastrow).Find(ans)
If SearchRange Is Nothing Then MsgBox ans & "  Not Found": Exit Sub
SearchRange.Offset(, 1).Value = MyName.Value
SearchRange.Offset(, 2).Value = City.Value

End Sub
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
This works so perfect. Thank you so much. For now that is what really I want. I will spend time to understand it very well. In the future, I will learn how to do both at same time but I am so happy with the both codes now and I can not thank you enough. Thank you so so much once again.
 
Upvote 0
This script replaces the first script I sent you.
If id is found then values from sheet are uploaded to Userform
If id is not found values from userform are loaded into next empty row on sheet
Code:
Private Sub CommandButton3_Click()
'Modified  11/28/2018  9:01:15 PM  EST
'Upload or add to
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 We will add new record"
Cells(Lastrow + 1, 1).Value = ans
Cells(Lastrow + 1, 2).Value = MyName
Cells(Lastrow + 1, 3).Value = City
Exit Sub
End If

MyName.Value = SearchRange.Offset(, 1).Value
City.Value = SearchRange.Offset(, 2).Value
End Sub
 
Last edited:
Upvote 0
Yes that is great. That is what I was hoping/thinking as well. You read my mind :) thank you very much once again
 
Upvote 0
No if you want to do all three with the same button.
Look at how this works.

Code:
Private Sub CommandButton3_Click()
'Modified  11/29/2018  12:30:31 AM  EST
'Do all three. Up load data to UseForm or add new record or Down load to sheet changes
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
ans = id.Value
anss = MsgBox("Do you want to download Changes", vbYesNo)
If anss = vbYes Then GoTo M
Set SearchRange = Range("A1:A" & Lastrow).Find(ans)
If SearchRange Is Nothing Then
MsgBox ans & "  Not Found We will add new record"
Cells(Lastrow + 1, 1).Value = ans
Cells(Lastrow + 1, 2).Value = MyName
Cells(Lastrow + 1, 3).Value = City
Exit Sub
End If

MyName.Value = SearchRange.Offset(, 1).Value
City.Value = SearchRange.Offset(, 2).Value
M:
ans = id.Value
Set SearchRange = Range("A1:A" & Lastrow).Find(ans)
If SearchRange Is Nothing Then MsgBox ans & "  Not Found": Exit Sub
SearchRange.Offset(, 1).Value = MyName.Value
SearchRange.Offset(, 2).Value = City.Value

End Sub
 
Upvote 0
My Aswer Is This,

How should your code be changed so that when you enter an ID to fill in automatically, name and city, and no longer display the message "Do you want to download Changes", unless something changes in one or more from the textbox.
Of course if ID not exists, textbox with name and city will remain empty and wait to manual fill.

Thank you.
 
Upvote 0
Tom: I would really like to help you. It wouild be best for you to start a new posting and explain in detail what you have and what you want. Thanks. I will see your posting and try to help you.
My Aswer Is This,

How should your code be changed so that when you enter an ID to fill in automatically, name and city, and no longer display the message "Do you want to download Changes", unless something changes in one or more from the textbox.
Of course if ID not exists, textbox with name and city will remain empty and wait to manual fill.

Thank you.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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