Show UserForm ONLY once workbook is fully open & displayed

[bifjamod]

I have a userform set to run on the Workbook_Open event, as below:

With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With

The problem I am encountering is that the userform displays before the "Home" page of the workbook is displayed. I need the user to be able to see the data on that page before making a listbox selection from the userform. I considered placing it in a worksheet_activate event, but that also does not solve the problem, as it would then trigger the userform every time the user visits another sheet and then returns, which is not desirable.

I've tried both a worksheet.activate and an application.wait before the userform, to no avail.

Has anyone a suggestion on how to resolve this? I see lots of search results for a delay on unloading, but not on loading.

[SamT]

Is it that you only want the form to show once per book opening?

Declare a module level Boolean Variable, FormShowed, in the Home code page

In the Home code page, use Selection_Change to show the form. This does require the User to click on the sheet somewhere, but you can't have everything.

In the selection_change code, test If Not FormShowed, before showing the form, set FormShowed = True after showing the Form the first time.

Personally, I would use the Sheet_BeforeDoubleClick Event to allow the User to decide when to show the form at any time.

[bifjamod]

Thank you for the assist, Sam. Your idea may work; however, I have a compile error - object required on the "Set FormShowed = True" line. I may have declared the boolean incorrectly? See code below.

The objective of the process is that when the user opens the workbook, they select their name and the process being performed (which is why they may need to see the "Home" page to be sure what process is required), which in turn timestamps a timekeeping page. Once the workbook is closed, the timekeeping page is timestamped with the close time. So, it should only happen once per workbook opening.


Dim FormShowed As Boolean

Private Sub Worksheet_Activate()
If Not FormShowed Then
With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With
Set FormShowed = True
Worksheets("TIME").Range("D1999").End(xlUp).Offset(1, 0).Value = UserForm1.ListBox1.Value
Worksheets("TIME").Range("E1999").End(xlUp).Offset(1, 0).Value = Left(UserForm1.ListBox2.Value, 2)
End Sub

(I used the worksheet activate event as opposed to the worksheet change event....will tinker with that to see which is better.)

[bifjamod]

I got it working with this:

Dim FormShowed As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not FormShowed Then
With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With

End If
FormShowed = True

Worksheets("TIME").Range("D1999").End(xlUp).Offset(1, 0).Value = UserForm1.ListBox1.Value
Worksheets("TIME").Range("E1999").End(xlUp).Offset(1, 0).Value = Left(UserForm1.ListBox2.Value, 2)
End Sub

Primarily, it did not like the SET command; also, it did not play well with the Worksheet_Activate event, so I'll experiment with this to find the best application for my purpose.

Thanks again!

[bifjamod]

Hmmmm....this dramatically slows down data entry on the Home page, as it's processing the script every time I change cells. Not the solution I was hoping for after all.

UPDATE: OK, I see the problem; I need the timestamp functions contained with the IF/THEN section. Problem solved! Thanks for putting up with me!

[mikerickson]

How about this


Private Sub Worksheet_Activate()
Static HasRun as Boolean

If Not HasRun Then
' show user form
End IF

HasRun = True
End Sub

[bifjamod]

How about this


Private Sub Worksheet_Activate()
Static HasRun as Boolean

If Not HasRun Then
' show user form
End IF

HasRun = True
End Sub


I understand (after a little reading up) what the purpose of the static variable is in this case; however, it still does not resolve my primary problem. When opening the workbook, it opens to "Home" (sheet 1). The user needs to see information on that sheet to make an informed listbox selection. Unfortunately, merely opening the workbook and even with coding the sheet to activate is insufficient to run the script based on worksheet_activate. It requires changing to a different sheet, then back to activate it.

I also tried scripting the worksheet changes as part of the workbook open process, but that still results in the userform displaying before the workbook has fully displayed.

Thanks, though - I learned something new with the "static variable."

[SamT]

The user needs to see information on that sheet to make an informed listbox selection.
What you really mean is that the UserForm cannot show until the User is ready for it.

Use an Event procedure. BeforeDoubleClick would be my choice, but SelectionChange is viable as well as adding a Control Toolbox Command Button

[bifjamod]

What you really mean is that the UserForm cannot show until the User is ready for it.

Use an Event procedure. BeforeDoubleClick would be my choice, but SelectionChange is viable as well as adding a Control Toolbox Command Button

I currently have it set to SelectionChange; it's not ideal, but it works. Regardless of BeforeDoubleClick or SelectionChange, it requires the user to remember to make some input before starting work that may not be in the Excel workbook but should still be timed and charged to that workbook as part of the process. That is why it's best to force the userform display without any input required.

As I said, it's not ideal, but it works....as long as I remember to make a selection change before doing anything else.

[SamT]

That is why it's best to force the userform display without any input required.
You're not limited to one Event. Use as many as it take for Excel to notice that the User has started doing something.

If you look at the WorkBook Events, they include all those and many more.

Once the Workbook is open, there is nothing the User can do that can't be used as a trigger to run the Form. Even if he/she does nothing at all for some time, that Wait can be used to trigger the Form.

[mikerickson]

It sounds like Activating the Home sheet is part of your opening the user form, so you could code it.


Private Sub Worksheet_Activate()
Static HasRun As Boolean

If Not HasRun Then
ThisWorkbook.Sheets("Home").Activate
' show user form
End IF

HasRun = True
End Sub

Any other preparatory steps could be included in the 'show user form comment.

[snb]

I need the user to be able to see the data on that page before making a listbox selection from the userform.

You'd better show those data in the userform.

[bifjamod]

You'd better show those data in the userform.

That's a good idea as well; it's not particularly ideal in this situation, but as a last resort, I could feed a limited data set to the userform. Thanks for suggesting it.

[bifjamod]

It sounds like Activating the Home sheet is part of your opening the user form, so you could code it.


Private Sub Worksheet_Activate()
Static HasRun As Boolean

If Not HasRun Then
ThisWorkbook.Sheets("Home").Activate
' show user form
End IF

HasRun = True
End Sub

Any other preparatory steps could be included in the 'show user form comment.

Another good idea, but with no satisfaction. When the workbook opens, it opens to "Home", but does not trigger worksheet_activate event unless a different sheet is selected and then I come back to this one.

[bifjamod]

You'd better show those data in the userform.

The more I thought about it, the more I liked this solution; I can add several of the cell values to the userform, which should suffice for the user to know what listbox selection to make. The user may not have view of ALL of the sheet's relevant data, but there should be enough to make this the best solution so far. Thanks again for suggesting it!

[mikerickson]

My mistake. My intent, throughout this thread, that code in the WorkBOOK_Activate event in ThisWorkbook module.