hidden worksheets

[monk999]

hi,

i want to hide the worksheets in a workbook.I tried to change the visibility of the worksheets from the sheet properties.Actually the sheets were hidden;however i recorded some macro buttons,which goes after pressing to the corresponding worksheets.Since the worksheets are nto visible now,i am faced with an error.How can i solve this problem?

Thank you,

 

[Loomah]

I'm a little confused by what your problem may be here

If you copy the code below into the General area of 'ThisWorkbook' and run each in turn you should find the first one hides a sheet (3), the second accesses the hidden sheet to change a value and the third will unhide it again!
Option Explicit

Sub hide()
Worksheets("sheet3&quot.Visible = xlSheetVeryHidden
End Sub

Sub aaa()
Worksheets("sheet3&quot.Range("A1&quot.Value = 1
End Sub

Sub unhide()
Worksheets("sheet3&quot.Visible = xlSheetVisible
End Sub


stop press!
I've just tried to recreate what I think your error may be
If you try to 'Select' a hidden sheets you will get an error so either unhide the sheet before doing stuff to it and re-hide after or access the data without selecting or activating the hidden sheet - incidentally a more efficient way of coding in VBA.

 

[monk999]

@loomah

Thx,


maybe i could not make myself clear enough.I want to hide
the worksheets which are standing in a row at the bottom.(which you see all of them like Sheet1, sheet2,...sheet15)

I applied your code but nothing changed. :<

 

[Loomah]

Maybe I'm beginning to understand
If you just want to hide/show the worksheet 'tabs' than use

ActiveWindow.DisplayWorkbookTabs = True/False

Of course this won't stop users from accessing different sheets in your wokbook if they know the shortcut keys for navigating worksheets

 

[monk999]

What's wrong?

I did copied the code &quot;ActiveWindow.DisplayWorkbookTabs = False&quot; under &quot;this workbook&quot;,but still seeing the sheet tab.

Nothing has changed!Where am i making a mistake?

 

[dsi]

You can put your program calls on the main sheet and unhide the sheets as they are needed. For example, put a program button on Sheet1 that unhides and activates Sheet3.

'Contained in the Code for the main sheet (Sheet1)
Sub ActivateSheet3()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet3")
    'Unhide the sheet
    ws.Visible = xlSheetVisible
    ws.Activate
    'The _Activate code for Sheet3 should now run
End Sub

To hide all worksheets (except one):

Private Sub Workbook_Open()
    Dim ws As Worksheet
    'Hide All Sheets but one
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> &quot;Sheet1&quot; Then
            ws.Visible = xlSheetHidden
        End If
    Next
End Sub

Hope this helps...

 

[Loomah]

I'm basically stumped so...
ensure the visible property of the required sheets is set to &quot;xlSheetVisible&quot; as shown in my first response or do this by choosing Format>Sheet>Unhide from the menu
from the Tools menu in Excel (not in the vbe) select Options then the view tab (I don't think this has changed over different versions) then untick the sheet tabs option.

I've tried running this code from every sheet in a workbook, the workbook itself and from a module in both xl97 & 2000 without a problem so I really can't see what's going wrong.

would you just copy this code and paste it into a module and let me know if it works

Sub test()
ActiveWindow.DisplayWorkbookTabs = False
End Sub

 

[monk999]

@dsi

Thx..

i managed to hide the workbook tabs,partly.Is it not possible to hide all of them?

 

[monk999]

@loomah

OK.Tx

it helped and now the worksheet tab is invisible.

tx again.

 

[dsi]

monk999:
It is an Excel requirement that you leave at least one worksheet visible.