Updating cells and text box's with ComboBox

[styoda]

Hi,

Please help,

I have a number of text box's on a userform, there's a ComboBox with 1-10 in the list (combo_box1). When a number is selected I would like it to do the following:-

1. Update cells in sheet1
2. Update text box's on the same userform, but also, when changes are made in these text box's the cells in sheet1 are also updated (same cells as point 1)

So, lets say, when number "1" is selected in comobo_box1, I enter the details in the text box's on the form, this then copies the information from the text box's to the cells in sheet1. This is then repeated for the other 9 items in combo_box1.
However, if I go back to "1" in the combo_box1 and make changes to the text box's on the userform, this also needs to update the cells in sheet1.

Does this make sense and is this possible ?

I don't have any code at the moment, not sure if this is possible.


Thanks in advance

[Kenneth Hobson]

Use the TextBox's ControlSource property. If you post what does what we can show how. e.g.
1, Data!A1, TextBox1 - Data!A2, Textbox2, etc.

Here is the concept with a more complicated set of linked cells, controlsource.

Please Login or Register  to view this content.

[styoda]

Hi Kenneth,

Thanks very much for you help, I managed to manipulate your code and get it working for the first entry in the ComboBox1, but not the others.
However, I have come across a problem, I've named each textbox and there's also other combobox's, which has complicated things, and now nothing works, because of this. In hindsight I should have renamed each box, but this is only a small part of project and the other code in the project is just way to big to change.

I know naming the text/combo box's will increase my code somewhat, but if you would be so kind and help with just the first entry in ComboBox1 then I should be able to do the rest.

To make you aware:-
If ComboBox1 = 1 then
Email Account = A2
Customer Delivery Site = B2
Account Number = C2
etc
If ComboBox1 = 3 then
Email Account = A4
Customer Delivery Site = B4
Account Number = C4
etc

I've attached my workbook, can you please have a look as see what you can do ?


Thanks very much,

[Kenneth Hobson]

This sets your controlsource property based on the listindex of the combbox. This is the row number +1 to skip row 1 headings.

Here is the main trick. Add the column letter in the Tag Property for each control that you want to set a controlsource property. This basically links the control value to the cell value. Change one and it changes the other.

As coded, it assumes that the sheet is active when you show the userform.

Please Login or Register  to view this content.

Be sure to test on a backup copy.

[styoda]

Thanks very much Kenneth, it works perfectly.

[styoda]

Hi Kenneth,

I should have asked this earlier, can you please let me how I can run this code from another sheet (sheet2 for example)

Thanks

[Kenneth Hobson]

There are several ways to run it from other sheets.

For the cell references in the userform to a sheet other than activesheet, there are two fixes for that. (1) Add the worksheet object prefix:

Please Login or Register  to view this content.

and (2), add the sheet name in the Tag for each control or set it at run time like this with the prefix added:

Please Login or Register  to view this content.

[styoda]

Thanks very much once again Kenneth, works perfectly.