Password Protection

[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?

 

[Chip Pearson]

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]...

 

[Bearacade]

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

 

[ElsiePOA]

Thanks to both Chip and Bearacade for the prompt response. The problem
is solved!

 

[AntnyMI]

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?

 

[ElsiePOA]

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.

 

[Desert Piranha]

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,

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