closing another file by code causes failure to run workbook activate event

K

KenCowen

I have a userform in workbook A that opens workbook B and hides the userform. File B has a button to "return to the userform (workbook A)". All the button does is close workbook B, which should activate workbook A and trigger the reshowing of the original userform; but the activation event doesn'ttrigger and the form does not reshow, but wb A is the active workbook. IfI close workbook B with the x or file close command, workbook A is activated and the activation code runs, triggering the redisplay of the userform. All the code does is

ThisWorkbook.Close

It actually used to do a lof of other stuff, but, I can't get it to work with just this line. Something is different about closing the form from thisone line macro, and closing through file close, I think. Can anyone shed any light on what is going on, and perhaps provide an alternate method?

I have also tried leaving wb B open, then close it when wb A is closed through the userform termination event. Wb A closes fine that way, but, if closing wb A is preceded by the closing of wb B in the userform termination code, the code stops after closing wb B and leaves wb A still open. Basically, when the userform is closed, I want both files to be gone; and I can't seem to get it done.

Thanks

Ken
 
K

KenCowen

Post your userform code.

James

I have workbook test1.xlsm with a single userform. The userform has a single button with the following code:

Private Sub CommandButton1_Click()
Workbooks.Open ("test2.xlsm")
Unload Me

End Sub

Workbook text2.xlsm has a worksheet button with the following code:

Private Sub CommandButton1_Click()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

WB test1.xlsm has the following in ThisWorkbook code module:

Private Sub Workbook_Activate()
UserForm1.Show
End Sub

The userform is shown whenever test1 is activated; unless test1 is activated by closing test2 through the command button1 code. If test2 is closed with the x button or file close, the userform is shown. I have workaround that is working okay, but, it seems overly complex. I have added the triggering of an OnTime code to the command button code on test2. The OnTime function triggers the redisplay of the original userform after a one second delay. It works, but, I would reall like to know why test1 does not run the activate code when it becomes active through the closing of test2.

Thanks

Ken
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top