Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Allow Sort, Sorting on Protected Worksheets

Hi guys!
Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.
I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was

	VB:
	
Range("A2:S5000").Select 
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
ActiveWindow.ScrollRow = 2 
Range("D2").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the current excel document I am working on it runs from A6:F500 so I changed the range accordingly but alas no joy!!

thanks!!


Post your answer or comment

comments powered by Disqus
I have a table of cells on a protected worksheet with a filter on this range of cells.

The problem is when the worksheet is protected then I can not use the sort feature on the filter options because of the sheet being protected.

Is there something I can do to keep the sheet protected while sorrting the table.

Thanks
Adam.

Hi All

Is there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc

“The Range on each worksheet is the same ("A11:CR200")

	VB:
	
 Range("C11") 
And the  Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
Range("D11").Select” 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks

John

I'm not fond with macros, but this is what i need help with.

I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D)
This is what the macro to do. Unprotect, sort selected column, protect.

find on protected worksheet

i have a protected sheet (i.e., listing of items that make an order guide) that i would like to use the find funcition to locate an item, but on a protected sheet, the find doesn't work...?

???

thank you.

Hello guys,
I am using following code to Change all cells in the worksheet to values, however, this code is not working on protected worksheet?

With wb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False

Can somebody please help me with code to unprotect the file and then change the cells in the worksheet to values?

Thanks for help in advance
Ta

Excel 2003 worksheet
I have data in B1 being the header row through to Y366.
I have selected all cells and unlocked them. I have then selected B1:Y1 and
locked them as they are my header row.
I then went tools,protection,allow users to edit and input a range of
B2:Y366. did not want to give password permission, clicked apply and then
clicked protect sheet and checked unlocked cells and sort and then OK.
When I then go to sort data it tells me "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 the sheet command (Tools menu, Protection submenu)> You may be
prompted for a password.
What am I doing wrong. Should it not sort even though the protection is on
if I have a user range input?
Someone please help.
I did have columns hidden but they have all be displayed prior to the first
step as above.
I want it protected as I have formula's that will be tucked away on my
hidden columns that I don't want people to get to and wipe by mistake.
Thanks
Sue

Excel 2003 worksheet
I have data in B1 being the header row through to Y366.
I have selected all cells and unlocked them. I have then selected B1:Y1 and
locked them as they are my header row.
I then went tools,protection,allow users to edit and input a range of
B2:Y366. did not want to give password permission, clicked apply and then
clicked protect sheet and checked unlocked cells and sort and then OK.
When I then go to sort data it tells me "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 the sheet command (Tools menu, Protection submenu)> You may be
prompted for a password.
What am I doing wrong. Should it not sort even though the protection is on
if I have a user range input?
Someone please help.
I did have columns hidden but they have all be displayed prior to the first
step as above.
I want it protected as I have formula's that will be tucked away on my
hidden columns that I don't want people to get to and wipe by mistake.
Thanks
Sue

Operating excel 2003
I have an excel workbook which is protected but sort has been checked in the
protection box. This worksheet sorts column F into date of birth order.
Oldest at the top using A->Z. I have installed this workbook onto two other
computers at different sites and all sort beautifully in protected mode.
However, I have installed this workbook onto a 3rd computer (once again a
separate site) and it will not allow the sort to happen without unprotecting
the worksheet. All of these computers are running Windows XP and Office
2003. The workbook was designed using the same system and same Office
programs. I copied the non sorting workbook onto flash disk and brought it
back to my computer. That copy on the flash disk works perfectly fine on my
computer. Could there be something on that particular computer's Excel
program that has been fiddled with by the user to deny the sort on protected
worksheets as three out of four are operating fine. The owner is not able to
help.
Can anyone help. I have left it unprotected at the moment so it at least
will work for them but it leaves all formulas exposed for someone to wipe.
Any suggestions greatly appreciated.
Sue

I am working in Excel 2003.

I want to lock the values and formats of all of the cells on the page, but still be able to sort/use an autofilter.

