UPdate value on sheet cells from textbox on userform

[lumch]

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]

 

[Roderick_E]

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.

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]

 

[Norie]

How are you populating the combobox that's being used to populate the textboxes?

 

[lumch]

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.

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

 

[Roderick_E]

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:

insidcol = "H:H"

You say 4 columns???

 

[lumch]

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:

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

 

[Roderick_E]

Which column is the insurance id# in?

 

[lumch]

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

 

[lumch]

Also quick question: Where do I need to put the code? on another command button or inside the one already exists ? thanks

 

[Roderick_E]

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.

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