Password Protection

  • Thread starter Thread starter ElsiePOA
  • Start date Start date
E

ElsiePOA

I have a sheet that is password protected. Is it possible to write a
macro that will un-protect the sheet automatically, sort it and
re-protect it, entering the password again automatically? And, if it
is possible to do that, how can I password protect the macro, so that
the user can't access the sheet protection password?
 
Password protection is notoriously weak in Excel, but it will
keep the novice masses from changing things. Your code would look
like

Worksheets("Sheet1").Unprotect Password:="ABC"
' do the sort
Worksheets("Sheet1").Protect Password:="ABC"

You can password protect the VBA code (also very weak password
protection). In VBA, go to the Tools menu, choose VBA Project
Properties, then the Protection tab. Check "Lock project for
viewing" and enter a password.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"ElsiePOA"
message
news:[email protected]...
 
Sub Macro1()

Dim comment As String

comment = InputBox("Enter Sort Password")

If comment <> "12345" Then
MsgBox ("Invalid Password")
Exit Sub
End If

ActiveSheet.Unprotect Password:="55555"
Columns("A:E").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
ActiveSheet.Protect Password:="55555"

End Sub

Sort Password is set as 12345, Activesheet Password is set as 55555

It's selecting Columns A:E and sorting by Column A

Customize as you need to
 
Elsie, this worked for you?
I was able to get the various password prompts as instructed. However,
anyone who navigates to Tools|Macros|Edit will be able to see the
password typed in the code.

Have you figured out a way to disable access to the Macro menu unless
the correct password is entered?
 
Look at Chip Pearson's response to my inquiry. He tells you how to
protect the macros. The protection doesn't kick in until after you
have saved and closed the workbook. Next time you open it, all of the
macros for that workbook will be password protected.

Excel's protection is weak, but this will keep the average user from
accessing the protected data.
 
AntnyMI said:
Elsie, this worked for you?
I was able to get the various password prompts as instructed. However,
anyone who navigates to Tools|Macros|Edit will be able to see the
password typed in the code.

Have you figured out a way to disable access to the Macro menu unless
the correct password is entered?Hi AntnyMI,
Click to expand...

You can hide the macro name from being visible in th
'Tools|Macros|Edit',
by puting 'Option Private Module' at the top of the General modul
where your code resides
 
Back
Top