Forms in Excel

[jeffcravener]

I have an 'automated' Excel report and I wanted to use a form to hold a text box that would display the status of the report as it is running.

I have never used forms in Excel before, and to my dismay, they do not have all the properties accociated with normal VB forms.

Here is what I would like to do with the form:

I don't want the user to be able to close the form
I want the user to be able to minimize the form
I want the form to remain on TOP of all other windows

Is it possible to do the above?

[WorkHorse]

Nope.

You can stop closing with the Terminate event. You can fake a minimize that reduces the UserForm size and puts it out of the way. But the UserForm acts as part of the Excel application. After all, it is VB FOR applications. If you minimize the app the UserForm minimizes along with it. The UserForm doesn't have an hWnd so you can't use API to make it always on top.

You either have to start something else to show the status, find some APIs that will show a status window, or just go with showing the status in the Excel status bar.

[Daniel McCool]

UserForms can be made to stay on top. My current project has a UserForm that stays on top of the WorkBook. I don't see any property for this, and have done nothing to make it do this. I can't tell you how to make it work for you, but it proves it can be done.

Edit: In response to the reply after this: "Oops, misunderstood what he was intending it to be "Always on Top" of."

[WorkHorse]

How the UserForm operates compared to the Workbook is determined by the ShowModal property. UserForms can stay on top of the Workbook, but they can not stay on top of other applications if the Workbook is minimized. If the Excel Application is not on top, then the "Visual Basic" UserFrom for the "Application" (VBA) cannot "remain on TOP of all other windows".