Edit cell as populate in userform

[lumch]

Hello, I have this code (attached bellow), so I enter data in userform text, that is transfer to a excel sheet database, then when I enter in combobox data its populate according to data on database ( Sheet16 ) all names, phone numbers, insurance info, Sometimes I need to update information on data base. I have to close excel open the "view code", so is a pain, My question is How can I edit information from Userform that will change it on database (sheet 16). Thanks a lot.


Private Sub CommandButton2_Click()
On Error Resume Next
Dim iRow As Long
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
iRow = WS.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row



WS.Cells(5, 2).Value = Me.TextBox1.Value
WS.Cells(5, 8).Value = Me.TextBox2.Value
WS.Cells(9, 2).Value = Me.ComboBox15.Value
WS.Cells(13, 5).Value = Me.TextBox5.Value
WS.Cells(13, 9).Value = Me.TextBox6.Value
WS.Cells(15, 9).Value = Me.TextBox7.Value
WS.Cells(15, 2).Value = Me.TextBox8.Value
WS.Cells(7, 8).Value = Me.TextBox17.Value
WS.Cells(27, 2).Value = Me.ComboBox16.Value
WS.Cells(13, 2).Value = Me.TextBox19.Value
WS.Cells(5, 9).Value = Me.TextBox22.Value
WS.Cells(8, 8).Value = Me.TextBox21.Value
WS.Cells(5, 10).Value = Me.TextBox23.Value
WS.Cells(21, 2).Value = Me.ComboBox3.Value
WS.Cells(23, 7).Value = Me.ComboBox4.Value
WS.Cells(25, 2).Value = Me.ComboBox5.Value
WS.Cells(1, 1).Value = Me.TextBox29.Value
WS.Cells(7, 2).Value = Me.TextBox32.Value




On Error Resume Next


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




Dim fromRow As Integer
Dim toRow As Integer
fromRow = 1
toRow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Rows(fromRow & ":" & toRow).Sort Key1: ActiveSheet.Range ("A:G"), _
Order1:=x1Ascending, Header:=x1Yes, OrderCustom:=1, _
MatchCase:=False, Orientation:=x1TopBottom


Worksheets("Sheet16").Range("A1:G50000").RemoveDuplicates Columns:=Array(1, 7), Header:=xlYes




---------------------------------------------------------------------------------------------------
THIS IS THE COMBO BOX THAT PULLS DATA FROM SHEET 16 AND POPULATES TO OTHER TEXTBOX, THANKS





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

 

[AFPathfinder]

It would just be the reverse of how you load the controls. You posted syntax that changes values on sheet 16 from your control values:

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

You just need to program the event that triggers the update.

 

[lumch]

It would just be the reverse of how you load the controls. You posted syntax that changes values on sheet 16 from your control values:

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

You just need to program the event that triggers the update.

------------------------------------------------------------------------------------

I am sorry I'm kind of new on this. How can I do do modified the data enter, or better update when I need to change it?, I can even add a save command button after update information and it get change on sheet16?

thanks

 

[AFPathfinder]

Wouldn't you just change the textbox values? Once you're done changing the values, you can have a command button to transition those changes back to the sheet.

 

[lumch]

Update cell data

Hello, I have a excel database of patients, also a data base of specialists, In a user form I have 2 combo boxes , one is for the patients demographics and the other is for specialist, every time I enter a patient;s info is saved on the patients demographics sheet, and every time I enter a specialist info is saved in the specialist sheet, When I need to pull a patient information I just enter like the 3 first letter and pulls that info into text-boxes into my user forms, same for the specialist info. The problem I have is that very often patient or specialist change the info, i.e: patients phone number, or specialist location, or fax number, How can I change/modify/update that information in database, from the text-boxes, I'm trying to find a way to change the info when pull up and realized need an update I want to change it in the user-form and maybe with a save/update button , change that info in the cells saved on the sheets, thanks a lot

 

[Macropod]

Re: Update cell data

lumch: as you latest post is clearly related to the topic you already had under discussion, I've merged the threads. Please keep all related content in the same thread.

 

[lumch]

Re: Update cell data

lumch: as you latest post is clearly related to the topic you already had under discussion, I've merged the threads. Please keep all related content in the same thread.

OK thanks

 

[lumch]

Wouldn't you just change the textbox values? Once you're done changing the values, you can have a command button to transition those changes back to the sheet.

It doesn't for some reason it wont change the info into those cells where the data was entered...

If I change the data on the text boxes and click on the command button that initially saved the info into cell, it will not even save it again, I will need something that updates that info for those specific patient's information. Can you help me with this please? thanks a lot.