Displaying userform before worksheet displays

[Ken Warthen]

In the workbook.open event of an Excel spreadsheet I load a userform. Is
there anyway to display the userform before any of the worksheets load or
display? I'd like the end user to only see the userform and no worksheets.

Ken

 

[JLGWhiz]

I see a lot of postings that ask this question. I often wonder why the
developer does not just use a color filled sheet as background for their
UserForm. The code below, placed in the ThisWorkbook code module would
allow such an initial presentation of the UserForm:

Private Sub Workbook_Open()
Sheets(4).Activate 'Be sure there is a sheets(4)
Range("A1").Activate
ActiveSheet.Range("A1:Z60").Interior.ColorIndex = 5
UserForm1.Show
End Sub

The color fill range is arbitrary but should cover the displayed area on the
screen. The developer can pick any color index that suits their fancy.

 

[JLGWhiz]

You might find the scroll method better than activating Range("A1") of the
display sheet.

Private Sub Workbook_Open()
Sheets(4).Activate 'Be sure there is a sheets(4)
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("A1:Z60").Interior.ColorIndex = 5
UserForm1.Show
End Sub


This will avoid the highlighted cell within the window if you have place the
cursor off screen.

 

[Ken Warthen]

The workbook has about fifty worksheets in it. I wanted to display a
userform before while all the worksheets are loading. Adding another
worksheet will just further delay the loading of the worksheets, won't it?

Ken

 

[JLGWhiz]

Probably wouldn't speed things up any, doing it that way. You did not
specify what you want to do with the UserForm, so I just assumed you wanted
it displayed without a worksheet background. There was a posting a couple
of days ago in which it was suggested to make the Application hidden, since
if the Application is visible the workbook will require at least one
worksheet. I couldn't find the posting but if you re-post with an
explanation of what you are trying to do with the UserForm while the sheets
are loading, then someone should have a suggested solution for you.