Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to password protect a worksheet

  Asked By: Howard    Date: Nov 20    Category: MS Office    Views: 829
  

I have my code mostly sorted.I have cells which i hide and unhide but
i need to password protect them.I can do this but i need to put some
error checking in because when someone enter the wrong password vba
goes to debug.Can anyone help?

Here is the code i have.

Private Sub ToggleButton1_Click()

'Locks Hiddens Cells to prevent accidental delete of values

If ToggleButton1.Value = True Then

'This area contains the things you want to happen
'when the toggle button is not depressed

ActiveSheet.Unprotect
Rows(6).EntireRow.Hidden = True
Rows(8).EntireRow.Hidden = True
Rows(10).EntireRow.Hidden = True
Rows(12).EntireRow.Hidden = True
Rows(14).EntireRow.Hidden = True
Rows(16).EntireRow.Hidden = True
Rows(18).EntireRow.Hidden = True
Rows(20).EntireRow.Hidden = True
Rows(22).EntireRow.Hidden = True
Rows(24).EntireRow.Hidden = True
Rows(26).EntireRow.Hidden = True
Rows(28).EntireRow.Hidden = True
Rows(30).EntireRow.Hidden = True
Rows(32).EntireRow.Hidden = True
Rows(34).EntireRow.Hidden = True
Rows(36).EntireRow.Hidden = True

