Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Aloha folks,
    I'm having trouble with two activex control combo boxes on a spreadsheet, and I'm stuck on two issues. Please, any suggestions are most appreciated.

    Ideally, the user selects an item from the first box (named ComboProject) and the second box (ComboAddress) should display the addresses relative to that project. The user then selects an address in ComboAddress, and the spreadsheet has a bunch of lookup formulas that make calculations specific to that address. I decided to have ComboAddress's ListFillRange be a defined range name, and when the user selects an item in ComboProject, a macro re-defines the named range used in ComboAddress. This works, to a point.

    Issue 1: When a new project is selected in ComboProject, ComboAddress does not "refresh". It still displays the last selected address from the previous project. Once the user clicks the dropdown arrow for ComboAddress, the new address list appears. But ideally I want ComboAddress to show the first address in the re-defined list as soon as a new project is selected in ComboProject.

    Issue 2: When the file is closed, whichever project is selected determines ComboAddress's size and choices when the file is re-opened. Some projects have 20 or more addresses, some have only one. If the file is closed when a 1-address project is selected in ComboProject, then when the file is re-opened, ComboAddress will only have one row, even after other projects are selected. Furthermore, in this example, only the first address in any project can be selected, that is you can't scroll down to access additional addresses. Saving the file doesn't change this, only closing and re-opening the file does.

    I'm thinking that these are ActiveX control issues, which I'm unfamiliar with. I prefer activex controls mostly because they have more flexibility, especially in appearance. In the Excel Object Reference, I can't find any way to 'refresh' the ComboAddress box to (1) display the first new address and (2) resize itself to allow for all items in the re-defined range. Is there some way to achieve this, or am I better off trashing this setup and using an AddItem loop to build ComboAddress's ListFillRange instead of using a defined range? Or using form control objects instead of activex control objects?

    I'm hesitant to upload the file because it contains confidential client information. If you feel an example is necessary, let me know and I'll make a generic one.

    Mahalo for any help,
    John Jacobson

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    While in design mode, you can double-click the first combo box to create or edit its On Change event procedure. You can write code here to select an item in the second combo box.

    I think a userform (or Microsoft Access) are more suitable for this kind of thing than an Excel worksheet.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767511' date='25-Mar-2009 23:26']While in design mode, you can double-click the first combo box to create or edit its On Change event procedure. You can write code here to select an item in the second combo box.

    I think a userform (or Microsoft Access) is more suitable for this kind of thing than an Excel worksheet.[/quote]

    Thanks Hans,
    For your feedback. Unfortunately I don't have the development time available to go another route. So for now, I think I'll just use a single combo box to display both project and address. Then both issues go away.
    Aloha, John Jacobson

Posting Permissions

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