OrderThenFindEmptyCell

[Workbook]

I have this macro which I pasted below and it works great. However once I
lock cells J7:116 and L7:116, the macro no longer works. Do you know what I
can do differently so that I can run the macro below but still keep cells
J7:116 and L7:116 locked?

Sub OrderThenFindEmptyCell()
Dim BlankCell As Range
With Range("B7:B11")
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Order"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows)
BlankCell.Select
End With
End Sub

 

[OssieMac]

Unprotect the sheet at the start of the macro and protect it again at the end.

ActiveSheet.Unprotect ("MyPassword")

ActiveSheet.Protect ("MyPassword")

If you want any special parameters when protecting then record a macro to
get the syntax however, it does not record the password; you need to edit the
code to insert password.

 

[MCheru]

O I totally overlooked that one. I was concentrating on format, protection,
unlock cells part. Thanks for the tip you were right! I pasted the
completed working macro below.

Thank you again.

Sub OrderThenFindEmptyCell()
ActiveSheet.Unprotect

Dim BlankCell As Range
With Range("B7:B11")
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Order"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows)
BlankCell.Select
End With
ActiveSheet.Protect
End Sub

 

[Workbook]

Hey thanks guys! This works great!

O I totally overlooked that one. I was concentrating on format, protection,
unlock cells part. Thanks for the tip you were right! I pasted the
completed working macro below.

Thank you again.

Sub OrderThenFindEmptyCell()
ActiveSheet.Unprotect

Dim BlankCell As Range
With Range("B7:B11")
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Order"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows)
BlankCell.Select
End With
ActiveSheet.Protect
End Sub