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]

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

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

 

[lezawang]

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.

 

[My Aswer Is This]

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

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

 

[lezawang]

Yes that is great. That is what I was hoping/thinking as well. You read my mind thank you very much once again

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Yes that is great. That is what I was hoping/thinking as well. You read my mind thank you very much once again

 

[My Aswer Is This]

No if you want to do all three with the same button.
Look at how this works.

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

 

[Tom.Jones]

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.

 

[My Aswer Is This]

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.

 

[Tom.Jones]

Thank you for your generosity.