Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hidden sheet access
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hidden sheet access
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 "Nigel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hidden sheet access
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("Un hide...") 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 "Peter T" <peter_t@discussions wrote in message ... 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 "Nigel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign Macro with Hidden Sheet | Excel Worksheet Functions | |||
Macro to run on hidden sheet | Excel Discussion (Misc queries) | |||
Using a Macro to look at Hidden Sheet | Excel Discussion (Misc queries) | |||
Can a macro format a hidden sheet? | Excel Discussion (Misc queries) | |||
Macro dependent on hidden sheet? | Excel Programming |