To do this I went to Tools --> Protection --> Protect Sheet
Then I checked "protect worksheet and contents of locked cell" (it won't let me hit ok without checking that)
Then under "allow all users of this worksheet to:"
And I selected, "Select locked cells", "Select unlocked cells", "Sort" & "Use AutoFilter"

From my logic and what I have read online, this should allow me to sort in my worksheet....
However, when I highlight what I want to sort and select the sort, I get the error message:
"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."

What am I doing wrong?

Operating excel 2003
I have an excel workbook which is protected but sort has been checked in the
protection box. This worksheet sorts column F into date of birth order.
Oldest at the top using A->Z. I have installed this workbook onto two other
computers at different sites and all sort beautifully in protected mode.
However, I have installed this workbook onto a 3rd computer (once again a
separate site) and it will not allow the sort to happen without unprotecting
the worksheet. All of these computers are running Windows XP and Office
2003. The workbook was designed using the same system and same Office
programs. I copied the non sorting workbook onto flash disk and brought it
back to my computer. That copy on the flash disk works perfectly fine on my
computer. Could there be something on that particular computer's Excel
program that has been fiddled with by the user to deny the sort on protected
worksheets as three out of four are operating fine. The owner is not able to
help.
Can anyone help. I have left it unprotected at the moment so it at least
will work for them but it leaves all formulas exposed for someone to wipe.
Any suggestions greatly appreciated.
Sue

Hey all

I have workbook that when the user clicks to a worksheet tab a macro runs that does a custom sort on the previous sheet:


	VB:
	
) 
    Call data_global(Sh) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
macro data_global:


	VB:
	
 Worksheet) 
    With WS 
         
        .Range("B4:M34").Sort Key1:=.Range("B4"), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I'm looking for is a way to insert some code in the macro data_global to uprotect the last sheet that was activated, then after it sorts to reactivate protection on that last sheet.

Any ideas GREATLY appreciated as always! THANKS

Hello,

So, i've searched and searched this site to find the best way to do a simple sort on all worksheets within a workbook. I used to activate each sheet then sort it, one by one,...then i found the below loop method here but it's not working for me.

It sorts the ActiveSheet, but none of the other sheets and there's no runtime error. I am using this on a test workbook with the same data in 5 worksheets.

What's wrong with this code?


	VB:
	
 SortSheets() 
     
    Dim ws As Worksheet 
     
    For Each ws In Worksheets 
         
        Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ 
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
    Next ws 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This looping structure works for PageSetUp, but not this Sort.

I know how to allow / deny users to change cell contents on a protected
worksheet ("Allow all users of this worksheet to select unlocked cells"),
but unlocking check-boxes (on the Format Control / Protection tab) still
prevents users from changing the state of a check-box if the sheet is
protected, even if "Allow all users of this worksheet to select unlocked
cells" is selected.

Any ideas?

Thanks!

Chuck

Hi,

I've read a few of the other posts on allowing use of grouping in a protected worksheet but 'still' can't get it to work.

Here is my code. It switches between a 'partial' view of worksheet with some columns/rows grouped and a full view with all columns/rows ungrouped. I'm a beginner with VBA having learnt so far from this forum. Great ... awesome forum btw.


	VB:
	
 
Sub Partial_View() 
     
    Dim cap As String 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    ActiveSheet.Unprotect 
     
    ActiveSheet.DrawingObjects(6).Select 
     
    cap = Selection.Characters.Text 
     
    If cap = "Full View" Then 
         
        cap = "Partial View" 
         
        ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 
    Else 
         
        cap = "Full View" 
         
        ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 
         
    End If 
     
    Selection.Characters.Text = cap 
     
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    ActiveSheet.EnableOutlining = True 
    ActiveSheet.Protect 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas on how to make this work. I'm getting major frustration now.

Thanks,

Simon

have the macro unprotect then protect the sheet again each time it runs
--
paul
remove nospam for email addy!

"Lisa D'K" wrote:

> I have a protected worksheet that is located on a shared drive. Everyone
> accesses and uses it in a read-only format. I have protected various cells
> within the worksheet so that everyone can use tab to navigate only to the
> unprotcted cells that require input data. I created a macros to clear all of
> these unprotected cells at once so that the spreadsheet can be re-used easily
> with less error. I do not want to allow anyone to unprotect the worksheet,
> but they cannot run the macro in a protected status. Please help. Thanks.
> Excel 2002, Windows XP

