Help with VB error?

[pappyb]

Hello all. I hope this is in the right forum, but I'm having the hardest time understanding what I'm doing wrong with the following code. It's in an excel spreadsheet. If I leave the sheet unprotected, it works just fine.

If I protect the sheet and enter something in column b I get the following error:
Unable to set the NumberFormat property of hte Range Class

Can someone please help me to understand what I'm doing wrong? Thanks all!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B3:B100"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "mm/dd/yy"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[macropod]

Hi pappyb,

Since the worksheet is protected, you can't do anything involving a protected range in code. Instead of protecting the sheet, try:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
  If .Count > 1 Then Exit Sub
  If Not Intersect(Range("B3:B100"), .Cells) Is Nothing Then
    If IsEmpty(.Value) Then
      .Offset(0, -1).ClearContents
    Else
      With .Offset(0, -1)
        .NumberFormat = "mm/dd/yy"
        .Value = Date
      End With
    End If
  Else
    MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
    Application.Undo
  End If
End With
Application.EnableEvents = True
End Sub

Cheers
[MS MVP - Word]

 

[pappyb]

Excellent...your input is greatly appreciated!!! Thank you!

 

[macropod]

Hi pappyb,

Better change that code to:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
  If .Count > 1 Then Exit Sub
  Application.EnableEvents = False
  If Not Intersect(Range("B3:B100"), .Cells) Is Nothing Then
    If IsEmpty(.Value) Then
      .Offset(0, -1).ClearContents
    Else
      With .Offset(0, -1)
        .NumberFormat = "mm/dd/yy"
        .Value = Date
      End With
    End If
  Else
    MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
    Application.Undo
  End If
  Application.EnableEvents = True
End With
End Sub

Otherwise you might get a nasty surprise.


Cheers
[MS MVP - Word]