use macro button to run macro in protected sheet

[Guest]

I have a list of items that i want to sort. I am using a button to run a
macro to do this. I need to have this sheet password protected. When I
protect it and run the macro, it ask for a password. I will be sending this
to other people and want this macro to run without having to enter a password
but still have the sheet protected as it contains prices.

Thanks

 

[dominicb]

Good evening earl

Use:

ActiveSheet.Unprotect Password:="pass"

to unprotect the sheet, assuming the password to be "pass". You can
now run your sorting macro. Now use this line:

ActiveSheet.Protect Password:="pass"

to reprotect the sheet, setting the password back as "pass".

HTH

DominicB

 

[Guest]

Thanks dominicb
this solved my problem.

 

[Ron de Bruin]

This is another option
Old posting from Dave Peterson
*************************

xl2002 added some options to allow filtering and sorting of a protected
worksheet.

Until then, I think you'd have to give the user a way to sort the data--maybe a
macro that would unprotect the sheet, sort the data and then reprotect the
sheet.

But for the autofilter, you can protect the worksheet in code (if the autofilter
is already applied):

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm