Edit cell as populate in userform

lumch

Board Regular
Joined
Jan 29, 2010
Messages
204
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
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:
Code:
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

Board Regular
Joined
Jan 29, 2010
Messages
204
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:
Code:
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
 
Last edited:

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
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

Board Regular
Joined
Jan 29, 2010
Messages
204

ADVERTISEMENT

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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
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

Board Regular
Joined
Jan 29, 2010
Messages
204
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

Board Regular
Joined
Jan 29, 2010
Messages
204
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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
Top