Fixed it myself. Found a work-around that is not pretty, but works reliably.

"Steve the large" wrote:

I have a listbox on a worksheet -a "splash sheet" which is activated in the
workbook_open event. I want to display a list of worksheets from which a
user can choose. It does not work as expected -it's blank at first. Details
& code below.

=details===========
The listbox is named "lstDashboards".
The splash sheet is named "Main Page"
Running Excel version 9.0.8961 SP-3 Windows 2000 version 5.00.2195 SP-4

In the workbook_open sub, I activate main page before I turn screenupdating
off. See below.

In main page's worksheet_activate event, I clear and load lstDashboards. I
load the list box with a set of worksheets that meet certain criteria so that
the user can select from this list and be taken directly to a sheet displayed
on the list.

I open the workbook by double clicking on it in windows explorer.

=what I see=================
The splash sheet displays, the old list is cleared.

About 20 secs go by while other stuff is being setup. The mouse icon turns
into a sand-timer.

The sand-timer turns back into a pointer.

The list is not updated. It is blank. If I select another sheet by it's
tab, and then select main page again, the list is appropriately updated.

I don't know why it doesn't work on the workbook_open event.

Any suggestions?


=code=============
Thisworkbook code:
Private Sub Workbook_Open()
Application.ActiveWorkbook.Sheets(1).Activate
Application.ScreenUpdating = False
.
.
.
end sub
The ... stuff above is time consuming (about 20 seconds)
-not an issue.

worksheet "Main Page" code:
(behind the splash sheet -it's tab name is "Main Page")
Private Sub Worksheet_Activate()
UpdateDashList
End Sub

(in a module)
Sub UpdateDashList()
Dim sh As Worksheet
Dim bFound As Boolean

With Application.ActiveWorkbook.Worksheets("Main Page")
.lstDashboards.Clear
bFound = False
For Each sh In Application.ActiveWorkbook.Worksheets
If (InStr(1, sh.Name, "DSH")) Then
bFound = True
.lstDashboards.AddItem (sh.Name)
End If
Next
If bFound Then
.lblNoneToChoose.Visible = False
Else
.lblNoneToChoose.Visible = True
End If
End With
End Sub

.lblNoneToChoose is a label displayed if there are no matching worksheets.