Displaying userform before worksheet displays

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

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
 
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.
 
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.
 
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
 
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.
 
Back
Top