OrderThenFindEmptyCell
Hey thanks guys! This works great!
"MCheru" wrote:
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
"OssieMac" wrote:
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.
--
Regards,
OssieMac
"Workbook" wrote:
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
|