VBA to avoid other Excel Instance/workbooks from closing when “Application.visible=False”

[Reetesh]

Hello.
I'm fairly new to VBA and need help with this small problem.

I've an excel workbook which has a Userform in it. Whenever the userform is active it hides the workbook on which the userform is. To do this I've used "Application.visible=False". Now the problem that i'm facing is other workbooks are getting closed as well, which were already open before, whenever the Userform is active. I've tried using "Windows("workbook.xls").Visible = False", however it stops some of the functions on Userform and 'Thisworkbook.Application.visible=False" & "Workbooks("workbook.xls").Application.visible=False" on the workbook in which the userform is, however its still the same. It closes all the workbook which were already open. I've tried to use "Sheets("Home").Visible = xlVeryHidden" however its giving the "Runtime Error 1004: Unable to set the visible property of the Worksheet class."

 

[Legacy 456155]

Now the problem that i'm facing is other workbooks are getting closed as well.

Do you mean that other workbooks are becoming hidden? Or are they being closed? I don't see anything in your post that closes anything.

If you wish to hide an instance and yet interact with other workbooks, you will need to create a new instance of the Excel.Application. Please expound a bit. Typically, one would use Workbooks.Count to determine to quit the application or make it visible upon closing the workbook that had hidden the app.

 

[Reetesh]

Yes exactly, the other workbooks are getting hidden, not closed. Sorry i made a confusion by mentioning the word "close".

"If you wish to hide an instance and yet interact with other workbooks, you will need to create a new instance of the Excel."
What should i do here exactly?

Really sorry for being a pain, as i mentioned in my post, I'm fairly new to vba, because of that might not be able to understand what you said.

What i actually want to do, is to only hide the workbook on which the userform is and that workbook should only become visible when the userform is closed. And while doing this, it should not hide the other workbooks which are already open.

 

[Legacy 456155]

What i actually want to do, is to only hide the workbook on which the userform is and that workbook should only become visible when the userform is closed

Maybe?

Private Sub UserForm_Initialize()
    ThisWorkbook.Windows(1).Visible = False
End Sub

Private Sub UserForm_Terminate()
    ThisWorkbook.Windows(1).Visible = True
End Sub

If there will ever be multiple windows, you can loop through them and hide/show them all.

 

[Reetesh]

I've tried "Windows("workbook'sname.xls").Visible = False". However some functions on the userform doesn't work. There is a table which i use for the drop down list in the userform, that doesn't show any options when i used it.
But never tried "ThisWorkbook.Windows(1).Visible = False". Will give it a try and let you know whether it works or not.
Thanks

 

[Reetesh]

Hello dataluver,

I've tried using "ThisWorkbook.Windows(1).Visible = False", however its doesn't work. It doing the same thing which it did when i used
"Windows("workbook'sname.xls").Visible = False".

So in the userform there is a ComboBox which i'm using and the "Row Source" for that is a table which is in the workbook. whenever i'm using "ThisWorkbook.Windows(1).Visible = False" or "Windows("workbook'sname.xls").Visible = False" the combobox does not display the options from the table.

It work in not hiding the other workbooks which were already open when the userform is in active. However, above mentioned is the problem which i face when i use this function.

 

[Legacy 456155]

I don't know why hiding would make the data inaccessible. Can you upload a copy of your file? If you wish, you can drop it here. If you do, post back with the name of the file. In the mean time, I'm going to try and reproduce the problem you are running into. Anyone else have any light on this?

 

[Legacy 456155]

Ok. I created a simple table, set the rowsource of a combobox, and hid the workbook on Initialize. The rowsource held true. I wonder what you might be doing differently? Beside xlVeryHidden worksheets, I don't ever recall running into any issues like this. Doesn't mean they don't exist, but it's new to me. Upload your file if you can and/or give out some more details.

 

[Reetesh]

Hello dataluver,

I've uploaded the file in the link which you mentioned. Then Name of the File is "Tracker.xls(197KB)". The file is made on Excel version 2003. The "Row Source" for the "ComboBox" is on the "List" worksheet in the workbook and in visual basic the module where i was trying the "ThisWorkbook.Windows(1).Visible = False" and "Windows("workbook'sname.xls").Visible = False" is "Load_Form".

Let me know if any confusions are there.
Thanks for the all the help mate. Appreciated.

 

[Reetesh]

Hello there,

Just wanted to let you know that the worksheet on which the Row Source for the combobox is "xlVeryHidden" . Because others should not be able to see other worksheets apart from the "Home" worksheet in the workbook.