Intermittent error.... help me make my script more stable.

[damienchew]

Hi below is my subroutine. What it does is to unlock a range in a column based on a cell being "empty" or "filled" with data.
So for example If F3 is empty F10 to F54 is locked, Else IF F3 has data that range is unlocked.

The problem is that the subroutine is unstable. Sometimes I get runtime error 13 and the debugger highlights the line

If Target.Value = "" Then


Here's the subroutine....

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F3:O3")) Is Nothing Then
If Target.Value = "" Then
Sheets(ThisWorkbook.ActiveSheet.Name).Unprotect Password:="1234" 'change to your actual password
Range(Cells(10, Target.Column), Cells(54, Target.Column)).ClearContents
Range(Cells(10, Target.Column), Cells(54, Target.Column)).Locked = True
Sheets(ThisWorkbook.ActiveSheet.Name).Protect Password:="1234"
Else
Sheets(ThisWorkbook.ActiveSheet.Name).Unprotect Password:="1234" 'change to your actual password
Range(Cells(10, Target.Column), Cells(54, Target.Column)).Locked = False
Sheets(ThisWorkbook.ActiveSheet.Name).Protect Password:="1234"
End If
End If

End Sub

[Olly]

Okay - first off, can you please wrap your code in CODE tags - makes it much easier to read, and to copy.

Now, to your problem - runtime error 13 shows a type mismatch, most likely caused when you have an error value in your target cell. Handle this by checking if the target cell is an error value.

I've also changed your code a little to deal with range changes of more than one cell at a time (multiple cell selection and delete, for example).

Please Login or Register  to view this content.

[Kaper]

The problem happens probably when the target is not a single cell

so may be you shall check every cell in possibly multi-cell Target one by one.

Please Login or Register  to view this content.

PS. Please note how indenting (just kindness to those reading) and using code tags (required by forum rules) improve readability of the code

[damienchew]

Hi All,

Firstly, let me apologise about the CODE TAGS... I couldn't figure out how to get them earlier.... I have been schooled since.
Thank for both the solutions above... both worked but I'll be using OllyXLS solution...

Thanks a bunch.