List Box not showing the selection or scrolling until the focus has been removed

[erirog74]

I have been having some issues with the ActiveX List box. I have a list box that I have populated. When I try to click on a selection, nothing happens until I click on a cell to cause the list box to lose focus. One that happens, the selection is highlighted. Same with scrolling. I click on the scroll bar and nothing happens. When I click out of the list box, it scrolls down. How can I get the list box to highlight or scroll without having to click out of it to see where I am?

Thanks

 

[Yongle]

welcome to MrExcel

Please post
- any code associated with the listbox
- any sheet event code which is triggered in sheet containg that listbox
(worksheet_change, worksheet_selectionchange, worksheet_calculate)

Which other objects are on that sheet?

 

[erirog74]

I am running a SQL query to put data into a tab called Lists. Then reading that list to populate the List Box. This populates the listbox but When I click on an entry, it doesn't hightlight until I select another cell. Same thing with scrolling. I have attached a screenshot of my Parameters tab. I am basically running a scrap report and I want to be able to Include or Exclude reasons. I am loading all the reasons into the Include and will add some buttons to move certain entries to exclude if needed.

Sub LoadReasons()

Dim intCntr As Integer
Dim intList As Integer


Sheets("Lists").Select

With ActiveWorkbook.Connections("XXXXXXX").OLEDBConnection
.CommandText = "EXEC XXXXXXX"
ActiveWorkbook.Connections("XXXXXXX").Refresh
End With

DoEvents

intList = 0
intCntr = 2

Do Until Cells(intCntr, 1) = ""
Sheets("Parameters").lbxInclude.AddItem
Sheets("Parameters").lbxInclude.List(intList) = Cells(intCntr, 1)

intList = intList + 1
intCntr = intCntr + 1
Loop

Sheets("Parameters").Select
Range("A1").Select


End Sub

 

[Yongle]

I am unable to recreate your problem, so there are probably some other things going on in your workbook that are causing your issue.

I suggest you split that macro into 2 separate macros:

One to refresh the table

Sub RefreshTable()
    With ActiveWorkbook.Connections("XXXXXXX")
        .OLEDBConnection.CommandText = "EXEC XXXXXXX"
        .Connections("XXXXXXX").Refresh
    End With
End Sub

And one to populate the listbox when it is activated
- you must place code below in the SHEET code window for sheet "Parameters"

Private Sub lbxInclude_GotFocus()
    Dim intCntr As Integer, intList As Integer, lb As Object, Lists As Worksheet
    Set Lists = Sheets("Lists")
    Set lb = Me.lbxInclude
    lb.Clear
    intList = 0
    intCntr = 2
    Do Until Lists.Cells(intCntr, 1) = ""
        lb.AddItem
        lb.List(intList) = Lists.Cells(intCntr, 1)
        intList = intList + 1
        intCntr = intCntr + 1
    Loop
    lb.Selected(0) = True
End Sub

 

[erirog74]

I tried separating the code and was still unable to get it to work. I then created a new file since this one wasn't all that big. Took me a few minutes and it worked. Then it didn't. Turns out, when I put it on my laptop screen, it works. When I move it to my 2nd screen, it doesn't. Crazy, but, at least now I know it isn't my stuff. Thanks for the help.