I have a protected worksheet that is located on a shared drive. Everyone
accesses and uses it in a read-only format. I have protected various cells
within the worksheet so that everyone can use tab to navigate only to the
unprotcted cells that require input data. I created a macros to clear all of
these unprotected cells at once so that the spreadsheet can be re-used easily
with less error. I do not want to allow anyone to unprotect the worksheet,
but they cannot run the macro in a protected status. Please help. Thanks.
Excel 2002, Windows XP

Hi All,

I'm having a little bit of problem in here with using AutoFilters through VBA. So I would greatly aprieciate some help as i cant find a way around it.

My worksheet is protected to allow using of autofilters and i can use them as normal without any problems. When I'm trying to use autofilters through VBA it tells me that the command cannot be performed on the protected worksheet. Strange? Ir maybe it's normal? Im using first line to remove a filter and then the second one to apply it (refresh)

Range("A1").AutoFilter Field:=1 
Range("A1".AutoFilter Field:=1, Criteria:=<>

I was trying to unprotect sheet before the code and then protect it after but I decided that i would like the worksheet to be shared so it is not an option anymore. Any ideas?

Thanks for help in advance.

The issue I have is that I need to protect a worksheet to limit the cells that users can input values into however I want to enable them to hide/unhide columns if the wish to see more details.

Is there a way that you can have a protected worksheet but allow users to hide/unhide columns in Excel2000?

Hi, I am using Dave Hawley's code to allow grouping/ungrouping on a protected sheet:

	VB:
	
 Worksheet_Activate() 
    With Me 
        .Protect Password:="eb", UserInterFaceOnly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to add the option of inserting rows to the protected sheet using this code. I am using an Excel 2007 file. PLEASE HELP!!
Thank you,
Reayn.

I have a protected worksheet that allows users to manually input into
unprotected ranges, or manually copy from a protected range into an
unprotected area. Copy from cells have different font and background colors
that change the destination cell formats after paste even when sheet is
protected.

How do I restore the color formats of the destination cells after the
changes are processed by an executed macro?

The following works fine if sheet is unprotected, but gives error 1004 if
protected:
Range("destname").Interior.ColorIndex = 35
Range("destname").Font.ColorIndex = 1

Is there a way of skipping to the next input cell, on a protected
worksheet, when the enter/return key is pressed ?

I have a protected worksheet with a number of input cells. Because of
the layout there are rows in between the input cells, so when
enter/return is pressed the cell immediately below the current one is
selected, instead, I would like to move to the next input cell at this
point.

Thanks and regards
Fred

In Excel 2000: Using the below code to open a workbook with password
protected worksheets. Opens okay. When the user clicks the “X� Close Window
button, a warning message comes up:

Do you want to save the changes you made to ‘ICM A1.xls’? Two questions:

1. Why the message? The user cannot make changes on the protected worksheets.
2. Is there a way to bypass this message such that when the user clicks
Close Window, the workbook closes, according to the Auto_Close code?

Option Explicit

Sub Auto_Open()
Dim WS As Worksheet
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
Application.EnableEvents = False

For Each WS In ThisWorkbook.Worksheets
If WS.Visible = xlSheetVisible Then
WS.Select
Application.Goto WS.Range("A1"), True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.WindowState = xlMaximized
ActiveWindow.View = xlNormalView
End If
Next

Application.Goto Reference:=Range("A100"), Scroll:=False
Application.EnableEvents = True
End Sub

Sub Auto_Close()
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("A100"), Scroll:=False
Application.DisplayFullScreen = False
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHorizontalScrollBar = True
Application.ScreenUpdating = True
End Sub

I have a table that is in a protected worksheet. When the worksheet is not protected I can right-click in any of the cells and insert a new row. When I protect the worksheet, even with all allowable options checked, I cannot insert a new row in the table.

The only way I know to get a new row is to go to the last cell in the table where there is data and push the "tab" button, which will add a new row.

Is there a fix to my problem? Thank you.

Is there a way to insert a comment on an unprotected cell in a protected
worksheet. I am using Excel 2002 and when I right click on the cell the
comment option is not listed. If you go to Insert in the menu comment is
grayed out.

Thanks for any assistance...


No luck finding an answer? You could always try Google.