[RESOLVED] minimizing excel

[forunna]

G'day, I am trying to minimize excel while running a questionaire based on a series of userforms.
The forms access a number of worksheets in the same workbook.

Ive tried this when the first form initializes but it stops the form from appearing;

Application.WindowState = xlMinimized

Ive tried this but if the forms are stopped before the end it appears as though excel is closed yet it is still open, jsut not showing anywhere not even down the bottom;

Application.Visible = False

with;

Application.Visible = True

at the end.

any ideas?

[Lord Orwell]

instead of putting it in the load, try putting it in the form activate.

[RobDog888]

Sounds like you are trying to minimize the Excel application yet keep a UserForm in normal state. This is not possible as Excel is a MDI application.

[Lord Orwell]

perhaps he could trick it with a setparent call to assign his window to the desktop?

Yoda are you sure? I know it can't be done in ACCESS but i thought excel used standard VBA forms.

[Lord Orwell]

Excel has a neat learning feature you can use to learn how to do anything in VB. the macro recorder. Unlike ACCESS, etc. it creates vb code. Here is code that worked for me.
This is in a module:
Application.WindowState = xlMinimized
UserForm1.Show

One thing you have to keep in mind when doing tricks like this in office is that for some reason (and i really don't know why) code will only work correctly depending on where it is placed. I have the best luck with modules but for example i had a heck of a debugging fit with cut and paste operations in excel with code in a sheet. The code didn't work until i moved it to a module. Perhaps it is the same issue here. I doubt you will get Excel to behave if you mess with it with code from inside a form.

[forunna]

still cant get it.

Under Activate the minimize does nothing and the visible=false has the same problem. as under Initialize,
i.e. excel dissapears and the forms work great, nothing shows that excel is open (even task manager). yet when I open the file again it says the file is already open and reopening it will lose any changes. If i close and open before the application has made any changes to the worksheet there is no problem.

When I put the code under Initialize everything is minimized and adding frmWelcome.show doesnt work. (thats what the first form is called).

[Lord Orwell]

put it in a separate module like i said. Is the fact it is asking you to save on close the problem?

[RobDog888]

Yes, you can do it with SetParent but then you will also run into issues with the user form sending messages to the message stream of a non-excel window like the desktop.

The second solution will work, I wasnt thinking last night

You may want to show it modelessly though depending on your needs.

UserForm1.Show vbModeless

Also, if they switch workbooks or Excel apps then it will reshow the application again.

[Lord Orwell]

i have suppressed that msgbox before... i can't remember how i did it. hmm.
Either do one of these things:
ActiveWorkbook.Protect Structure:=True, Windows:=False
at beginning of code
and
ActiveWorkbook.Unprotect
at end

OR

save the workbook again before closing through code. You are evidently changing something through code excel wants to save. Could be something as simple as which page has focus.

[RobDog888]

Can you attach your workbook?

[forunna]

Thanks for all your help but I have worked around it now.
The problem was when I made excel hidden it was staying open but not visible if the forms were closed prematurely.

I have disabled the little red Xs at the top and given them all a little EXIT button that closes the whole application immediately and suppresses the query to save.

Private Sub cmdExit_Click()
Application.DisplayAlerts = False
Application.Quit
End Sub

[Lord Orwell]

yeah that was it, displayalerts!