Macro & Protected sheet
in [General]
|
Prev: ADDING COLUMNS FOR COMMON ROWS
Next: How do I remove a Calculated Field from the Pivot Table field list
From: Lise on 8 Apr 2010 21:39 Hello everyone I have a sheet which is protected so that cells cannot be changed and formulas are hidden which works well. However I also have a macro on this sheet which I still want people to be able to use. (see below) I have seen some answers already but seem to be doing something wrong - what do I need to add to the below to have the macro only run but keep the actual cells locked/protected? Many thanks Sub Sort() ' ' Sort Macro ' Macro recorded 17/03/2010 by Lisa Senior ' ' Keyboard Shortcut: Ctrl+o ' Columns("A:A").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Thanks as always Lise
From: Garreth Lombard on 9 Apr 2010 06:21 Hi Lisa, Paste the code below in a standard module --------------- Sub Sort_pr_range() ActiveSheet.Unprotect 'Unprotect the sheet Range("A1:F100").Sort Key1:=Range("B1"), _ '("A1:F100")would be your range Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom ActiveSheet.Protect 'Protect the sheet End Sub --------------- Hope it works for you -- Thank you and Regards Garreth Lombard "Lise" wrote: > Hello everyone > > I have a sheet which is protected so that cells cannot be changed and > formulas are hidden which works well. However I also have a macro on this > sheet which I still want people to be able to use. (see below) I have seen > some answers already but seem to be doing something wrong - what do I need to > add to the below to have the macro only run but keep the actual cells > locked/protected? > > Many thanks > > Sub Sort() > ' > ' Sort Macro > ' Macro recorded 17/03/2010 by Lisa Senior > ' > ' Keyboard Shortcut: Ctrl+o > ' > > Columns("A:A").Select > Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ > DataOption1:=xlSortNormal > > End Sub > -- > Thanks as always > > Lise
From: Lise on 11 Apr 2010 17:36
Sorry Gareth I must have missed a step as this still gives me the bug alert. I kept my CTRLO macro alive and added a module with your info below -- Any assistance would be great Thanks as always Lise "Garreth Lombard" wrote: > Hi Lisa, > > Paste the code below in a standard module > > --------------- > Sub Sort_pr_range() > ActiveSheet.Unprotect 'Unprotect the sheet > Range("A1:F100").Sort Key1:=Range("B1"), _ '("A1:F100")would be your range > Order1:=xlAscending, Header:=xlGuess, _ > OrderCustom:=1, MatchCase:=False, _ > Orientation:=xlTopToBottom > ActiveSheet.Protect 'Protect the sheet > End Sub > > --------------- > > Hope it works for you > > -- > > Thank you and Regards > > Garreth Lombard > > > "Lise" wrote: > > > Hello everyone > > > > I have a sheet which is protected so that cells cannot be changed and > > formulas are hidden which works well. However I also have a macro on this > > sheet which I still want people to be able to use. (see below) I have seen > > some answers already but seem to be doing something wrong - what do I need to > > add to the below to have the macro only run but keep the actual cells > > locked/protected? > > > > Many thanks > > > > Sub Sort() > > ' > > ' Sort Macro > > ' Macro recorded 17/03/2010 by Lisa Senior > > ' > > ' Keyboard Shortcut: Ctrl+o > > ' > > > > Columns("A:A").Select > > Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ > > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ > > DataOption1:=xlSortNormal > > > > End Sub > > -- > > Thanks as always > > > > Lise |