Excel VBA Sorting Error

[Scott Halper]

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]

Check your first post.

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

 

[Bernie Deitrick]

Scott,

You cannot sort a worksheet - you can sort a range on a worksheet. So, change

.Sort

to

Selection.Sort


HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

untested but try this withOUT selections. Notice the . in front of
range("q7")
Private Sub NFP_Sort_Click()
Const PWORD As String = "123456"
Application.ScreenUpdating = False
With Worksheets("Southeast - NFP")
.Unprotect Password:=PWORD
.Range("NFP_Sort").Sort Key1:=.Range("Q7"), Order1:=xlDescending,
_
Header:=xlGuess,Orientation:=xlTopToBottom
.Protect Password:=PWORD
End With
Application.ScreenUpdating = TRUE
End Sub

 

[Don Guillett]

As Dave said, "check your first post". I will add "Patience is a VIRTUE"