Visual basic unlock "SORT" function in an EXCEL spreadsheet
 

How do I add a string to enable the "SORT" function in the following
workbook?

My current string in the spreadsheet is as follows:

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets(Array("Key Controls", "NonKey
Controls"))
With ws
.Protect Password:="sox2005", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True
.EnableOutlining = True
End With
Next ws
End Sub

Visual basic unlock "SORT" function in an EXCEL spreadsheet
 

In Excel 2002 and later, it is an argument to the Protect methods.

In earlier versions, you can't enable it. However, you should be still be
able to sort with code using the UserInterfaceOnly argument.

--
Regards,
Tom Ogilvy

"Jetty" wrote in message
oups.com...
How do I add a string to enable the "SORT" function in the following
workbook?

My current string in the spreadsheet is as follows:

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets(Array("Key Controls", "NonKey
Controls"))
With ws
.Protect Password:="sox2005", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True
.EnableOutlining = True
End With
Next ws
End Sub

Visual basic unlock "SORT" function in an EXCEL spreadsheet
 

Thanks for your reply. I have Excel 2003 and
1) I did try to unprotect the file first
2) Placed a check mark in the sort box
3) Saved the file with the password
4) Closed the file
5) Re-Opened the file, enabling the macros
6) The sort function is still disabled

Any ideas?


Jetty

*** Sent via Developersdex http://www.developersdex.com ***

Visual basic unlock "SORT" function in an EXCEL spreadsheet
 

Here are the arguments to Protect:

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
AllowSorting, AllowFiltering, AllowUsingPivotTables)

You are not setting the AllowSorting argument in your code.

Here is what help says about that argument:

AllowSorting Optional Variant. True allows the user to sort on the
protected worksheet. Every cell in the sort range must be unlocked or
unprotected. The default value is False.


--
Regards,
Tom Ogilvy


"Jetty" wrote in message
...
Thanks for your reply. I have Excel 2003 and
1) I did try to unprotect the file first
2) Placed a check mark in the sort box
3) Saved the file with the password
4) Closed the file
5) Re-Opened the file, enabling the macros
6) The sort function is still disabled

Any ideas?


Jetty

*** Sent via Developersdex http://www.developersdex.com ***

Visual basic unlock "SORT" function in an EXCEL spreadsheet
 

Thanks Tom for your help! It works!
Jetty

*** Sent via Developersdex http://www.developersdex.com ***