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

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"
wrote in
message
...

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

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


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=564433

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

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

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.


--
ElsiePOA


------------------------------------------------------------------------
ElsiePOA's Profile: http://www.excelforum.com/member.php...fo&userid=3901
View this thread: http://www.excelforum.com/showthread...hreadid=564433

AntnyMI Wrote:
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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=564433