ListBox Change Selection (VBA)

[gravanoc]

This seems like it is frustratingly simple, but I've had no luck with getting my ListBox to deselect or even change its current selection.
My ListBox is populated with all the tabs in the workbook (there are 36 tabs with very long names), so when they click the tab name in the ListBox it immediately transports them to that tab. That works fine. The problem is that if they return to the page with the ListBox the same tab name remains selected and cannot be selected again unless something else is selected first. This adds an unnecessary extra step into the process.

Two possible workaround solutions would be to either set the ListBox to have no selection, meaning nothing is highlighted, or to change the index to zero, which is the tab the ListBox is on.
Here is my current code, although I've tried several variations of it. Everything works EXCEPT for changing the selection.

Option Explicit

Private Sub ListBox1_Click()
    
    Dim wb As Workbook
    Dim MATTOC() As Worksheet
    Dim wsDest As Worksheet
    Dim wsCount As Integer
    Dim wsName As String
    
    Dim i As Integer


    Set wb = ThisWorkbook


    wsCount = Worksheets.Count
    ReDim MATTOC(1 To wsCount)
    For i = 1 To wsCount


        Set MATTOC(i) = Worksheets(i)


    Next


    wsName = MATTOC(4).Range("ToCChange").Value
    Set wsDest = Worksheets(wsName)
    ListBox1.ListIndex = 0
    wsDest.Activate
    wsDest.Select

End Sub

 

[Akuini]

Have you tried: ListBox1.ListIndex = -1

 

[gravanoc]

I just tried that, but no luck.

 

[mikerickson]

Have you tried writing code to the Workbook_SheetActivate and Workbook_SheetDeActivate events in the ThisWorkbook code module. Changing the List box to reflect the user's manual changing of worksheets?

Sych things so that the ActiveSheet is always selected in the Listbox, no matter how it gets there?

 

[Akuini]

Don't put it in Sub ListBox1_Click
Try placing it in:

Private Sub ListBox1_LostFocus()
ListBox1.ListIndex = -1
End Sub

 

[Rick Rothstein]

Instead of making your users go back to a specific worksheet to find the ListBox in order to go to a different sheet, you might want to consider using the UserForm method I posted in my mini-blog article here...

http://www.excelfox.com/forum/showthread.php/1830-A-Neat-quot-Go-To-Sheet-quot-Selector