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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With