I have a file that I want the user to click an insert button and not
be able to insert rows. I have protected the sheet, except for the
area they can enter data into and have created another Sort button.
My code keeps errors at the sort stage. Any help is appreciated.

Private Sub NFP_Sort_Click()
Const PWORD As String = "123456"
Application.ScreenUpdating = False
With Worksheets("Southeast - NFP")
.Select
.Unprotect Password:=PWORD
.Range("NFP_Sort").Select
.Sort Key1:=Range("Q7"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
.Range("Total_NFP").Select
.Protect Password:=PWORD
End With
Application.ScreenUpdating = False
End Sub

Thanks,
Scott

your .Sort statement is within the bounds of the With Worksheets() block. In
other words you are essentially doing

Worksheets("Southeast - NFP").Sort ...

when you mean to be doing

Range("NFP_Sort").Sort

"Scott Halper" wrote:

I have a file that I want the user to click an insert button and not
be able to insert rows. I have protected the sheet, except for the
area they can enter data into and have created another Sort button.
My code keeps errors at the sort stage. Any help is appreciated.

Private Sub NFP_Sort_Click()
Const PWORD As String = "123456"
Application.ScreenUpdating = False
With Worksheets("Southeast - NFP")
.Select
.Unprotect Password:=PWORD
.Range("NFP_Sort").Select
.Sort Key1:=Range("Q7"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
.Range("Total_NFP").Select
.Protect Password:=PWORD
End With
Application.ScreenUpdating = False
End Sub

Thanks,
Scott

If this code is behind a worksheet (not in a general module), then the
range("q7") will refer to the worksheet with the button--not the "southeast -
nfp" worksheet.

Option Explicit
Private Sub NFP_Sort_Click()
Const PWORD As String = "123456"
Application.ScreenUpdating = False
With Worksheets("Southeast - NFP")
.Select
.Unprotect Password:=PWORD
.Range("NFP_Sort").Sort _
Key1:=.Range("Q7"), Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.Range("Total_NFP").Select
.Protect Password:=PWORD
End With
Application.ScreenUpdating = False
End Sub

You may want to drop both the .select lines. They're not really necessary to do
the sort.

Scott Halper wrote:

I have a file that I want the user to click an insert button and not
be able to insert rows. I have protected the sheet, except for the
area they can enter data into and have created another Sort button.
My code keeps errors at the sort stage. Any help is appreciated.

Private Sub NFP_Sort_Click()
Const PWORD As String = "123456"
Application.ScreenUpdating = False
With Worksheets("Southeast - NFP")
.Select
.Unprotect Password:=PWORD
.Range("NFP_Sort").Select
.Sort Key1:=Range("Q7"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
.Range("Total_NFP").Select
.Protect Password:=PWORD
End With
Application.ScreenUpdating = False
End Sub

Thanks,
Scott

--

Dave Peterson