Command button will not work, despite include a line to unprotect and protect worksheet.

[Livin404]

Hello, I have a VBA that I think should work after the sheets are protect. When I select F5 or the Run/Sub arrow key the macro works as designed. It's only when I use the command botton, I get the error. I provided an image of the error and my macro. Thank you very much indeed.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="mozzer"
Dim xlSort As XlSortOrder
Dim LastRow As Long
With Sheets("Air Outbound")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If (.Range("A1").Value > .Range("A" & CStr(LastRow))) Then
xlSort = xlAscending
End If
.Range("A1") = "Date"
.Columns("A:I").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes
End With
ActiveWorkbook.RefreshAll
ActiveSheet.Protect Password:="mozzer", AllowFormattingColumns:=True, AllowInsertingHyperlinks:=True, AllowDeletingRows:=True
End Sub

 

[Logit]

.Range("A1").Value = "Date"

 

[Fluff]

Is the button on the Air outbound sheet?
If not you are un/protecting the wrong sheet

 

[Fluff]

Assuming the button is on another sheet, try

Private Sub CommandButton1_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
With Sheets("Air Outbound")
.Unprotect Password:="mozzer"

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If (.Range("A1").Value > .Range("A" & CStr(LastRow))) Then
xlSort = xlAscending
End If
.Range("A1") = "Date"
.Columns("A:I").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes
ActiveWorkbook.RefreshAll
.Protect Password:="mozzer", AllowFormattingColumns:=True, AllowInsertingHyperlinks:=True, AllowDeletingRows:=True

End With
End Sub

Also your If statement is redundant, so you can get rid of it.

 

[Livin404]

Thank you for the quick respondse. I'm afraid it did not change the end result. It was worth a try for sure.

 

[Livin404]

Assuming the button is on another sheet, try

Private Sub CommandButton1_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
With Sheets("Air Outbound")
.Unprotect Password:="mozzer"

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If (.Range("A1").Value > .Range("A" & CStr(LastRow))) Then
xlSort = xlAscending
End If
.Range("A1") = "Date"
.Columns("A:I").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes
ActiveWorkbook.RefreshAll
.Protect Password:="mozzer", AllowFormattingColumns:=True, AllowInsertingHyperlinks:=True, AllowDeletingRows:=True

End With
End Sub

Also your If statement is redundant, so you can get rid of it.

That works perfect, thank you so muich!

 

[Fluff]

You're welcome & thanks for the feedback.