MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Visible property in macro?

Posted by RoB on October 12, 2001 1:08 PM

I have 2 workbooks open, and want to make one inivisible using a macro. Robb told me to try:
Windows("Mybook.xls").Visible = False

I put this in my macro, but im getting the error:

Run-Time error '9':
Subscript out of range

anyone know what im doing wrong?


Posted by RoB on October 12, 2001 2:00 PM


i guess you cant activate a sheet when its hidden?

Posted by RoB on October 12, 2001 2:08 PM

Re: oh..Hmmm

I mean use a sheet in a macro when its hidden?

Posted by Todd on October 12, 2001 2:09 PM

Re: oh..Hmmm

that sounds right... you can set screenupdating=false, unhide, do something with hit, hide again, then set screenupdating back to true.

Posted by RoB on October 12, 2001 2:26 PM

help! it wont work

Heres my macro open code, where the first runtime error is:

Sub Workbook_open()
Sheets("2000 Customer DataBase Input").Cells(1, 29) = "1"

ActiveWindow.WindowState = xlMaximized
Application.WindowState = xlMaximized

Workbooks.Open Filename:="S:\Path\Funded 2000 Files Log.xls"

Workbooks("2000 Files Input Screen.xls").Activate

Sheets("2000 Customer DataBase Input").Unprotect
Range("B1") = "Last File Input: " & Workbooks("Funded 2000 Files Log.xls").Worksheets("2000 Files").Range("C65536").End(xlUp).Value & _
" " & Workbooks("Funded 2000 Files Log.xls").Worksheets("2000 Files").Range("B65536").End(xlUp).Value
Sheets("2000 Customer DataBase Input").Protect

Windows("Funded 2000 Files Log.xls").Visible = False

End Sub

I'm getting the Run time error on the last line
Windows("Funded 2000 Files Log.xls").Visible = False

any ideas?

Posted by Robb on October 13, 2001 5:51 PM

Re: help! it wont work


Subscript out of range error usually means the code is referencing something
that cannot be found/identified. Does the file name appear in the title bar as
Funded 2000 Files Log.xls or is it perhaps missing the .xls extension.

If it does not include the extension, change the code to:

Windows("Funded 2000 Files Log").Visible = False

In any event, make sure the name used in the code is axactly as it appears in the
title bar.

Any help?



Posted by RoB on October 15, 2001 4:22 PM


I put in :
Application.Windows("Funded 2000 Files Log.xls").visible=false

in a wild last attempt, and voila, it worked...i tried both your methods robb...but they didn't work :(

anywho, this works, thanks for the input anyway!