Solved: how to hide sheets for posting

[Gingertrees]

Hello everyone,
I want to HIDE most of the sheets in my workbook in order to post it and get my questions answered. Problem: My workbook has a "prompt" sheet to ensure user enables macros - and that code gives the option of hiding everything or showing all but the prompt sheet.

Workbook has 26 sheets. For this purpose, I'd like to only have Sheets 1-5 visible when user opens the workbook. How can I convince the following code to do that? :

[vba]
' MODULE: ThisWorkbook
Option Explicit
Dim bolMyOverride As Boolean

'// BeforeClose and Open remain as you had them, as do the proedures 'HideSheets' and //
'// 'UnhideSheets'. //
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call HideSheets
.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub Workbook_Open()
'this is located in the ThisWorkbook module
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call UnhideSheets
.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub


Private Sub Workbook_Activate()

'// After you have run 'EnableStuffSoICanWork()', then the Boolean 'bolMyOverride' //
'// equals TRUE. //

'// So... assuming you've run the aforementioned sub and bolMyOverride has been set //
'// to True, the below test fails, and 'CutCopy_Disable' is never called. In short,//
'// as long as bolMyOverride retains a value of True, you can make mods w/o //
'// interference, as long as you don't reset. //
If Not bolMyOverride Then
'// Code moved to own sub //
Call CutCopy_Disable
End If
End Sub

Private Sub Workbook_Deactivate()

'// SAA //
If Not bolMyOverride Then
Call CutCopy_Enable
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Not bolMyOverride Then
With Application
.CellDragAndDrop = False
.CutCopyMode = False 'Clear clipboard
End With
End If

End Sub


Private Sub EnableStuffSoICanWork()
Call CutCopy_Enable
bolMyOverride = True
End Sub

Private Sub DisableStuffSoOthersCannotGooberUpMyDay()
Call CutCopy_Disable
bolMyOverride = False
'// Optional of course //
ThisWorkbook.Save
End Sub

Private Sub CutCopy_Disable()
Dim oCtrl As Office.CommandBarControl

'Disable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl

'Disable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl

Application.CellDragAndDrop = False
End Sub

Private Sub CutCopy_Enable()
Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl

'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl

Application.CellDragAndDrop = True
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
' If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
' Application.Goto Worksheets(1).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub
[/vba]

[Bob Phillips]

Why do you want to hide them in a post only?

[Gingertrees]

Because I keep running into things that are fixed when I post an abbreviated version of my workbook here, that then break when I put the same "fixed" version back in the original. I do not understand why, but cest la vie.

[Bob Phillips]

But hiding them won't change that in any way.

[Gingertrees]

xld: Yes I am aware that hiding sheets will not solve any coding problems.

Goal: make addresses, phone #s, certain forms SLIGHTLY less accessible. Yes, everybody with an internet connection still has the option of going in and undoing any xlsheetveryhidden, or Googling my organization or visiting us, but at least it would be a little less obvious.

E.g.: the nice solution provided to me by mdmackillop in this post:
http://www.vbaexpress.com/forum/showthread.php?t=27942
does not work when I paste the code into my actual workbook. As you yourself pointed out, I need to post the workbook.

Or am I just being too paranoid?

[Bob Phillips]

Not necessarily mate, if the data is confidential you have a duty to protect it. But you either post the whole workbook and accept we can see it, or you post a reduced version and maybe find that it doesn't properly show the problem. Either way, posting a workbook with hidden sheets might reduce your paranoia superficially, but the data is still posted, so you are just as exposed.

How about just obfuscating the data? It should be simple to create a little macro that goes through all names and changes them to say NAME1, NAME2, etc. Ditto telephone numbers.

[Gingertrees]

The forms themselves aren't exactly confidential - since we're a non-profit, US laws state intellectual property and copyright laws are kind of N/A for us. Nonetheless, I will do the honorable thing and create a macro. Which I hadn't thought of, thanks. :-)