Consulting

Results 1 to 16 of 16

Thread: Show UserForm ONLY once workbook is fully open & displayed

  1. #1
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location

    Show UserForm ONLY once workbook is fully open & displayed

    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.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,273
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    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.)

  4. #4
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    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!

  5. #5
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    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!

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    How about this

    Private Sub Worksheet_Activate() 
        Static HasRun as Boolean
    
        If Not HasRun Then
            ' show user form
        End IF
    
        HasRun = True
    End Sub

  7. #7
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    Quote Originally Posted by mikerickson View Post
    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."

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,273
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    Quote Originally Posted by SamT View Post
    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.

  10. #10
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,273
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #11
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,921
    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.

  13. #13
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    Quote Originally Posted by snb View Post
    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.

  14. #14
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    Quote Originally Posted by mikerickson View Post
    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.

  15. #15
    VBAX Regular
    Joined
    Mar 2016
    Location
    Space Coast FL
    Posts
    47
    Location
    Quote Originally Posted by snb View Post
    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!

  16. #16
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    My mistake. My intent, throughout this thread, that code in the WorkBOOK_Activate event in ThisWorkbook module.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •