Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combobox Change event (2000)

    I have a combobox (from Control Toolbox) with the following code attached. For some reason the sub is being triggered on every Worksheet_Change event rather than just on the cbo change.

    Then when I do make a change to the cbo it cycles thru once correctly, followed by a 2nd cycle that hangs on line4.

    Where is the error?

    Note: the cbo LinkedCell is [NewAlert]




    Private Sub cboAddToAlertList_Change()

    Dim NextResID As Range
    Application.EnableEvents = False
    'Determine where to insert new data
    Set NextResID = Range("b65000").End(xlUp).Offset(1, 0)
    Line4:
    [NewAlert].Copy NextResID
    NextResID.Offset(0, 1) = Application.Index([ResID], [NewAlert]).Offset(0, 1)
    Set NextResID = Nothing
    Application.EnableEvents = True

    End Sub

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

    Re: Combobox Change event (2000)

    In a test workbook I created, the Worksheet_Change event does not trigger the cboAddToAlertList_Change event, so the latter code is executed just once when I select something in the combo box. (I assumed that ResID is another named range.)

    What other code do you have behind the worksheet?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox Change event (2000)

    Hans,
    There is NO OTHER event code ; neither in ThisWorkbook nor any of the sheets.
    I tried using the code in a new workbook, as you have and it seems to work.
    I tried deleting the cbo and making a new one with the same properties - still doesn't work
    There are no hidden sheets.

    there are only 2 reg module subs, neither connected to this event.

    ???????????????

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combobox Change event (2000)

    If it works as expected in a new workbook and Hans confirms that it works as expected, but it works "unexpectantly" in the workbook, that it is in, I suspect that there must be something in this workbook that is causing it to behave this way.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox Change event (2000)

    I also tried placing the code in a reg module, exporting the new module, deleting the old code, them importing it back. the problem still exists:
    every change in the sheet (data entry as well as deleting a cell) will trigger the cbo_change_event code.

    Have I somehow corrupted the entire file?
    I am able to make it work in a new workbook.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combobox Change event (2000)

    Does any of the other code change the "linked cell" to the combo box? This will trigger the cboChange event since the cbo is being changed.

    Steve

Posting Permissions

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