Results 1 to 6 of 6
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box - Macro (XP)

    Hi, I have a series of combo boxes and I have put some code in the _CHANGE event. The list of items in the combobox is a dynamic range name. It seems that the macro gets fired 6 or 7 times each time I change the value and this repeated firing is messing up other things. Is there a better event to use - say _LOSTFOCUS?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Combo Box - Macro (XP)

    Also see <post#=526,947>post 526,947</post: >.

    The On LostFocus event occurs only once, so you may need this workaround, but it isn't very user friendly, since the user has to click outside the combo box before something happens. Not using dynamic ranges is the only other workaround I know.

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Macro (XP)

    Further to the above post - the firing of the macro is one issue that I am running into. Another is that my drop down box is showing the wrong number of enteries.

    I've put together an example spreadsheet. Use the combobox at B12 to select which list to show in the combo box at B14. The values in the second (B14) combo box get updated but the number of items shown are not updated. To repeat the bug that I am running up against, select "List 2" in the first drop down box then save and close the workbook. Reopen the workbook and select "List 4" then try to select "West" from the second combo box (I only see the first two enteries as the number of enteries shown is stuck on 2).
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Macro (XP)

    Thanks Hans - would dynamic range names be causing the second issue too? If I don't go dynamic, then I suppose that I would have to go the comobox.clear then comobox.additem route - correct?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Combo Box - Macro (XP)

    Try this:

    Private Sub ComboBox1_Change()
    Me.ComboBox2.ListFillRange = "xFillRange"
    Me.ComboBox2 = ""
    End Sub

  6. #6
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box - Macro (XP)

    Beautiful! - Thx Hans.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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