Open a workbook with only one sheet visible.

**jad70** · 03-10-2009, 09:50 AM

Hi All,

Quick question, and I have spent the last hour searching for the answer.

How do I get a workbook to open with a selected sheet visible, or preferably all hidden? I already have a userform that opens upon opening the workbook that the user 'Enters' the workbook with.

My untidy method would have been to use an If -Then statement for each sheet in the Private Sub Workbook_Open. Is there a neater way of doing it?

Many thanks in advance.

JD

**ccbltdneedhelp** · 03-10-2009, 10:57 AM

I think all you really need to do is when you have the workbook open. right click on each tab you want to hide, and then click hide. This should leave only the one worksheet you wish to be visible. Thats how i did it anyways.

**jad70** · 03-11-2009, 06:59 AM

Thanks for the top tip, but I require the Open book event code that makes all but the selected sheet hidden, so that when the workbook is opened, no matter what sheets were left visible the last time it was opened, it only opens with a certain sheet visible.

Any takers?

JD

**Hamo987** · 03-11-2009, 09:21 AM

On the workbooks load event,(or when you kick off your macro) can't you just select the worksheets you don't want visible. Something like this

Please Login or Register  to view this content.

Could try something like that, basically select the worksheet you don't want to be visible, then set its property to hidden. Should work. You could also try recording a macro manually where you hide the sheet just to find out the correct syntax for the code.

There is an OnLoad setting to where you could put this code. Click on this workbook in the VBE. Then select workbook on the drop down list on the left. Then you should see a bunch of event handlers you can play with. One of them being "Open".

Hope this makes sense, I'm very tired.

**jad70** · 03-11-2009, 09:29 AM

Hamo,

yep, I could do it that way, but I would prefer to select only one sheet to be visible, rather than make the other 7 hidden, if you see the difference.

JD

**modytrane** · 03-11-2009, 09:53 AM

the attached example does what you want.
Just add as many sheets as you want hidden inside the Array.
modytrane

**jad70** · 03-13-2009, 06:17 AM

Modytrane,

many thanks for the response. I encountered a couple of problems with this as I have a couple of sheets 'very Hidden' and the code makes them visible again. It would appear that the only viable way of doing this is to make each sheet .visible=False. As this works I shall now leave well alone....

JD