Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB macro ascending & decending Alpha sorting on protected sheet
ok, come on guys. I know some one can help me with this. This is a repost in
hopes that some one can help. I am trying to figure out how to allow a user to use the sort ascending and sort descending options that are displayed when the user clicks the drop down icon in a autofiltered cell. The other options (all, custom, top 10, blanks, non blanks etc..) work just fine. I am only having trouble "turning on" the ascending and descending sort options. Note that the worksheet IS protected. Here is an excerpt of my code.....thanks in advance for the help! Note that AutoFiltering is turned on.... sub Workbook_Open() Sheets("WORKLOG").Unprotect .. .. .. do stuff..... .. .. ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowHyperlinks:=True end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB macro ascending & decending Alpha sorting on protected sheet
statum,
One other requirement for sorting to be allowed on a protected sheet is that the cells that you want to allow to be sorted must be formatted as unlocked (format / protect tab, uncheck locked). HTH, Bernie MS Excel MVP "statum" wrote in message ... ok, come on guys. I know some one can help me with this. This is a repost in hopes that some one can help. I am trying to figure out how to allow a user to use the sort ascending and sort descending options that are displayed when the user clicks the drop down icon in a autofiltered cell. The other options (all, custom, top 10, blanks, non blanks etc..) work just fine. I am only having trouble "turning on" the ascending and descending sort options. Note that the worksheet IS protected. Here is an excerpt of my code.....thanks in advance for the help! Note that AutoFiltering is turned on.... sub Workbook_Open() Sheets("WORKLOG").Unprotect . . . do stuff..... . . ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowHyperlinks:=True end sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB macro ascending & decending Alpha sorting on protected shee
Thanks Bernie. However, that defeats the purpose of the sheet being
protected. I do not want the user changing anything. I only want them to have viewing, sorting and hyperlink priviledges. Writing code like: Range("A1:AA10000").locked=false unlocks the cells and allows the user to edit content. I want them to be able to sort alphabetically without having to unprotect or unlock any cells. I do not understand why this is not possible. If all other autofilter sorting options work, why don't these? After all, they are just sort options. "Bernie Deitrick" wrote: statum, One other requirement for sorting to be allowed on a protected sheet is that the cells that you want to allow to be sorted must be formatted as unlocked (format / protect tab, uncheck locked). HTH, Bernie MS Excel MVP "statum" wrote in message ... ok, come on guys. I know some one can help me with this. This is a repost in hopes that some one can help. I am trying to figure out how to allow a user to use the sort ascending and sort descending options that are displayed when the user clicks the drop down icon in a autofiltered cell. The other options (all, custom, top 10, blanks, non blanks etc..) work just fine. I am only having trouble "turning on" the ascending and descending sort options. Note that the worksheet IS protected. Here is an excerpt of my code.....thanks in advance for the help! Note that AutoFiltering is turned on.... sub Workbook_Open() Sheets("WORKLOG").Unprotect . . . do stuff..... . . ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowHyperlinks:=True end sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB macro ascending & decending Alpha sorting on protected shee
Yes it does defeat the purpose.... one way round would be to use macros to sort, which unlock, sort,
and relock the range. HTH, Bernie MS Excel MVP "statum" wrote in message ... Thanks Bernie. However, that defeats the purpose of the sheet being protected. I do not want the user changing anything. I only want them to have viewing, sorting and hyperlink priviledges. Writing code like: Range("A1:AA10000").locked=false unlocks the cells and allows the user to edit content. I want them to be able to sort alphabetically without having to unprotect or unlock any cells. I do not understand why this is not possible. If all other autofilter sorting options work, why don't these? After all, they are just sort options. "Bernie Deitrick" wrote: statum, One other requirement for sorting to be allowed on a protected sheet is that the cells that you want to allow to be sorted must be formatted as unlocked (format / protect tab, uncheck locked). HTH, Bernie MS Excel MVP "statum" wrote in message ... ok, come on guys. I know some one can help me with this. This is a repost in hopes that some one can help. I am trying to figure out how to allow a user to use the sort ascending and sort descending options that are displayed when the user clicks the drop down icon in a autofiltered cell. The other options (all, custom, top 10, blanks, non blanks etc..) work just fine. I am only having trouble "turning on" the ascending and descending sort options. Note that the worksheet IS protected. Here is an excerpt of my code.....thanks in advance for the help! Note that AutoFiltering is turned on.... sub Workbook_Open() Sheets("WORKLOG").Unprotect . . . do stuff..... . . ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowHyperlinks:=True end sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB macro ascending & decending Alpha sorting on protected shee
Right. If you notice from the code I provided, I am doing exactly that. The
user would need a way to start the macro simply, like a "click box". However, I cannot put a "click box" on the sheet that am I protecting. Also, why should I have to? The Autofilter drop down box already has the alpha sorts defined and displayed along with all the other sorting options. This makes no sense. A sort is a sort is a sort. "Bernie Deitrick" wrote: Yes it does defeat the purpose.... one way round would be to use macros to sort, which unlock, sort, and relock the range. HTH, Bernie MS Excel MVP "statum" wrote in message ... Thanks Bernie. However, that defeats the purpose of the sheet being protected. I do not want the user changing anything. I only want them to have viewing, sorting and hyperlink priviledges. Writing code like: Range("A1:AA10000").locked=false unlocks the cells and allows the user to edit content. I want them to be able to sort alphabetically without having to unprotect or unlock any cells. I do not understand why this is not possible. If all other autofilter sorting options work, why don't these? After all, they are just sort options. "Bernie Deitrick" wrote: statum, One other requirement for sorting to be allowed on a protected sheet is that the cells that you want to allow to be sorted must be formatted as unlocked (format / protect tab, uncheck locked). HTH, Bernie MS Excel MVP "statum" wrote in message ... ok, come on guys. I know some one can help me with this. This is a repost in hopes that some one can help. I am trying to figure out how to allow a user to use the sort ascending and sort descending options that are displayed when the user clicks the drop down icon in a autofiltered cell. The other options (all, custom, top 10, blanks, non blanks etc..) work just fine. I am only having trouble "turning on" the ascending and descending sort options. Note that the worksheet IS protected. Here is an excerpt of my code.....thanks in advance for the help! Note that AutoFiltering is turned on.... sub Workbook_Open() Sheets("WORKLOG").Unprotect . . . do stuff..... . . ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowHyperlinks:=True end sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 VB macro for Alpha-Sorting on a protected sheet | Excel Programming | |||
Data ascending and decending | Excel Discussion (Misc queries) | |||
sort data by criteria other than ascending or decending | Excel Worksheet Functions | |||
Custom Filter that Displays All But in Ascending or Decending Orde | Excel Discussion (Misc queries) | |||
Order Data in Pivot Table Non ascending or Decending | Excel Discussion (Misc queries) |