Excel ActiveX Listbox not enabled on file open

Question

I am trying to figure out a bizarre situation.

I have a workbook with many sheets. On one sheet, I have one ActiveX listbox (CTOverview.Listbox1). On a second sheet, I have a total of three listboxes (CTSelected.Listbox1 thru Listbox3). I am using a query to populate Listbox1 on both sheets with the same data. The code for this is below:

strSQL = "Select Distinct [Region] From [UniqueCTList$] Order by [Region]"
closeRS
OpenDB

' initialize listboxes
CTSelect.ListBox1.Clear
CTSelect.ListBox2.Clear
CTSelect.ListBox3.Clear
CTOverview.ListBox1.Clear

' initialize with entire division value
CTSelect.ListBox1.AddItem "Entire Division"
CTOverview.ListBox1.AddItem "Entire Division"

' initialize selected Tech
CTData.Range("CT_Selected") = ""

' populate listboxes using recordset
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then
    Do While Not rs.EOF
        CTOverview.ListBox1.AddItem rs.Fields(0)
        CTSelect.ListBox1.AddItem rs.Fields(0)
        rs.MoveNext
    Loop
Else
    MsgBox "I was not able to find any unique Regions.", vbCritical + vbOKOnly
    Exit Sub
End If

This code works beautifully (not my code - I got it from the intertubes). Both LIstbox1's on both sheets load with the list of distinct regions. However, I cannot get CTOverview.Listbox1 to respond to any input until I've selected something from CTSelected.Listbox1. Once I do that, both Listbox1's work normally and are not otherwise connected, at least as far as I can tell.

I have tried closing the recordset after I've populated the two Listbox1's (no effect). I have tried selecting a default item in CTOverview.Listbox1 (no effect).

Just in case it matters, here is my code to open/close the recordset:

Public Sub OpenDB()
    If cnn.State = adStateOpen Then cnn.Close

    cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; _
        DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    cnn.Open
End Sub


Public Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
End Sub

As far as I can tell, I have to take focus off of the CTOverview sheet. If I click onto any other sheet, then click back to CTOverview, Listbox1 seems to work. This is a problem because CTOverview is supposed to be my launch sheet.

Any idea why this might be happening? I'm pulling out my hair trying to figure this out. Any insights would be much appreciated.

Rochellrochella

Are you populating the listbox in the worbook_open event? – Siegel

yes, I am. the code above runs on open. – Rochellrochella

Hmmm, I thought so. I had answered a similar question (I am not sure whether it was in SO or some other forum). I don't even remember what I suggested but it worked.... Damn! Anyways. Can you try this for me. After you have populated the listbox, right at the end (just before end sub) activate sheet2 and in the next line activate the launch sheet. Use Application.Screenupdating =false to ensure that there is no screen flickering. Now test it. – Siegel

Hot Damn that worked. It's kludgy as all hell but hey, it's working. THANK YOU SO MUCH! – Rochellrochella

Answer 1

Siddharth Rout provided the answer in a comment. After you have populated the listbox, right at the end (just before end sub) activate sheet2 and in the next line activate the launch sheet. Use Application.Screenupdating =false to ensure that there is no screen flickering.

Elbert