hiding sheets and sheet visibility

[cocopops2001]

is there anyway i can have pages that are 'hidden' but still allows the user to select them i am currently using a button? the

sheets(x).select

does not work when the sheets are hidden. not xlveryhidden just xlhidden.

i have also tried

 sheets(x).activate

to no avail. it goes to the next sheet in the line, ie when i want sheet 5 it goes to sheet 6.

any ideas?

 

[VoG]

You cannot select a hidden sheet. you'll need to unhide it first.

 

[mikerickson]

"is there anyway i can have pages that are 'hidden' but still allows the user to select"

If the user can select the sheets, how can they be called hidden?

 

[cocopops2001]

the workbook is essentially a database of pages in which the user can navigate through using buttons with macros assigned, i would loike the pages to be hidden so that there is not a long list of tabs along the bottom. so i will need to put in something like

Sub pipes_menu()
sheet(5).visible = xlvisible    
Sheets(5).select
End Sub

how do i make sure they hide again when the user navigates to a different page would something like

dim sheetnum as worksheet.index

set sheetnum as activesheet.index

sheet(sheetnum).visible = xlhidden

on each button work?

as long as it is before the unhide and select commands so that it is the previous page that is hidden?

what do you think?

 

[mikerickson]

Perhaps if you put this in the ThisWorkbook code module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim oneSheet As Worksheet
    
    For Each oneSheet In ThisWorkbook.Worksheets
        oneSheet.Visible = IIf(1 < Abs(oneSheet.Index - Sh.Index), xlSheetHidden, xlSheetVisible)
    Next oneSheet
End Sub

 

[cocopops2001]

is this basically testing if the sheet is being selected then checking its visibility settings and making it visible if it is not?

I have seen it somewhere before but what are the integers for visibility

1 xlvisible

0 xlhidden

-1 xlveryhidden

 

[VoG]

Try running this

Sub list()
MsgBox xlSheetVeryHidden & vbNewLine & xlSheetHidden & vbNewLine & xlSheetVisible
End Sub

 

[cocopops2001]

very good VoG, you obviously know your stuff. tried the code mike and sort of does what i want but not quite. this cycles through the sheets when really I want two sheets to be visible at all times and any other sheet visible only when it is selected.

ps i also have one sheet that is to stay xlveryhidden at all times. thanks

 

[cocopops2001]

no ideas on how to do this?

 

[Alpacino]

I've just don't something similar. I've just created macros to view sheets and macros to hide all and unhide all.
In module
Write:
Sub ViewSheet1()
Sheets("Sheet1").Visible = True
Sheets("Sheets1").Activate
End Sub

You can assign this to a obeject or button and this will transport the user to the sheet1 and unhide it.

To hide sheet just simply change = false to True

Maybe u can adapt this to ur situation
If u want certain sheets to stay visible stay leave them out of macro.

Hope this helps.