A little trouble when using ListFillRange Property

[HDRules]

I haved a spreadsheet that will be used for performing various calculations for different tests. Each test is on a separate sheet, with sheet 1 being used to select which test the user wants to perform. Since additional tests are being added as they get qualified, I wanted to automate the listbox so when the workbook was opened each sheet name would be added to column a, a sort would be performed on that column, and then the ListFillRange would be updated for the list box. However, it appears that the list box gets populated prior to the sort completing, and only after clicking on each entry in the list box does everything look right. This will obviously not be acceptable for the end user. How do I make sure the list does not get refreshed until after the sort is complete? Below is the code I used to do this.

Private Sub Workbook_Activate()
Dim i As Integer
i = 1
For Each ws In Worksheets
    If ws.Name Like "OST*" Then
        Worksheets("TestSelect").Range("A" & i).Value = ws.Name
        i = i + 1
    End If
Next ws
i = i - 1
Worksheets("TestSelect").Range("A1").Sort Key1:=Worksheets("TestSelect").Columns("A")
Worksheets("TestSelect").SelectedTest.ListFillRange = "a1:a" & i
End Sub

Note, this does not always create the problem, but enough times to aggravate me. Any help would be appreciated.

 

[SkipVought]

Hi,

Here's one way

Private Sub Workbook_Activate()
    Dim i As Integer, ws As Worksheet
    
    i = 1
    With Worksheets("TestSelect")
        .[A1].CurrentRegion.ClearContents   'clear the listfillrange
        
        For Each ws In Worksheets
            If ws.Name Like "OST*" Then
                .Range("A" & i).Value = ws.Name
                i = i + 1
            End If
        Next ws
        
        .[A1].Sort Key1:=.Columns("A")
         SelectedTest.ListFillRange = .[A1].CurrentRegion.Address
   End With
End Sub

Another way is to use a DYNAMIC named range via Insert/Name/Define.
[tt]
Names in workbook: TestSheetList
Refers to: =OFFSET(TestSelect!$A$1,0,0,COUNTA(TestSelect!$A:$A),1)(
[/tt]
Then in your Combobox, reference the ListFillRange
[tt]
TestSheetList
[/tt]


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[HDRules]

Thanks alot. Your code is much more streamlined than mine, but alas, still seems to have problems. Now, the listbox appears blank at the end of the code, but scrolling up and down makes them appear. Is there someway to force a refresh or something to fix this?

 

[HDRules]

Well, I deleted the old listbox control, created a form with a listbox, and then initialized it to have a row source determined by your currentregion.address. Now it works like a champ, no bugs that I can find. For some reason a stand alone listbox does not display like on in a user form.

Thanks again Skip!