Visual basic unlock "SORT" function in an EXCEL spreadsheet

  • Thread starter Thread starter Jetty
  • Start date Start date
J

Jetty

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
 
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.
 
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
 
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.
 
Back
Top