Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Northern, California, USA
    Thanked 0 Times in 0 Posts

    Execute Code Behind Form (97 SR2)

    I have a set of code that I execute from one userform.

    What I'd like to do, is display another form While this code is executing.

    I thought I could simply put a statement before the code to make it display the form, run the code, then close the form:

    Sub Report_Run ' this is the form I want to display. It basically tells the user to wait while the report is being built....
    'Report Builder Code here
    report.hide 'This closes the form.
    End Sub

    Unforunately, the code halts until the form is closed. Any Ideas?

    <IMG SRC=>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Dublin, Ireland, Republic of
    Thanked 0 Times in 0 Posts

    Re: Execute Code Behind Form (97 SR2)

    A solution would be for both forms to be non-modal, but as you are using XL97 that is a tricky option. If all you want to do is display a message, you could include a Label with the caption set to the message, but have it set to invisible until you want to display the message. When your report tis built set it to invisible again. Or you could build the label on the fly as follows :<pre> Dim lblMessage As Control
    Set lblMessage = Controls.Add("Forms.Label.1")
    lblMessage.Left = 5
    lblMessage.Top = Me.Height / 4
    lblMessage.Width = Me.Width - 10
    lblMessage.Height = Me.Height / 3
    lblMessage.Font.Bold = True
    lblMessage.Font.Size = 18
    lblMessage.TextAlign = 2
    lblMessage.Caption = "Please wait while report is being processed"
    'Code to process report here
    lblMessage.Visible = False</pre>

    You would need to experiment with the variou size/positional properties to suit your existing form.


  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

    Re: Execute Code Behind Form (97 SR2)

    Here is one way to do that.

    Lets say your main form is called userform1 and your message form Userform2.

    First you show Userform1 as you are used to.

    On userform1 there is a button (button1) that gets pressed and contains this code:

    Private Sub CommandButton1_Click()
    End Sub

    In Userform2 's code window:

    Private Sub UserForm_Initialize()
    Application.OnTime Now, "demotimer"
    End Sub

    The routine demotimer is in a normal module and contains the code that needs to be run while your message is shown. e.g.:

    Sub demotimer()
    For i = 1 To 10000
    Application.StatusBar = i
    Application.StatusBar = ""
    Unload UserForm2
    End Sub

    Note that the code in demotimer unloads the userform2 when it has finished it's work, thus returning you to UserForm1.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

Posting Permissions

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