make workbook visible

[Seeker]

Dear All,
Some files are opened under ActiveWindow.Visible = False when a master file
opened, a small code is needed to be placed in the Workbook_BeforeClose to
check if any invisible file(s) still open, if yes, make invisible file(s) be
visible and close file(s). A thread has mentioned by using
Windows(“file.xlsâ€).Visible = True, how should I do that please?
Regards

 

[Patrick Molloy]

a loop like this would do it...just add it to the before close procedure



Dim wb As Workbook
For Each wb In Workbooks
Windows(wb.Name).Visible = True
Next

 

[Seeker]

Hi Patrick,
Tks for your prompt reply. Is that means I have to list all workbook names
before Next like this:

Windows(wb.book1).Visible = True
Windows(wb.book2).Visible = True
Windows(wb.book3).Visible = True
.....
Next
End Sub

Rgds

 

[Jacob Skaria]

Try the below

Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then wb.Close False
Next

If this post helps click Yes

 

[Seeker]

How about the close books action ? where do I place the ActiveWorkbook.Close.

 

[Jacob Skaria]

The code is to be placed within the BeforeClose event of your workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then wb.Close False
Next
End Sub

If this post helps click Yes

 

[Seeker]

Hi Jacob,
The closed workbooks still invisible?
Rgds

 

[Jacob Skaria]

I didnt get you..Try opening those workbooks again manually..

If this post helps click Yes

 

[Seeker]

Hi Jacob,
I have two master files both has macro to open other files for data
extraction. One of the file (say workbook 3)is sharing by these two master
files. After your code placed to Workbook_BeforeClose, and ran both master
files' macro, I supose all opened invisible files should be restored as
normall visible files and be closed. I tried to open all files that had been
called by these two master files and able to open them normally. Only for
this workbook 3 is still invisible. Excel showing the opeing file scale at
the bottom when opening workbook3, but I cannot see anything after excel
finished open.

 

[Jacob Skaria]

Do you have any other code getting executed during Workbook Open event..

If this post helps click Yes

 

[Seeker]

Jacob,
Yes, Workbooks.Open and ActiveWindow.Visible = False
Thats it.

p.s. I found said file was auto hide in window arrangement. I have to
release it, then all back to normal, rest files do not need this procedure.
Rgds

 

[Patrick Molloy]

I'm not sure waht you mean. Is there any point in making the workbooks
visible if you're closing them anyway?
This code has an IF/END IF that closes all workbooks without saving except
the workbook runnign the code.

Dim wb As Workbook
For Each wb In Workbooks
Windows(wb.Name).Visible = True
if wb.Name <> thisworkbook.Name then
wb.Close False
end if
Next

 

[Patrick Molloy]

no, its a loop, so wb.Name will be 'book1' say in the first iteration, then
it will change to 'book2' in the next iteration.. Essentially Workbooks is a
collection of workbooks in the curent excel session and iterating FOR EACH wb
will assign each workbook to the variable wb for each iteration

 

[Seeker]

Jacob,
What I mean is file now opened as invisible, I have to go to Window ->
Unhide before I can manipulate it.
Rgds

 

[Seeker]

Hi Jacob,
I found the trick.
Since the file still hide in the window as invisible no matter when it is
opened by macro or manually, Firstly I opened it manually(it is invisible),
then unhide it from window, then file show up on window as visible, I saved,
closed, then the file back to normal. So now I can open this file manully
from directory path and able to alter contents. Thanks again for your help
and my problem is solved now.
Regards

 

[Seeker]

Hi Patrick,
Thanks for your code. However, with help from Jacob, my problem is solved now.
Thanks again.
Regards