PDA

View Full Version : How to hide the workbook while exeuting macro?



gopi09_m
05-17-2008, 11:08 AM
Hi,

How can i hide the workbook named WrkBk1.xls, when i have opened the WrkBk2.xls from the macro of WrkBk1.xls. Macro in WrkBk1.xls will process the data in WrkBk2.xls during which i want user to not able to select the WrkBk1.xls.So i thought of hiding it or when user selects it, it wont let user select the WrkBk1.xls during execution.

And once the execution is done i will close WrkBk2.xls and in WrkBk1.xls i will show the message done.

Regards,
Krrishna

Bob Phillips
05-17-2008, 11:31 AM
Windows("WrkBk1.xls").Visible - False

gopi09_m
05-17-2008, 08:40 PM
Windows("WrkBk1.xls").Visible = False
Its throwing the below error..
Run-time error '9'
subscript out of range

georgiboy
05-18-2008, 12:08 AM
Have you thought of something like this

Windows("Book1").WindowState = xlMinimized
MsgBox "Hello"
Workbooks.Add
MsgBox "Hello"
Windows("Book4").Close False
MsgBox "Hello"
Windows("Book1").WindowState = xlMaximized
End Sub

its just some random code between two windowstates, you could adjust it for your needs.

Hope this helps

Bob Phillips
05-18-2008, 03:01 AM
Windows("WrkBk1.xls").Visible = False
Its throwing the below error..
Run-time error '9'
subscript out of range

That suggests that you do ot have a workbook by that name.

Simon Lloyd
05-18-2008, 01:59 PM
That suggests that you do ot have a workbook by that name.If you are trying the code on an unsaved workbook i.e you have just opened Excel and see Book1 then the .xls extension does not apply as you haven't actually saved it as a type.

Try this:
Open a new workbook, go to the VBE and add a sub, now add this line:

Windows("book1.xls").Visible = false
run the sub and you get Runtime error 9, Subscript out of range, now change the line to this:

Windows("book1").Visible = false
and it works fine!

gopi09_m
05-18-2008, 09:05 PM
Thank you.Thats exactly what i required.