Hi: I have an Excel document that is protected. There are macros buttons on the page which contain un-password protect and then re-protecting at the end to allow it to perform certain functions for the end-user. However, when I try to sort ascending or descending, it does not allow it. What is the code for this?
You should be able to record a macro (after you've unprotected the sheet) to sort either ascending or descending.
Then you'll have the code for that portion.
Roady wrote:
> Hi: > I have an Excel document that is protected. There are macros buttons on the > page which contain un-password protect and then re-protecting at the end to > allow it to perform certain functions for the end-user. However, when I try > to sort ascending or descending, it does not allow it. What is the code for > this?
When protecting a sheet, there is actually a checkbox for SORT. I recorded a very quick macro, after the statement ActiveSheet.Protect you probably have several items here but you would want to include AllowSorting:=True -- ** John C **
"Roady" wrote: > Hi: > I have an Excel document that is protected. There are macros buttons on the > page which contain un-password protect and then re-protecting at the end to > allow it to perform certain functions for the end-user. However, when I try > to sort ascending or descending, it does not allow it. What is the code for > this?
I have done both of what you have said, however it gives me the following error message after being re-protected, "The cell or chart you are trying to change is protected and therefore read-only. to modify a protected cell or chart, first remove protection using the Unprotect Sheet command...etc." Just so I am clear, I want the user to be able to sort and/or filter AFTER the macro has been run and the sheet is re-protected. In the protect sheet check box selections, I do select 'allow sort' but somehow it never allows it. I have the following coding in my macro that should allow that but somehow does not work: ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True
Another weird thing is that even though in the check boxes I select 'Allow Column Formatting' and 'Allow Row Formatting', it unchecks it after re-protecting again. HELP! Thanks. :)
"Dave Peterson" wrote: > You should be able to record a macro (after you've unprotected the sheet) to > sort either ascending or descending.
> Then you'll have the code for that portion.
> Roady wrote:
> > Hi: > > I have an Excel document that is protected. There are macros buttons on the > > page which contain un-password protect and then re-protecting at the end to > > allow it to perform certain functions for the end-user. However, when I try > > to sort ascending or descending, it does not allow it. What is the code for > > this?
I have done both of what you have said, however it gives me the following error message after being re-protected, "The cell or chart you are trying to change is protected and therefore read-only. to modify a protected cell or chart, first remove protection using the Unprotect Sheet command...etc." Just so I am clear, I want the user to be able to sort and/or filter AFTER the macro has been run and the sheet is re-protected. In the protect sheet check box selections, I do select 'allow sort' but somehow it never allows it. I have the following coding in my macro that should allow that but somehow does not work: ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True
Another weird thing is that even though in the check boxes I select 'Allow Column Formatting' and 'Allow Row Formatting', it unchecks it after re-protecting again. HELP! Thanks. :)
"John C" wrote: > When protecting a sheet, there is actually a checkbox for SORT. I recorded a > very quick macro, after the statement > ActiveSheet.Protect > you probably have several items here but you would want to include > AllowSorting:=True > -- > ** John C **
> "Roady" wrote:
> > Hi: > > I have an Excel document that is protected. There are macros buttons on the > > page which contain un-password protect and then re-protecting at the end to > > allow it to perform certain functions for the end-user. However, when I try > > to sort ascending or descending, it does not allow it. What is the code for > > this?
> I have done both of what you have said, however it gives me the following > error message after being re-protected, "The cell or chart you are trying to > change is protected and therefore read-only. to modify a protected cell or > chart, first remove protection using the Unprotect Sheet command...etc." > Just so I am clear, I want the user to be able to sort and/or filter AFTER > the macro has been run and the sheet is re-protected. In the protect sheet > check box selections, I do select 'allow sort' but somehow it never allows > it. I have the following coding in my macro that should allow that but > somehow does not work: > ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True
> Another weird thing is that even though in the check boxes I select 'Allow > Column Formatting' and 'Allow Row Formatting', it unchecks it after > re-protecting again. HELP! Thanks. :)
> "John C" wrote:
> > When protecting a sheet, there is actually a checkbox for SORT. I recorded a > > very quick macro, after the statement > > ActiveSheet.Protect > > you probably have several items here but you would want to include > > AllowSorting:=True > > -- > > ** John C **
> > "Roady" wrote:
> > > Hi: > > > I have an Excel document that is protected. There are macros buttons on the > > > page which contain un-password protect and then re-protecting at the end to > > > allow it to perform certain functions for the end-user. However, when I try > > > to sort ascending or descending, it does not allow it. What is the code for > > > this?
Hi Dave: unfortunately, I can't make sure all the cells to be sorted are unlocked because then there is no point in protecting the document. The user would be able to delete out the contents of the cells which is what I need to prevent. Does that make sense?
I am not sure what macro I could create. I have about 32 columns with auto-filters across so would I then need to create a macro for each column? I don't want to create a macro that will simply unprotect, because that leaves the data too vulnerable. Ideas?
"Dave Peterson" wrote: > Make sure that all the cells in the range to be sorted are unlocked.
> Or just provide a macro that would unprotect the sheet, sort the data, reprotect > the sheet.
> Roady wrote:
> > Hi John and Dave:
> > I have done both of what you have said, however it gives me the following > > error message after being re-protected, "The cell or chart you are trying to > > change is protected and therefore read-only. to modify a protected cell or > > chart, first remove protection using the Unprotect Sheet command...etc." > > Just so I am clear, I want the user to be able to sort and/or filter AFTER > > the macro has been run and the sheet is re-protected. In the protect sheet > > check box selections, I do select 'allow sort' but somehow it never allows > > it. I have the following coding in my macro that should allow that but > > somehow does not work: > > ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True
> > Another weird thing is that even though in the check boxes I select 'Allow > > Column Formatting' and 'Allow Row Formatting', it unchecks it after > > re-protecting again. HELP! Thanks. :)
> > "John C" wrote:
> > > When protecting a sheet, there is actually a checkbox for SORT. I recorded a > > > very quick macro, after the statement > > > ActiveSheet.Protect > > > you probably have several items here but you would want to include > > > AllowSorting:=True > > > -- > > > ** John C **
> > > "Roady" wrote:
> > > > Hi: > > > > I have an Excel document that is protected. There are macros buttons on the > > > > page which contain un-password protect and then re-protecting at the end to > > > > allow it to perform certain functions for the end-user. However, when I try > > > > to sort ascending or descending, it does not allow it. What is the code for > > > > this?
> Hi Dave: unfortunately, I can't make sure all the cells to be sorted are > unlocked because then there is no point in protecting the document. The user > would be able to delete out the contents of the cells which is what I need to > prevent. Does that make sense?
> I am not sure what macro I could create. I have about 32 columns with > auto-filters across so would I then need to create a macro for each column? I > don't want to create a macro that will simply unprotect, because that leaves > the data too vulnerable. Ideas?
> "Dave Peterson" wrote:
> > Make sure that all the cells in the range to be sorted are unlocked.
> > Or just provide a macro that would unprotect the sheet, sort the data, reprotect > > the sheet.
> > Roady wrote:
> > > Hi John and Dave:
> > > I have done both of what you have said, however it gives me the following > > > error message after being re-protected, "The cell or chart you are trying to > > > change is protected and therefore read-only. to modify a protected cell or > > > chart, first remove protection using the Unprotect Sheet command...etc." > > > Just so I am clear, I want the user to be able to sort and/or filter AFTER > > > the macro has been run and the sheet is re-protected. In the protect sheet > > > check box selections, I do select 'allow sort' but somehow it never allows > > > it. I have the following coding in my macro that should allow that but > > > somehow does not work: > > > ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True
> > > Another weird thing is that even though in the check boxes I select 'Allow > > > Column Formatting' and 'Allow Row Formatting', it unchecks it after > > > re-protecting again. HELP! Thanks. :)
> > > "John C" wrote:
> > > > When protecting a sheet, there is actually a checkbox for SORT. I recorded a > > > > very quick macro, after the statement > > > > ActiveSheet.Protect > > > > you probably have several items here but you would want to include > > > > AllowSorting:=True > > > > -- > > > > ** John C **
> > > > "Roady" wrote:
> > > > > Hi: > > > > > I have an Excel document that is protected. There are macros buttons on the > > > > > page which contain un-password protect and then re-protecting at the end to > > > > > allow it to perform certain functions for the end-user. However, when I try > > > > > to sort ascending or descending, it does not allow it. What is the code for > > > > > this?
