PDA

View Full Version : Solved: chart sheet and work sheet



jammer6_9
01-21-2009, 11:53 PM
How can I restrict user to see any worksheet or chart sheet hidden using the FORMAT-SHEET-UNHIDE toolbar using VBA...

GTO
01-22-2009, 12:09 AM
Greetings Jammer,

Just for example, let's say you give the chart and sheet codenames of 'chtHidden' and 'shtHidden'.

Sub HideEm()
Dim sh
For Each sh In ThisWorkbook.Sheets
If sh.CodeName = "chtHidden" _
Or sh.CodeName = "shtHidden" Then
sh.Visible = xlSheetVeryHidden
End If
Next
End Sub

Sub ShowEm()
Dim sh
For Each sh In ThisWorkbook.Sheets
sh.Visible = xlSheetVisible
Next
End Sub

Just an easy example of course, but the keypoint is to set the sheets' visibility to xlSheetVeryHidden (value = 2)

Hope this helps,

Mark

jammer6_9
01-22-2009, 07:51 AM
Greetings Jammer,

Just for example, let's say you give the chart and sheet codenames of 'chtHidden' and 'shtHidden'.

Sub HideEm()
Dim sh
For Each sh In ThisWorkbook.Sheets
If sh.CodeName = "chtHidden" _
Or sh.CodeName = "shtHidden" Then
sh.Visible = xlSheetVeryHidden
End If
Next
End Sub

Sub ShowEm()
Dim sh
For Each sh In ThisWorkbook.Sheets
sh.Visible = xlSheetVisible
Next
End Sub

Just an easy example of course, but the keypoint is to set the sheets' visibility to xlSheetVeryHidden (value = 2)

Hope this helps,

Mark

Thanks for the response... You gave me the idea... This works fine with me...


ThisWorkbook.Sheets("My Sheet").Visible = xlSheetVeryHidden