Help with VB?

[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

 

[strongm]

>I hope this is in the right forum

Nope ...

Try here: forum707

 

[pappyb]

Thanks for the heads up!


I will post my question there.

 

[JoeAtWork]

Protecting a worksheet makes it read-only, so of course you can't modify it (either as a user or through code).

 

[JamiePocock]

You can use the VbFileAttribute commands to unwrite protect the file so you can do what you need to do then write protect it after you have finished.

SetAttr app.path & "/file.xml", VbFileAttribute.vbNormal
do events

your code here

do events
SetAttr app.path & "/file.xml", VbFileAttribute.vbReadOnly


Think this will work, been a few years since I used VB6