Need Help With Combo Box (2000 9.0.3821 SR-1)

[wallygator]

I need to make an update to a spreadsheet, but I am not the Excel programming wiz. Basically, I need to add a combo box, and when the combo box value changes, populate fields in Sheet1 with values in Sheet2 depending on what the combo box value is.

I am sure this is an easy thing to do for those in the know. I appreciate your assistance!

Wally

[HansV]

Wally,

Welcome to Woody's Lounge.

Could you be a bit more specific? Perhaps you could descibe in more detail how you want to "populate fields in Sheet1 with values in Sheet2", or attach a small spreadsheet illustrating what you want to happen. Thanks.

[waveylines]

A combo box selects an entry from a re-defined range. So let's say you have set up a range of values in cells in Sheet 2 in cells B1:B5.
Now Click on <View> <Toolbars> <Forms> and select Combo box icon by dragging it onto your spreadsheet.
Next 'right-click' on the combo box and then click on <Format Control>
Enter the Input range (In this example it would be Sheet2!B1:B5) by clicking on the icon on the right of the input box; and sect the range that you need.
The cell link would be the cell in worksheet 1 that you wish to change.
Then you can make any other cells in the worksheet change, by using the "If" formula or the "lookup" formulas to link to the cell link

[wallygator]

User selects from combo box on Sheet1, data is obtained from Sheet2 accordingly and placed into the cells in Sheet1. User may then modify the data. This attached example should help to further explain what I need to do. I can't use the IF and LOOKUP functions in the cell as the user is able to change the values to tailor to his situation.

Wally

[waveylines]

What you seem to be asking for is an iterative calculation.

Select Combo - Right Click - Format Control - Set cell link to equal $A$3
In cell B6 type the formula "=OFFSET(Sheet2!A2,A3,1,1,1)"
In cell B7 type the formula "=OFFSET(Sheet2!B2,A3,1,1,1)"

Cells B6 & B7 are now formulae. If you want a user to be able to change these details it is easier to allow them to change the cells on worksheet 2. Perhaps protect the rest of Worksheet 2, if you need to

[wallygator]

The actual data sheet is quite large and is protected and hidden. The goal is to provide data that will work 80% of the time. For the other 20%, the user can change the data that is provided. I am hoping that I there is a VBA solution that can be applied when the value of the combo box changes, but I don't the mechanics of how to do this or the code that would be entered. Thanks.

Wally

[HansV]

Right-click the combo box and select Format Control. Set the Cell Link to B4, then click OK.
Right-click the combo box again and select Assign Macro. Click New and make the code look like this:

Sub DropDown1_Change()
Worksheets("Sheet1").Range("B6") = _
Worksheets("Sheet2").Cells(Worksheets("Sheet1").Ra nge("B4") + 2, 2)
Worksheets("Sheet1").Range("B7") = _
Worksheets("Sheet2").Cells(Worksheets("Sheet1").Ra nge("B4") + 2, 3)
End Sub

This code perfroms the VBA equivalent of the OFFSET formulas proposed by Steve, but enters the result in B6 and B7 as values that can be edited.

See attached version.

[wallygator]

I have the spreadsheet working. Thanks to everyone for all the assistance!!!

Wally