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

    combobox recycling event (2k)

    The following code executes twice.

    <pre>Private Sub ComboBox1_Change()
    ActiveCell = [LinkCell]
    ActiveCell.Offset(1, 0).Select

    'Clear the cbo so I can make the same selection again
    Application.EnableEvents = False
    ComboBox1 = ""
    Application.EnableEvents = True
    End Sub</pre>



    You can verify this in a wbk and note that the Offset occurs twice
    Turning off Enable Events doesn't seem to work as I would expect.
    I tried also changing the word Combobox1 to [LinkCell] but that caused the
    application to crash - not sure why??.

    Since this ""Double Action" of the cbo is an ongoing problem for me I'm
    wondering if someone can shed some lite as to why and how to correct it.
    I thought that Application.EnableEvents=False would prevent a repeat of the event
    or any event until reset. Obviously clearing the cbo triggers a Change Event
    but why isn't it disabled?

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

    Re: combobox recycling event (2k)

    I think EnableEvents applies only to worksheet and workbook level events. Try this, it uses a Boolean variable to keep track of whether you are changing the value of the combo box in code.

    Private Sub ComboBox1_Change()
    Static CallingMyself As Boolean
    If Not CallingMyself Then
    ActiveCell = Range("B1")
    ActiveCell.Offset(1, 0).Select
    CallingMyself = True
    'Clear the cbo so I can make the same selection again
    ComboBox1 = ""
    CallingMyself = False
    End If
    End Sub

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

    Re: combobox recycling event (2k)

    Thanks Hans

    That works

Posting Permissions

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