Yes, but you can add code to unprotect, and then reprotect, the sheet. Change "password" in the two
instances to the actual password.
And having the dim statements at the top is OK, and is actually required in this case - but you
should have nothing else except this code:
Dim ForceChange As Boolean
Dim myRow As Long
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myR As Range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Range("D:E,H:H"), Target) Is Nothing Then Exit Sub
Set myR = Intersect(Range("D:E,H:H"), Target.EntireRow)
If WorksheetFunction.CountBlank(myR.Areas(1)) + _
WorksheetFunction.CountBlank(myR.Areas(2)) > 0 Then
myRow = Target.Row
ForceChange = True
Else
ForceChange = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not ForceChange Or Target.Row = myRow Then Exit Sub
Application.EnableEvents = False
Target.Parent.Unprotect "password"
Intersect(Range("D:E,H:H"), Cells(myRow, 1).EntireRow) _
.SpecialCells(xlCellTypeBlanks)(1).Select
MsgBox "Please enter a value in cell " & Selection.Address
Target.Parent.Protect "password"
Application.EnableEvents = True
End Sub
--
HTH,
Bernie
MS Excel MVP
Jock said:
Hi Bernie,
The "Dim ForceChange As Boolean
Dim myRow As Long" part is outside the sub and gives an error message.
I already have a 'worksheet change' event on the sheet so I added your code
to it, but it didn't do anything.
The sheet is password protected - could that be the problem?
Thanks,