interactive userform (read/write)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
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
 

lezawang

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
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:

lezawang

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
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
 

Tom.Jones

Board Regular
Joined
Sep 20, 2011
Messages
230
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
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:

Forum statistics

Threads
1,085,429
Messages
5,383,624
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top