'WEEK 1 LOCK
Range
("B6:F6,B8:F8,B10:F10,B12:F12,B14:F14,B16:F16,B18:F18,B20:F20,B22:F22,
B24:F24,B26:F26,B28:F28,B30:F30,B32:F32,B34:F34,B36:F36").Locked =
True
'WEEK 2 LOCK
Range
("I6:M6,I8:M8,I10:M10,I12:M12,I14:M14,I16:M16,I18:M18,I20:M20,I22:M22,
I24:M24,I26:M26,I28:M28,I30:M30,I32:M32,I34:M34,I36:M36").Locked =
True
'WEEK 3 LOCK
Range
("P6:T6,P8:T8,P10:T10,P12:T12,P14:T14,P16:T16,P18:T18,P20:T20,P22:T22,
P24:T24,P26:T26,P28:T28,P30:T30,P32:T32,P34:T34,P36:T36").Locked =
True
'WEEK 4 LOCK
Range
("W6:AA6,W8:AA8,W10:AA10,W12:AA12,W14:AA14,W16:AA16,W18:AA18,W20:AA20,
W22:AA22,W24:AA24,W26:AA26,W28:AA28,W30:AA30,W32:AA32,W34:AA34,W36:AA3
6").Locked = True
'WEEK 5 LOCK
Range
("AD6:AH6,AD8:AH8,AD10:AH10,AD12:AH12,AD14:AH14,AD16:AH16,AD18:AH18,AD
20:AH20,AD22:AH22,AD24:AH24,AD26:AH26,AD28:AH28,AD30:AH30,AD32:AH32,AD
34:AH34,AD36:AH36").Locked = True
'WEEK 6 LOCK
Range
("AK6:AO6,AK8:AO8,AK10:AO10,AK12:AO12,AK14:AO14,AK16:AO16,AK18:AO18,AK
20:AO20,AK22:AO22,AK24:AO24,AK26:AO26,AK28:AO28,AK30:AO30,AK32:AO32,AK
34:AO34,AK36:AO36").Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True





Else

'This area contains the things you want to happen
'when the toggle button is depressed

ActiveSheet.Unprotect
Rows(6).EntireRow.Hidden = False
Rows(8).EntireRow.Hidden = False
Rows(10).EntireRow.Hidden = False
Rows(12).EntireRow.Hidden = False
Rows(14).EntireRow.Hidden = False
Rows(16).EntireRow.Hidden = False
Rows(18).EntireRow.Hidden = False
Rows(20).EntireRow.Hidden = False
Rows(22).EntireRow.Hidden = False
Rows(24).EntireRow.Hidden = False
Rows(26).EntireRow.Hidden = False
Rows(28).EntireRow.Hidden = False
Rows(30).EntireRow.Hidden = False
Rows(32).EntireRow.Hidden = False
Rows(34).EntireRow.Hidden = False
Rows(36).EntireRow.Hidden = False

'WEEK 1 UNLOCK
Range
("B6:F6,B8:F8,B10:F10,B12:F12,B14:F14,B16:F16,B18:F18,B20:F20,B22:F22,
B24:F24,B26:F26,B28:F28,B30:F30,B32:F32,B34:F34,B36:F36").Locked =
False
'WEEK 2 UNLOCK
Range
("I6:M6,I8:M8,I10:M10,I12:M12,I14:M14,I16:M16,I18:M18,I20:M20,I22:M22,
I24:M24,I26:M26,I28:M28,I30:M30,I32:M32,I34:M34,I36:M36").Locked =
False
'WEEK 3 UNLOCK
Range
("P6:T6,P8:T8,P10:T10,P12:T12,P14:T14,P16:T16,P18:T18,P20:T20,P22:T22,
P24:T24,P26:T26,P28:T28,P30:T30,P32:T32,P34:T34,P36:T36").Locked =
False
'WEEK 4 UNLOCK
Range
("W6:AA6,W8:AA8,W10:AA10,W12:AA12,W14:AA14,W16:AA16,W18:AA18,W20:AA20,
W22:AA22,W24:AA24,W26:AA26,W28:AA28,W30:AA30,W32:AA32,W34:AA34,W36:AA3
6").Locked = False
'WEEK 5 UNLOCK
Range
("AD6:AH6,AD8:AH8,AD10:AH10,AD12:AH12,AD14:AH14,AD16:AH16,AD18:AH18,AD
20:AH20,AD22:AH22,AD24:AH24,AD26:AH26,AD28:AH28,AD30:AH30,AD32:AH32,AD
34:AH34,AD36:AH36").Locked = False
'WEEK 6 UNLOCK
Range
("AK6:AO6,AK8:AO8,AK10:AO10,AK12:AO12,AK14:AO14,AK16:AO16,AK18:AO18,AK
20:AO20,AK22:AO22,AK24:AO24,AK26:AO26,AK28:AO28,AK30:AO30,AK32:AO32,AK
34:AO34,AK36:AO36").Locked = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, AllowFormattingRows:=True


End If

End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Alfie Evans     Answered On: Nov 20

If you want to prevent  the user from going into VBA Debugger on an error, you
can also password  the VBA Project code  by right-clicking on "Microsoft Excel
Objects"
while you are in the VBA Editor. A panel will appear, then select the
"Protection" tab
and select the "Lock project for viewing" and also put  in a password for the
whole project.
Also, here is some VBA code to prevent a user getting(hacking) into VBA code.
--------------------------------------------------------------------------------\
-------------------------
Option Explicit
'//=====================================================================
'// Run DisV from Workbook_Open or Worksheet_Activate
'// THEN run EnaV from Workbook_Close or Worksheet_Deactivate
'//=====================================================================
'// Note:
'// In order to Disable access into the VBA Editor
'// you must Disable ALL references to Access the code
'// eg Macros...dialog, View Code - available via RighClick on Sheet Tab
'// Record New Macro..., Design Mode as the User can put the
'// workbook in design mode then select a control & double click to
'// view code, right click top Document area  etc
'// Also you need to Disable the Custom Toolbar List AND
'// the fact that Double clicking ANY area of the commandbars will
'// also give you the Customize Toolbars Option Dialog.
'// The following Routine Takes care of this.
Const dCustomize As Double = 797
Const dVbEditor As Double = 1695
Const dMacros As Double = 186
Const dRecordNewMacro As Double = 184
Const dViewCode As Double = 1561
Const dDesignMode As Double = 1605
Const dAssignMacro As Double = 859

--------------------------------------------------------------------------------\
-------------------------

Sub DisV()
Application.VBE.MainWindow.Visible = False '// Close ALL VBE Windows 1st!
CmdControl dCustomize, False '// Customize
CmdControl dVbEditor, False '// &Visual Basic Editor
CmdControl dMacros, False '// Macros...
CmdControl dRecordNewMacro, False '// Record New Macro...
CmdControl dViewCode, False '// View Code
CmdControl dDesignMode, False '// Design Mode
CmdControl dAssignMacro, False '// Assig&n Macro...
Application.OnDoubleClick = "Dummy"
Application.CommandBars("ToolBar List").Enabled = False
Application.OnKey "%{F11}", "Dummy"
End Sub

--------------------------------------------------------------------------------\
-------------------------

Sub EnaV()
CmdControl dCustomize, True '// Customize
CmdControl dVbEditor, True '// &Visual Basic Editor
CmdControl dMacros, True '// Macros...
CmdControl dRecordNewMacro, True '// Record New Macro...
CmdControl dViewCode, True '// View Code
CmdControl dDesignMode, True '// Design Mode
CmdControl dAssignMacro, True '// Assig&n Macro...
Application.OnDoubleClick = vbNullString
Application.CommandBars("ToolBar List").Enabled = True
Application.OnKey "%{F11}"
End Sub

--------------------------------------------------------------------------------\
-------------------------

Sub Dummy()
'// NoGo, leave blank OR Display a message eg.
10 'MsgBox "Sorry this command is NOT available", vbCritical
End Sub

'===============================================================================\
================================================
'-The following code is useful if you don't want a user to be able to edit the
'-Excel's main menu/commandbar by right clicking on it.
'===============================================================================\
================================================
'Purpose : Enables or disables Excel's main menu commandbar
'Inputs : [bEnable] If True enables the Excel's right click
menu, else disables it
'Outputs : N/A
'Notes :
'===============================================================================\
================================================
Function CommandBarRightClick(Optional bEnable As Boolean = False) As Boolean
On Error GoTo ErrFailed
CommandBars("toolbar List").Enabled = bEnable
If Not bEnable Then
CommandBars("toolbar List").Delete
CommandBarRightClick = False
Else
CommandBars("toolbar List").Reset
CommandBarRightClick = True
End If
Exit Function
ErrFailed:
Debug.Print "Error in CommandBarRightClick: " & Err.Description
CommandBarRightClick = False
End Function

--------------------------------------------------------------------------------\
-------------------------
Do this upon starting(opening) the Excel program such as in Private Sub
Workbook_Open()
--------------------------------------------------------------------------------\
-------------
Call DisV
Call CommandBarRightClick(False)

Do this upon exiting(closing) the Excel programPrivate Sub Workbook_Close()
--------------------------------------------------------------------------------\
-------------
Call EnaV
Call CommandBarRightClick(True)

 
Answer #2    Answered By: Tiaan Thompson     Answered On: Nov 20

VBA thinks "CmdControl" is an undefined Sub or Function. I
searched MSDN, but didn't get any hits on this "CmdControl" construct.
Perhaps it isn't part of Excel 97 that I use.

 
Didn't find what you were looking for? Find more on How to password protect a worksheet Or get search suggestion and latest updates.




Tagged: