Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Arlington, Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    New Lounger
    Join Date
    Mar 2004
    Location
    Arlington, Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Location
    Arlington, Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  8. #8
    New Lounger
    Join Date
    Mar 2004
    Location
    Arlington, Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    Wally

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •