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

    Events for Drop-down box Results (xl2000)

    On a worksheet I have a drop-down box for selecting options. It is linked to cell B1.
    I would like to have an Event take place each time the VALUE of B1 changes.
    How do I do this?

    When I tried the Worksheet_Selection_Change (assigning B1 to Target) that only triggered the event when SELECTING B1 cell.
    When I tried the Worksheet_Calculate that triggered every data entry in the sheet.

    Is there a work around for this?

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

    Re: Events for Drop-down box Results (xl2000)

    Instead of reacting to a change in the linked cell, react to a change in the value of the combo box.

    If you created the combo box from the Forms toolbar, right click the combo box, and select Assign Macro... Change the proposed macro name if you wish, then click New. The macro will be created in a standard module. You can refer to the value of the linked cell in the macro.

    If you created the combo box from the Toolbox, switch to design mode and double click the combo box. An On Change macro will be created in the worksheet module. In this macro, you can refer to the Value property of the combo box, as well as to the value of the linked cell.

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

    Re: Events for Drop-down box Results (xl2000)

    Thanks. That did the trick

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

    Re: Events for Drop-down box Results (xl2000)

    Well maybe I was premature in my OK response. It almost works:

    I actually have 2 combo boxes. The "FORM" style is fine, but the control box one has a curios side effect:
    It is linked to cell D5 (named JobCode)
    The combobox_change macro finds the next blank cell in a range and inserts the formula: =Value(JobCode)

    Now the curious part:
    If I INSERT or DELETE a row in the worksheet, the combobox_change macro gets triggered. What's the connection?

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

    Re: Events for Drop-down box Results (xl2000)

    Let me clarify just a little:

    The On Change event for the combobox gets triggered when I do either

    Rt-click>>Insert
    or
    Rt-click>>Delete
    on a Row number at the far left of the worksheet.

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

    Re: Events for Drop-down box Results (xl2000)

    Strange but true. I don't know what causes this, seems to be a bug. You could get around it this way:

    Private varValue As Variant

    Private Sub ComboBox1_Change()
    If ComboBox1.Value <> varValue Then
    varValue = ComboBox1.Value
    ' Your code here
    End If
    End Sub

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

    Re: Events for Drop-down box Results (xl2000)

    Still no luck.
    When I put your code into the worksheet module, the INSERT/DELETE still fired it off. I moved the code to a standard module and declared the Combobox as an object, but couldn't get it to be triggered by the C box change. I ran it manually and it worked.

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

    Re: Events for Drop-down box Results (xl2000)

    I use Excel 2002 (SP-2), perhaps it works differently.

    I have attached my test workbook. When I delete rows, the On Change event of the combo box fires, but it has no effect because of the extra check.

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

    Re: Events for Drop-down box Results (xl2000)

    I came up with a different solution:
    The only reason I was using the Toolbox type of control was that I wanted to show 2 colums in the drop-down list -- a Job Number and Job Title
    The Form type of drop down only shows one column but I worked around this by creating a 3rd col to concatenate 1& 2, then used the Form type of box to show this 3rd col. No problem with the macro now.
    Thanks

Posting Permissions

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