OrderThenFindEmptyCell

W

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
 
O

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.
 
M

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
 
W

Workbook

Hey thanks guys! This works great!

MCheru said:
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
Click to expand...
 
Top