Excel VBA Sorting Error
 

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

Excel VBA Sorting Error
 

Check your first post.

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

Excel VBA Sorting Error
 

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


"Scott Halper" wrote in message
...
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

Excel VBA Sorting Error
 

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
Microsoft MVP Excel
SalesAid Software

"Scott Halper" wrote in message
...
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

Excel VBA Sorting Error
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Scott Halper" wrote in message
...
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