none
Userform Does Not Hide Until After the Macro Completes RRS feed

  • Question

  • I am running Excel 2013 on Windows 7.

    I have a general purpose Userform (used for entering passwords) which is in Personal.xlsb as it is used by several workbooks. I invoke it from one of these Workbooks.  On completion of useform data entry, the OKButton_Click event procedure is envoked and it calls Me.Hide before returning control to the calling code.

    The Userform does not get  hidden and remains visible until after the calling code completes. This may be several minutes as the calling code loops round opening (network) files and processing their data. ScreenUpdating is enabled in the calling routine whilst prompting the user for the password and then disabled thereafter.

    The code in calling rountine is:-

    Application.ScreenUpdating = True ' ' Prompt the user for the password to open all the Asset files ' PassWrd = Application.Run("personal.xlsb!M_EquityMacros.GetPassword", "Update Assets", False) If PassWrd <> vbNullString _

    Then

    Application.ScreenUpdating = False

    The code in the GetPassword routine (which is in Personal.xlsb) is:-

    Public Function GetPassword(ByVal TitleBar As String, ByVal YearEnd As Boolean)
        Application.EnableEvents = True
        
        Load PromptForPassword
        
        PromptForPassword.YearEnd.Enabled = YearEnd
        PromptForPassword.Caption = TitleBar & "  (" & VersionNbr & ")"
        
        PromptForPassword.Show
        
        GetPassword = PromptForPassword.EnteredPassword
        
        Unload PromptForPassword
    
    End Function

    The code in the Userform event procedure is:-

    Private Sub OKButton_Click()
        Me.Hide
    End Sub

    All very simple - but the userform does not hide until the calling macro completes.

    I have also noticed that if Windows (?) displays the "Downloading Progress Bar" when opening one of the networked files, this too does not hide after the file is opened and remains visible until the macro completes.


    • Edited by Tony_Hedge Tuesday, August 22, 2017 7:50 AM
    Monday, August 21, 2017 8:32 AM

All replies

  • If it was me, I would call the userform as follows. I don't know what VersionNBr relates to as it is not defined.

    Public Function GetPassword(ByVal TitleBar As String, ByVal YearEnd As Boolean) As String
    Dim ofrm As New PromptForPassword
        Application.EnableEvents = True
        With ofrm
            .YearEnd.Enabled = YearEnd
            .Caption = TitleBar    '& "  (" & VersionNbr & ")"'
            .Show
            GetPassword = .EnteredPassword.Text
        End With
        Unload ofrm
        Set ofrm = Nothing
    End Function

    In the calling worksheet I would use

    Dim PassWrd As String
        PassWrd = Application.Run("personal.xlsb!M_EquityMacros.GetPassword", "Update Assets", False)
        'the form should already have been dismissed here'
        If Not PassWrd = vbNullString Then
            MsgBox PassWrd 'e.g.'
            Application.ScreenUpdating = False
            'Do stuff'
        End If
        Application.ScreenUpdating = True
    Obviously we don't know anything about the rest of your macro, but this part works as intended.


    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, August 21, 2017 10:53 AM
  • Hi Graham, thanks for your suggestion. I altered the code as you suggested above. But it did not make any difference. The Userform still did not Hide.

    I think this is a real-time issue and that the thread that hides the Userform does not get run until after the macro has completed. If I breakpoint the macro at any point after returning from the Userform, then the Userform is immediately hidden.

    However I find this behaviour strange as the macro is looping round opening, then closing many (network) files, and I would have thought that whilst waiting for a file to open the thread running the macro would have released control of the cpu, thus allowing the Userform to be hidden by the other thread.

    Is there any way to lower the priority of the thread running the macro?

    Later update ....

    I think I have just proved that this is a real-time issue. If I amend the code, as below,  by adding a loop invoking the DoEvents function the Userform will reliably Hide

        Application.ScreenUpdating = True
    '
    '   Prompt the user for the password to open all the Asset files
    '
        PassWrd = Application.Run("personal.xlsb!M_EquityMacros.GetPassword", "Update Assets", False)
        
        For i = 1 To 60
            DoEvents
        Next
    
        If PassWrd <> vbNullString _
        Then

    If only go round the loop 50 times, the Userform unreliably Hides.

    I don't like looping round calling DoEvents, but it seems to work. Are there any better solutions?



    • Edited by Tony_Hedge Tuesday, August 22, 2017 8:53 AM
    Tuesday, August 22, 2017 8:00 AM
  • I suppose you could add the following to your module

    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Then call Sleep from your macro in place of the loop e.g.

    Sleep 1000
    MsgBox "Slept for 1 second"


    Graham Mayor - Word MVP
    www.gmayor.com

    Thursday, August 24, 2017 10:56 AM
  • Although Excel supports threading internally and seems to do a good job of using it.  VBA has no access to threading.  DoEvents blocks execution for a short period and accepts events during those periods.  I had an Excel optimization routine that could run for hours.  The only way I could figure out how to use a multiple core processor was to automatically divide the problem and dispatch multiple instances of Excel.  It seemed to do a good job of using the cores. 

    I wish VBA had C#'s async/wait.  If fact, I wish Excel would incorporate C# natively.

    Saturday, August 26, 2017 11:23 PM