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?

[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

[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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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?
>
>
> --
> ElsiePOA
>
>
> ------------------------------------------------------------------------
> ElsiePOA's Profile:
> http://www.excelforum.com/member.php...fo&userid=3901
> View this thread:
> http://www.excelforum.com/showthread...hreadid=564433
>

[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 wrote:
> Thanks to both Chip and Bearacade for the prompt response. The problem
> is solved!
>
>
> --
> ElsiePOA
>
>
> ------------------------------------------------------------------------
> ElsiePOA's Profile: http://www.excelforum.com/member.php...fo&userid=3901
> View this thread: http://www.excelforum.com/showthread...hreadid=564433

[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 the 'Tools|Macros|Edit',
by puting 'Option Private Module' at the top of the General module where your code resides.