VBA BeforeClose problem 1

[Queryman]

I have several workbooks that are formatted the same, but have different data. I have a userform that shows up for two seconds when the workbooks are closed. I am now changing the workbooks using only code and want to save and close them, but that triggers the showing of the user form. Since I have several workbooks, I do not wish to manually click the X button on the userform. Is there a way to get the userform to close using code, currently I have Activeworkbook.save and activeworkbook.close and that saves and closes the workbook, but leaves the userform open.



Michael

 

[Tigerlili3]

You will need to reference the user form by name to close it.

 

[Queryman]

I did, the form is called frmTBP. I tried kill frmTBP, unload frmTBP, it does not seem to make a difference





Michael

 

[PHV]

Is the UserForm modal ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tigerlili3]

Are you setting any values in the user form with your code or is the form not being used anymore? If it is not being used, it would be better to remove the code that triggers the user form to open. If you do still need it to open and be closed, try this...

Unload "frmTBP"

before closing the workbook.

 

[Queryman]

The form only appears on the beforeclose because I use it to show some info for the users.

The fowm is not loaded before that, so it cannot be unloaded or killed. It is part of the beforeclose event to show the form. Form is modal.

If I did not have so many workbooks to open and change, I guess I could try Application.quit at the end of my loop

Wish there was a way to close this pesky form.



Michael

 

[PHV]

Just a thought, have you tried to send Alt+F4 with SendKeys ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

Try this:

docmd.close acform, "frmMyModalForm"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

John, this is an UserForm, not an AccessForm.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tigerlili3]

Is the change you're making a one-time change where you just don't want the forms to show up while you're making the change, but want them to show up in the future when a user is closing the workbook? If this is the case, just comment out the line that opens the form and then remove the comment marker when you have completed your change.

 

[Queryman]

The code that opens the form is inside each workbooks beforeclose event, changing that line inside Thisworkbook beforeclose event seems complicated.

The change I have is a one time change, wher I am adding some data to each workbook, saving and closing it. In teh future, I would want that userform to display on beforeclose event.



Michael

 

[Queryman]

Did try SendKeys "%{F4}", True , did not work



Michael

 

[AnotherHiggins]

Maybe I should read the post next time, eh?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[rmikesmith]

Michael,

Try something like this:

...
ActiveWorkbook.Save
Application.EnableEvents = False
ActiveWorkbook.Close
Application.EnableEvents = True
...

You can set EnableEvents = True after all workbooks have been processed if you prefer, just make sure to do so as this property is persistent.


Regards,
Mike