MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How can I hide a single sheet from a workbook?

  Asked By: Franklin    Date: Jan 17    Category: MS Office    Views: 961

I have a sheet in my woorkbook where I have put the many WebQueries
that I need.
I would like to know how to hide this single sheet for 2 reasons:
1. it really does not look pretty: some queries bring back much more
information then I need.
2. it is important that the user does not update the queries him/her
self for different reasons.
3. some of the queries take some time to bring the data in and this
is also not very pretty so see.

Consequent to this question:
Would the macros that update the queries still work if the sheet is
Most of those Webqueries contain lots of calcultations beside them
(that I will eventually replace by functions), with results that are
used by other sheets: would those results still be brought back?



6 Answers Found

Answer #1    Answered By: Aabirah Khan     Answered On: Jan 17

Format / Sheet / Hide...........................

Answer #2    Answered By: Utsav Shah     Answered On: Jan 17

You replied:
"Format / sheet  / Hide"

I'm really sorry... but could you write a full line for me?
I would not know how to use this.

Should I assume that if you did not answer my other questions, about
1. if once hidden, the subs that need this sheet would still work  and
2. if the calculated values needed in the other sheets  would be well

it's because the answer would be yes?

Answer #3    Answered By: Ziza Mizrachi     Answered On: Jan 17

you should go to the dropdown menus in Excel and
choose the one called 'Format', then under that choose the 'Sheet'
option, then under that choose 'Hide'. Shorthand:

In vba:
ThisWorkbook.Sheets("Sheet2").Visible = False
ThisWorkbook.Sheets("Sheet2").Visible = xlSheetHidden
ThisWorkbook.Sheets("Sheet2").Visible = 0

To make it so that the user  cannot use the Format|Sheet|Unhide... to
unhide the sheet  you can make the sheet 'very hidden':
ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Sheet2").Visible = 2

but you'd have to use vba to make it visible again (see below), or be
in the vbe (where you edit macros) to change the Visible property of
the sheet to TRUE/xlSheetVisible/-1 again in the Properties pane.

To make it visible again:
ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVisible
ThisWorkbook.Sheets("Sheet2").Visible = True
ThisWorkbook.Sheets("Sheet2").Visible = -1

Answer #4    Answered By: Fairuzah Alam     Answered On: Jan 17

Forgot to answer the other bits:
Subs will generally still work  as long as there's no selection going
on (which there sometimes seems to be in some copy/paste operations,
though not explicitly). A few other commands also need the sheet  to be
visible, but try it and see. If the code falls over, you can
temporarily make the sheet visible again
(Application.ScreenUpdating=False) while that code runs and make it
invisible after the code has run. The brief screen changes associated
with this can be hidden from the user  with
Application.ScreenUpdating=False before unhiding and rehiding, and
Application.ScreenUpdating=True afterwards.

I think calculated values on other sheets  will be OK as long as you
haven't switched to manual recalculation. But try it!

Answer #5    Answered By: Gerardo Morgan     Answered On: Jan 17

Wow I did not expect so many people would pay attention to my
beginner's question!
I have now many different code lines to try.

In you second reply, I mostly appreciated those lines:

Application.ScreenUpdating=False before unhiding and
rehiding, and
Application.ScreenUpdating=True afterwards

because this was one of the reasons I wanted to hide  the sheet:
While the webqueries are updated, (it can take a few seconds) it is
really not very pretty to watch.

So in order to update  them hidden if it works, I could put  a flag
that would tell the user  to be patient while the data  are being

As to hiding the sheets  I have different codes to try.

All this if it works on my old Excel version (1997), where in the
menu Window I have workbook  hide option, but no sheet  hide.

Which brings me to another question  that I shall post separately.

Answer #6    Answered By: Kawakib Mansour     Answered On: Jan 17

Sorry, I didn't realise you were after code for it. That's simply how you
do it from the Excel menu.

This command button code hides Sheet2

Option Explicit

Private Sub CommandButton1_Click()
Worksheets("Sheet2").Visible = False
End Sub

Didn't find what you were looking for? Find more on How can I hide a single sheet from a workbook? Or get search suggestion and latest updates.