Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox Challenged (XP; SP3)

    I have an issue with code associated with the change of a selection in the ComboBox. The code is simplistic in nature but I can not determine why it is repeating the entry. It has got me puzzled. Overall the selected item from the combobox will put the email address in a laundry list within Col A on Sheet "List".

    The code is as follows:
    Private Sub ComboBox1_Change()
    MsgBox "Changed"
    Set af = Application.WorksheetFunction
    oLastRow = Worksheets("Main").Range("A65536").End(xlUp).Row + 1
    oName = Sheets("List").Range("D1")

    Sheets("Main").Range("A" & oLastRow) = af.Index(Sheets("List").Range("R_ListUsersRange"), _
    af.Match(oName, Sheets("List").Range("R_ListUsers"), 0), 3)
    End Sub

    If someone would take a look at it I would appreciate it.

    Thanks,
    John

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

    Re: ComboBox Challenged (XP; SP3)

    Somehow, the dynamic nature of the named ranges causes the On Change event of the combo box to repeat. I guess that the change to the linked cell forces Excel to recalculate the dynamic ranges, and this is a "change" to the combo box. If you changew the list fill range of the combo box to a fixed range, the problem doesn't occur.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Challenged (XP; SP3)

    Hans,

    Thanks for the response. I orginally thought the spreadsheets was corrupted and rebuilt it from scratch numerous times with the same end result.

    Who would have thought the dynamic nature of the range would be the cause.

    Regards,
    John

Posting Permissions

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