PDA

View Full Version : running a macro on an otherwise protected workbook



K. Georgiadis
03-12-2009, 11:45 AM
Hi folks,

The workbook in question is protected globally with a ProtectAll() macro and the user is not savvy enough to mess with VBA.

In one of the worksheets there is a profitability ranking of products, from the most profitable to the least profitable. As the user makes entries in different sections of the workbook, these rankings will change and the ranking table will need re-sorting. I am planning to add a macro button next to the table that will re-sort the table as needed without the user having to get involved in unprotecting/re-protecting the workbook.

My question is this: am I limited to (1) having to program the macro button so that it temporarily unprotects the worksheet and re-protects it after the re-sort or (2) is there an exception that I can add to the ProtectAll routine in the module which will allow the re-sort macro to run without having to run a temporary unprotect?

Many thanks!

lucas
03-12-2009, 12:41 PM
I don't think so.

K. Georgiadis
03-12-2009, 12:51 PM
I am sorry, I don't follow. If there is an exception that I can add to the UnprotectAll() subroutine, would you let me know what it reads like?

Thanks.

lucas
03-12-2009, 01:09 PM
I believe you will have to unprotect before you can sort.

mdmackillop
03-12-2009, 05:46 PM
This workbook (2003) should allow user input etc. but no sorting. Using UserInterfaceOPnly in the Protect macro

Private Sub CommandButton1_Click()
Range("C4:F35").Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

Private Sub CommandButton2_Click()
ActiveSheet.Protect userinterfaceonly:=True, Password:="pw"
End Sub