Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms (VBA, Office 2000)

    I'm having *real* problems with forms, and I could do with some help. It should be soooo simple, but I just can't get it to work properly. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    The problem comes when I want to hide a form, do some stuff, and then reshow it - for example, in one particular case, I need to hide the form so that I can see the print preview of the spreadsheet I've just created, and then I want to continue on once the preview is closed. Unfortunately, I don't seem to be able to unjide a form - I can only SHOW it. This then puts me back into the form at the entry level, rather than where I left off:


    Let me try to be a bit more descriptive. I have a form which allows the user to set some options before creating a report (=spreadsheet). Set the options, click a go button. This checks the validity of the options, generates the report, and then previews it using Excel Print Preview. Now, if I don't want the form obscuring all of the print preview (and, incidentally, grabbing focus so that I can't close the preview, and therefore locking the program up), I hide the form using Me.Hide - I'm prohibited from setting the visible property directly, so this appears to be the only way.

    So far so good, form goes away, print preview seen and closed, code continues to execute. At this point, I would like to display the form again so that I can carry on using the same options (for example, to have a report for a new date). Trouble is, the form starts again at the beginning, and being displayed modally, won't finish running my tidy up code until you close it. I can't display it modelessly, as the form is already displayed modally. HELP!


    I think that each time I run a report I get another instance of the form (or the same one, but multiply re-entered), and this can't be good. What am I doing wrong? Is there a better way?

    PLEASE HELP!

    Regards,

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

    Re: Forms (VBA, Office 2000)

    Stuart,

    I just tried the following and it seemed to work ok, the Form being restored to the same state as it was when th ecode was invoked. I ihad the code attached to a command button, but it should work in any situation. <pre>Private Sub CmndPreview_Click()
    Me.Hide
    ActiveWindow.SelectedSheets.PrintPreview
    Me.Show
    End Sub</pre>

    Are you doing something that might cause your procedure to restart, or are you unloading the form somewhere ?

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (VBA, Office 2000)

    Andrew, hi.

    I have code that runs on form startup, yes (I think it's in the INITIALIZE event rather than the ACTIVATE event, though). My problem occurs because I have code which runs AFTER the form show - in this case, because I show the form, and then ask if the user wants to continue using these settings or reset. I can't do this, because the form is modal, and so my code execution stops at the SHOW command until I have dealt with the modal form.

    In your example, add a line to change the caption *after* the show, and click your button three or four times. Now, the caption won't have changed, and look at the call stack - there are now several nested calls to your form. This is not nice - I could potentially generate 31 daily reports in my application, one after the other, and get thirty one levels of procedure nesting. Equally, I am not at all sure that subsequently unloading the form removes them all cleanly - I am not exiting the procedure cleanly, and hence which of the calls in the stack gets terminated first?

    All further assistance gratefully received! Thanks.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Forms (VBA, Office 2000)

    If performance and elegance are lower priorities than getting this to work, you could keep all your data in global variables and fully unload the form between uses, repopulating it from global variables in your initialize routine to restore the last state. Not being a person trained to encapsulate all my functionality inside form code modules, this seems natural to me, but many would scoff. To those, I apologize.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms (VBA, Office 2000)

    It isn't so much a matter of scoffing, Jefferson. One of the biggest problems with global variables is that they break so easily and are so easily stepped on. One unhandled (and unexpected) error will turn all your global variables to confetti without any warning. And let a couple of intertwined routines try to use the same global but different values, and you'll see why I avoid them. If you do use them, they need to be very carefully managed, which is a job in itself.
    Charlotte

  6. #6
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (VBA, Office 2000)

    Hi Stuart,

    What about creating a variable that tells you were you are up to in the form population so you can skip the part of the code that has already been executed.

    Alternatively, instead of hiding the form, change the height and width to a smallish value and move it almost off the screen. If it doesn't release control so that the user can close the preview, then change your now smaller form to include (visible) CLOSE button and use that to close the preview and resume.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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