Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Worksheet protection
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Worksheet protection
Resolved · Low Priority · Version 2003
Reply Thu 5 Jun 2008, 12:45Delegate Ben said...
Ben has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Worksheet protection
Is there a way to password protect each worksheet within a single workbook so that a sheet can only be viewed upon correct password input?
Thanks
For upcoming training course dates see: Pricing & availability
RE: Worksheet protection
Hi Ben
Thank you for your question.
I'm not aware of a feature in Excel that would allow you to do this. It may be possible to set up by writing VBA code.
I will transfer your question to the Excel VBA forum thread in case this can be done.
Kind regards
Amanda
RE: Worksheet protection
Hi Ben
Thank you for your question
The following code will provide a basic level of protection to each worksheet.
For this to work, you need to do the following
1. Enter the password for each sheet in cell "Q1" of that sheet. You can then hide the column to prevent casual observation of the password
2. Create a module in the VBA and then type in the following code
3. Create a worksheet called welcome. (This will be the sheet that the user will be directed to if they enter an incorrect password)
Sub Password()
Dim varPass As String
Dim varEntry As String
Cells.Select
Selection.EntireColumn.Hidden = True 'hide all the columns in the selected worksheet
varPass = Range("Q1").Value
If varPass = "" Then 'if there is no password for the sheet
MsgBox "This sheet has no password"
Cells.Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
Exit Sub
Else
varEntry = InputBox("Enter your Password") ' get password from user
If varEntry = varPass Then 'if ok
Cells.Select
Selection.EntireColumn.Hidden = False
Range("q1").EntireColumn.Hidden = True
Range("A1").Select
Exit Sub
Else
MsgBox ("Incorrect Password") 'if wrong
Selection.EntireColumn.Hidden = False
Cells.Select
Worksheets("Welcome").Select 'go back to welcome sheet
Range("q1").EntireColumn.Hidden = True
Range("A1").Select
End If
End If
End Sub
You must then call the procedure from the on activate event of each worksheet.
While this provides basic protection, there are a number of ways that a skilled person could get around it. If you have more detailed requirements then I suggest you contact our enquiries team, as you may require more formal consultancy
Regards
Stephen
Reply Mon 16 Jun 2008, 11:05Delegate Ben said...
RE: Worksheet protection
Thank you Stephen for this code. I will be really honest and say that although I did the VBA course, I have not had much opportunity to use it and I am having trouble with getting the code to run automatically upon selecting the worksheet. I am sure this is really basic, but a quick reminder would be really appreciated.
Thanks again,
Ben
RE: Worksheet protection
Hi Ben
Thanks for the follow up
If you open the VBE, in the project explorer window to the left you will see a list of all the worksheets in your workbook.
Double click on the first sheet and the code window will open for that sheet. At the top of the code window there are two combo boxes.
The left most one will probably say "general". Click on the combo and select "worksheet"
In the right most box select Activate.
This will then create a procedure that runs when the sheet is activated. In side this procedure simply type
Call Password , or whatever you called the procedure
You will then need to repeate this for each worksheet
Hope this helps. If not feel free to get back to me
Regards
Stephen
Reply Mon 16 Jun 2008, 11:42Delegate Ben said...
RE: Worksheet protection
Thanks for the quick reply.
I get a Compile Error. Is this because the Password sub is saved as a Module called "Password"
The Worksheet Activate code reads:
Private Sub Worksheet_Activate()
Call Password
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
RE: Worksheet protection
Hi Ben
The compile error could be caused by a number of things. T
The name of the module should not be a problem.
I suggest you check the spelling on your code. If you can't find an error, I suggest you email me the workbook and I'll hunt the bug down for you
My email address is stephenATmicrosofttraining.net
Regards
Stephen
RE: Worksheet protection
Hi Ben
Thank you for your follow up question, and subsequent supply of example file.
I didn't realise you had named the module Password, as opposed to just the procedure. This seems to be the problem.
I have renamed the module in your example file and it now works fine.
To rename the module select it in the explorer pane, and change its name in the properties pane, I called in Security. If the properties pane is not visible, click on View-Properties to activate it.
Hope this is working now. Any further problems, please don't hesitate to get in touch
Regards
Stephen Williams
Reply Mon 16 Jun 2008, 14:38Delegate Ben said...
RE: Worksheet protection
Thank you for your help. It now works.
Reply Mon 16 Jun 2008, 14:56Delegate Ben said...
RE: Worksheet protection
I will leave you alone soon I promise. When I open the book and choose to disable macros, I can freely access all sheets. Is there any way to get around this? The file will be used on various PCs.
RE: Worksheet protection
Hello Ben
Thank you for your posts.
We offer the forum service to all our delegates to answer questions related directly to content of courses they have attended with us.
While we are happy to support and advise our delegates in the manner that the forum was intended, there will be instances where we choose to advise delegates that we can only assist, or continue to assist, in a consultative role which would involve dedicated time from a trainer, and be billable.
This would apply to your post exchange with Stephen at this stage. If you are interested in taking this option forward to resolve any remaining issues with your spreadsheet, please contact our enquiries team to discuss your requirement.
Kind regards
Amanda
|