UPdate value on sheet cells from textbox on userform

lumch

Board Regular
Joined
Jan 29, 2010
Messages
204
Hello, I have this code that transfers data to a sheet and creates a data base of : Name, DOB, Phone number, Insurance name, Insurance ID.



Dim strDataRange As Range
Dim keyRange As Range
Set strDataRange = Range("A1:h5000")
Set keyRange = Range("A1:h5000")
strDataRange.Sort Key1:=keyRange, Header:=xlYes
Dim tr As Worksheet
Set tr = Worksheets("Sheet16")
iRow = tr.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
tr.Cells(iRow, 1).Value = Me.TextBox1.Value
tr.Cells(iRow, 2).Value = Me.TextBox2.Value
tr.Cells(iRow, 3).Value = Me.TextBox22.Value
tr.Cells(iRow, 4).Value = Me.TextBox23.Value
tr.Cells(iRow, 5).Value = Me.TextBox17.Value
tr.Cells(iRow, 6).Value = Me.ComboBox15.Value
tr.Cells(iRow, 7).Value = Me.TextBox21.Value
tr.Cells(iRow, 8).Value = Me.TextBox32.Value


Then on this code, I am able to call all that information enter before (above code) and its populates on textboxes:

Private Sub ComboBox13_Change()
On Error Resume Next
Me.TextBox1.Value = Me.ComboBox13.Column(0)
Me.TextBox2.Value = Me.ComboBox13.Column(1)
Me.TextBox22.Value = Me.ComboBox13.Column(2)
Me.TextBox23.Value = Me.ComboBox13.Column(3)
Me.TextBox17.Value = Me.ComboBox13.Column(4)
Me.ComboBox15.Value = Me.ComboBox13.Column(5)
Me.TextBox21.Value = Me.ComboBox13.Column(6)
Me.TextBox32.Value = Me.ComboBox13.Column(7)
On Error GoTo 0
End Sub

With ComboBox13
.ColumnCount = 1
.ColumnWidths = "120"
.ColumnHeads = False
.RowSource = "Sheet16!A2:h5200"
End With

What I can't do or I need to do is, If the data change on any of these textboxes and I need to update the information such as : phone number or Insurance Id. How can I change it on those textboxes and press a command button to update that new data enter?

Thanks a lot
[FONT=Helvetica, Arial, Verdana, sans-serif]I have submitted this before , However I think I did not explain well[/FONT]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
So the Insurance ID is probably unique. There are a couple ways you can go about this but the most straight forward is to modify each textbox's "change" code so updates based on finding the insurance ID on the worksheet.

Code:
Dim insidcol as string
insidcol = "B:B" 'or whatever column it is.
[COLOR=#252C2F][FONT=Helvetica]Set tr = Worksheets("Sheet16")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]iRow = application.match([/FONT][/COLOR][COLOR=#252C2F][FONT=Helvetica]Me.TextBox32.Value,[/FONT][/COLOR][COLOR=#252C2F][FONT=Helvetica]tr.range(insidcol),0)
'----now you have the row you need to update
[/FONT][/COLOR]Me.TextBox1.Value = tr.Cells(iRow, 1).Value
[COLOR=#252C2F][FONT=Helvetica]'...
'...
'...
[/FONT][/COLOR]
 
Upvote 0
How are you populating the combobox that's being used to populate the textboxes?
 
Upvote 0
So the Insurance ID is probably unique. There are a couple ways you can go about this but the most straight forward is to modify each textbox's "change" code so updates based on finding the insurance ID on the worksheet.

Code:
Dim insidcol as string
insidcol = "B:B" 'or whatever column it is.
[COLOR=#252C2F][FONT=Helvetica]Set tr = Worksheets("Sheet16")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]iRow = application.match([/FONT][/COLOR][COLOR=#252C2F][FONT=Helvetica]Me.TextBox32.Value,[/FONT][/COLOR][COLOR=#252C2F][FONT=Helvetica]tr.range(insidcol),0)
'----now you have the row you need to update
[/FONT][/COLOR]Me.TextBox1.Value = tr.Cells(iRow, 1).Value
[COLOR=#252C2F][FONT=Helvetica]'...
'...
'...
[/FONT][/COLOR]


Thanks for the response, So Sheet16 has in 4 columns in use "Name, DOB, phone, and insurance number", in case I need to update any of these ones, the above code will work?

thanks
 
Upvote 0
My code snippet was just an example of how you could go about this. From your posting, the insurance id column appears to be column H.
So my code would actually be:
Code:
insidcol = "H:H"
You say 4 columns???
 
Upvote 0
My code snippet was just an example of how you could go about this. From your posting, the insurance id column appears to be column H.
So my code would actually be:
Code:
insidcol = "H:H"
You say 4 columns???


Yes, Some times one of the RMA, enter wrong or misspell name, wrong DOB or Missing a number on the insurance id, or change on phone number, If is the case that I need to put a command button to update them individually, I can do that, If you tell me how, Thanks
 
Upvote 0
Which column is the insurance id# in?


The insurance is in the fourth column , but I would like to be able to change data (if needed) no only insurance info also Name (if misspelled ) or DOB, or phone numbers (this is one get update it all the time)

Thanks a lot
 
Upvote 0
Also quick question: Where do I need to put the code? on another command button or inside the one already exists ? thanks
 
Upvote 0
The insurance is in the fourth column , but I would like to be able to change data (if needed) no only insurance info also Name (if misspelled ) or DOB, or phone numbers (this is one get update it all the time)

Thanks a lot
Understood, but we need to use the insurance id column as a unique id to make sure we update the data for that row.

Code:
Dim insidcol as string
insidcol = "D:D" 'or whatever column it is.
Set tr = Worksheets("Sheet16")
iRow = application.match(Me.TextBox32.Value,tr.range(insidcol),0)
'----now you have the row you need to update
Me.TextBox1.Value = tr.Cells(iRow, 1).Value
'...
'...
'...
The idea would be that the user would input an insurance ID somewhere on the userform, press a button and it would populate your userform with all the current data within the Excel sheet for that ID. Then the user could correct any data that needs corrected and press another button to save it over the data in the spreadsheet.
See example of this: http://rodericke.com/estuff
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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