Macro for hidden sheet access

[Nigel]

Hi,

Can anyone help me with a macro for accessing a hidden sheet? i have a
hidden sheet named "Cost Data". i have this sheet hidden as it holds cost
information regarding projects that other people are not privy too. i set it
on a button to hide and another button with password to access it. However, i
have found a flaw which is the password only works as long as the VBA forms
are working. you have to close the form to enter certain data onto the sheet.
From this point, you can unhide the sheet.

would it be possible to show a password protected box when
"Format/Sheet/Unhide sheet" is selected from the menu bar and if the password
is incorrect, deny the sheet from being unhidden?

thanks in advance,


Nigel

 

[Peter T]

Hi Nigel,

AFAIK you cannot trap commandbar events other than your own custom menus.

Unhiding the sheet with "Format/Sheet/Unhide sheet" would trigger a
worksheet activate event. In this you could immediately unhide the sheet and
ask user for a password to unhide it. Store a worksheet level boolean to
store the state to get out of further activate events if visible is OK.

But why not hide the sheet as VeryHidden, user will not see it in
"Format/Sheet/Unhide sheet"

Try following attached to a button on another sheet. Note use of sheet
codename instead of sheet tab name, ie the one not in brackets in the vbe.
Sub Sheet2Visible()
Dim sPW As String
Dim sCap As String

If Sheet2.Visible = xlSheetVisible Then
Sheet2.Visible = xlSheetVeryHidden
sCap = "Unhide " & Sheet2.Name
Else
sPW = Application.InputBox("Enter password")
If sPW = "hello" Then
Sheet2.Visible = xlSheetVisible
sCap = "Hide " & Sheet2.Name
Else
MsgBox "incorrect password"
sCap = "Unhide Sheet2"
End If
End If
'On Error Resume Next
'assumes a forms button
ActiveSheet.Buttons(Application.Caller).Caption = sCap

End Sub

Regards,
Peter T

 

[Peter T]

AFAIK you cannot trap commandbar events other than your own custom menus.

Completely wrong, commandbar events can of course be trapped using
WithEvents in a Class.

For the OP's original purpose I would stick with my suggestion of hiding
sheets with xlSheetVeryHidden.

A bit late in the day but for the archives here's how could trap events for
Sheet/Unhide...

'' code in a class module named Class1

Option Explicit

Public WithEvents cbButton As CommandBarButton
Public sBookName As String

Public Property Let propWBname(s As String)
sBookName = s
End Property

Private Sub cbButton_Click(ByVal ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
Dim sht As Object
Dim sPW As String

If ActiveWorkbook.Name = sBookName Then
CancelDefault = True

sPW = Application.InputBox("Enter Unhide password")

If sPW = "hello" Then

For Each sht In ActiveWorkbook.Sheets
sht.Visible = xlSheetVisible
Next

Else

MsgBox "Not allowed to unhide sheets" & vbCr & _
"in " & sBookName
End If

End If

End Sub


'' code in a normal module
Dim ControlUnhide As Class1

Sub SetUnhide()
Dim ctrl As Object

On Error Resume Next
Set ctrl = Application.CommandBars(1) _
.Controls("Format").Controls("Sheet").Controls("Unhide...")
If Not ctrl Is Nothing Then

Set ControlUnhide = New Class1

Set ControlUnhide.cbButton = ctrl
ControlUnhide.propWBname = ActiveWorkbook.Name

Else
MsgBox "Unhide menu not showing 'cos no hidden sheets"
End If

End Sub

Sub DestroyClass1()
' best get rid of the class object when not needed,
' eg from workbook close event
Set ControlUnhide = Nothing
End Sub

Note, seems only possible to set a reference to the Unhide button if it's
not disabled in the activeworkbook, ie at least one sheet is not visible
which is not xlSheetVeryHidden. (I didn't look into the possibility of
setting a ref to the Unhide button via its ID rather than caption, maybe ?)

Regards,
Peter T