Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

ComboBox Selection Uses Data from One Sheet to Populate Cells on Another Sheet

0

I have been searching the interwebs all day long looking for a solution.  I really hope someone can assist me. 

I am creating a Change Order Log for my company.  What I am currently attempting to accomplish is when an employee uses the UserForm to create a new change order, the Trade Name that is selected from the Combobox on the userform will populate multiple cells on another worksheet. 

For example:

The Combbox is populated by Column A in Sheet1 (trade names)

When a trade is selected and the other change order fields are completed, the employee clicks the "Create CO" button. 

The information from the userform then populates sheet "Change Order Template".  Cell B7 is populated with the trade name selected in the Combobox. 

I need cell B8 from sheet "Change Order Template" to populate with the contact name which is located in Column B of Sheet1 based on the selection from Column A from the Combobox.

This may be extremely confusing - I can clarify if need be. 

Answer
Discuss

Answers

0
Selected Answer

To transfer a value from the user form to a worksheet is easy. Here is the syntax.

Worksheets("Change Order Template").Cells(7, 2).Value = ComboBox1.Value

Using the 'Row, Column, form of addressing a cell makes it easier to assign row and column numbers to variables which you can manipulate. If such is not your need, you may prefer to address a cell using worksheet notations. For example, the following code does the same job as the one above.

Worksheets("Change Order Template").Range("B7").Value = ComboBox1.Value

In order to write from one worksheet to another syntax like the following is needed.

Worksheets("Change Order Template").Cells(Rt, Ct).Value = Worksheets("Sheet1").Cells((Rs, Cs). Value

I use 't' for Target and 's' for Source, 'R' for Row and 'C' for Column. Later you can construct loops or even specify ranges, but doing one cell is what explains the principle.

Now, your values of 'Rt' and 'Ct' are not variable. I would declare them as enumerations. You may prefer to address them as ranges named by their worksheet notations. No difficulty here, either way. 'Cs' is known as well, and not variable. I would use enumerations again, but if you want to use worksheet notations the construction of cell addresses becomes very convoluted.

The only problem then is 'Rs'. 'Rs' is the row in Sheet1, I presume, of which the value from column A was selected from the Combobox by the user. There is a relationship between the worksheet row and 'Combobox1.Listindex'. The Listindex property is -1 when nothing is selected, 0 for the frist item, 1 for the second item etc. up to .ListCount -1 which is the last item. Your last item is from cell A100, which is probably blank, which may cause a problem. You may wish to specify the source range more precisely.

However, when .Listindex = 0, .Value = Range("A2").Value and 'Rs' would be 2. Therefore Rs = Combobox1.ListIndex + 2.

Problem solved?

Discuss

Discussion

This does not quite accomplish what I am looking to do. 

"Sheet1" is an editable contact list. 
Column A contains trades
Column B contains contact name
Column C contains email address, etc. 

"Change Order Template" will collect data based on entries placed on the Userform. 

Userform is how employees create a new Change Order.  So when the Userform opens, they have to fill out the CO# in a textbox.  Then from a ComboBox (TradeList1), they will select their trade.  The information comes from "Sheet1" Column A.  This part I have successfully accomplished by putting the following into the Combbox Properties> RowSource: 

Sheet1!$A$2:$A$100


What I need to happen is have the corresponing information from Columns B, C, D, etc. on "Sheet1" to fill specific cells on "Change Order Template".

Perhaps I should not be utilizing the ComBox Properties?

Also, let me clarify that the Column A information is the only thing that appears in the ComboBox.  I want the information from Columns B, C, D etc to populate "Change Order Template" without being seen on the Userform. 
Keady87275 (rep: 2) May 3, '17 at 8:20 pm
Understood. Please read the modifications I have made to my answer above.
Variatus (rep: 4889) May 3, '17 at 9:15 pm
BTW, I suggest that you give a proper (descriptive) name to 'Sheet2'. This is because 'Sheet2' is both Name and CodeName, meaning you can address a cell in it as "Worksheets("Sheet2").Cells(1, 1)" or "Sheet2.Cells(1, 1)". Having no difference between the two and both being generic is a sure source of grey hair at some point in the future.
Variatus (rep: 4889) May 3, '17 at 9:21 pm
As an alternative to the above solution, you could load Sheet1!A2:C100 into the Combobox, hide all columns except the first, and feed your order change template directly from the combobox. This would probably be the more elegant solution but it will require more coding, especially in regard to how you load the combobox.
Variatus (rep: 4889) May 3, '17 at 9:43 pm
I think I was over complicating everything. 
In my Userform, I have added multiple TextBox in order to populate the information that I want displayed on the other sheet.  When the item from Column A is selected in the ComboBox, the data from Columns B, C, D, etc populate into specific text boxes on my UserForm.  
From there, when an employee pushes a button to create the new Change Order it populates the cells with the details from the TextBoxes. 

This is not the way that I was originally attempting to complete the task however it is an alternative solution. 

I appreciate all of your assistance with this!  Your suggestions definitely brought more ideas to mind. 
Keady87275 (rep: 2) May 3, '17 at 10:54 pm
Add to Discussion
0

I think I was over complicating everything. 
In my Userform, I have added multiple TextBox in order to populate the information that I want displayed on the other sheet.  When the item from Column A is selected in the ComboBox, the data from Columns B, C, D, etc populate into specific text boxes on my UserForm.  
From there, when an employee pushes a button to create the new Change Order it populates the cells with the details from the TextBoxes. 

This is not the way that I was originally attempting to complete the task however it is an alternative solution. 

I appreciate all of your assistance with this!  Your suggestions definitely brought more ideas to mind. 

Discuss

Discussion

I'm glad I could be of assistance. If you consider your question answered please accept the reply as solution. If there are loose ends to tie up, don't hesitate to ask. If you get stuck again be assured your next question will slso find an answer here.
Variatus (rep: 4889) May 4, '17 at 4:50 am
Keady this is the Answer section. Next time post this type of comment in the discussion section. This helps people coming here in the future to quickly find the correct answer and also follow the discussion.

Only post an Answer to your question when it answers your question.
don (rep: 1989) May 4, '17 at 10:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login