Options disapear when clicked in list box

[czag10]

When I run the following code the values in the called sheet populate into the list box. However, when I try to click them on the user form sheet, the values disappear and I get a blank box. Can anybody help? Thanks!

Private Sub Product_Change()
Application.EnableEvents = False
   Product.Clear
    Application.EnableEvents = True
    
    Select Case BusinessChannel.Value
        Case "Stores"
           Product.List = Sheets(4).Range("C1:C3477").Value
        Case "Dealers"
             Product.List = Sheets(5).Range("C1:C34").Value
        Case "National Accounts"
            Product.AddItem "Please enter product in adjacent cell."
            End Select
End Sub

 

[Jerry Sullivan]

Hi and Welcome to the Board,

From your description, it sounds like you have a ListBox that's been named "Product" and it is on a UserForm (not embedded in a Worksheet).
Is that correct?

If so, the problem is that this Product_Change will run anytime an item is selected from the Listbox. At the start of the code, the Product.Clear statement clears the list which causes the event to trigger itself.

It looks like you've tried to prevent the code from triggering itself by using
Application.EnableEvents = False
...however that has no effect on events of Form Controls.

I'd suggest you take the code that populates the ListBox out of this this procedure and move it to the UserForm_Initialize event or to a separate Sub that can be called whenever the Listbox needs to be re-populated.