Forms (VBA, Office 2000)

Topic

New Reply

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.

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,

Reply | Quote

Replies

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.

Private Sub CmndPreview_Click()
    Me.Hide
    ActiveWindow.SelectedSheets.PrintPreview
    Me.Show
End Sub

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

Andrew C

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote