Code with Hidden Sheets

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
Can u help me with the code to hide / unhide entire worksheets - I need to unhide these 2 sheets before initiating my work and hide them again after I finish my work ! A macro wud be great !! Neverthless I know the basics of running and editing macros so that wud not be a problem : :eek:

EDIT - Split post - Smitty
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
can u help me with the code to hide / unhide entire worksheets - I need to unhide these 2 sheets before initiating my work and hide them again after I finish my work ! A macro wud be great !! Neverthless I know the basics of running and editing macros so that wud not be a problem :

Why do they have to be hidden/unhidden? You can work with sheets (in most cases) visible or not. What are you trying to accomplish?

Smitty
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
Why do they have to be hidden/unhidden? You can work with sheets (in most cases) visible or not. What are you trying to accomplish?

Smitty

these sheets are loaded with lookup formulae like vlookup() and index()match()...I need to unhide these sheets in order to populate the values from another workbook and then the Reports are sent to the management wherein I need to hide these sheets (which infact look very shabby cuz of the scattered data all over) ! Its okay if u cant help...I can figure it out all myself !
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Formulas will calculate regardless of a sheet's visible status, unless you turned calculation off somewhere.

Might you be talking about links that lead to errors when a sent wb can't find the link?

If so, you can value an entire wb relatively easily.

Smitty

(As for helping, that's why everyone's here)...:)
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

Formulas will calculate regardless of a sheet's visible status, unless you turned calculation off somewhere.

Might you be talking about links that lead to errors when a sent wb can't find the link?

If so, you can value an entire wb relatively easily.

Smitty

(As for helping, that's why everyone's here)...:)

naaah....there is no linking issue here...I know wat has to be done wen the recipient receives some kinda linking error...moreover...in my case the formulae does not calculate or populate the values if the sheet is hidden...I need to unhide that particular sheet whose reference is present in the worksheet which has the formulae...only then the values are populated - now I dunno why is that happening :cry:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
OK,

It's pretty easy to tackle with ScreenUpdating and a WorkbookOpen event.

Although (it's really late) and I haven't run into this without UDF's or Volatile functions, and rarley at that.

I.E.

Code:
Private Sub Workbook_Open()
  With Application
    .ScreenUpdating  = False
      Sheets("Sheet1").Visible = True
    .Calculate
      Sheets("Sheet1").Visible = XlVeryHidden
    .ScreenUpdating = True
   End With
End Sub

That might do it for you.

Smitty

Note: written offhand and not tested in your situation, but feel free to send it to me.
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
just to add to your knowledge there is no sheet in the workbook where I have changed its property to "xlVeryHidden" :eek:
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top