run a macro in a locked cell

[Guest]

I have a date macro. It automatically puts the date into the cell left of the
cell that I put data into.
I works fine if I don't lock the cell, but does nothing it it's locked.
Here is the macro:
____________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in Col C
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then 'can be modified, see below
n = Target.Row
If Excel.Range("C" & n).Value <> "" Then
Excel.Range("B" & n).Value = Date
Target.Offset(0, 1).NumberFormat = "mm-dd-yyyy"
End If
End If
enditall:
Application.EnableEvents = True
End Sub
__________________________________________________________________

Is there a workaround or something that I could add to the macro?

 

[Dave Peterson]

If it doesn't work with the cell locked, maybe it's because you have the
worksheet protected.

Add a line that unprotects the worksheet, makes the change and reprotects the
worksheet.

 

[Guest]

How do I unprotect and reprotect a cell with a macro?

 

[Dave Peterson]

You actually want to unprotect the worksheet -- not lock the cell.

If the cell is locked and the worksheet is protected, then the user can't change
that cell.

If you unprotect the worksheet, then it won't matter if the cell is locked or
unlocked.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in Col C
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then 'can be modified, see below
n = Target.Row
If Excel.Range("C" & n).Value <> "" Then
me.unprotect password:="YourPasswordHere"
Excel.Range("B" & n).Value = Date
Target.Offset(0, 1).NumberFormat = "mm-dd-yyyy"
me.protect password:="YourPasswordHere"
End If
End If
enditall:
Application.EnableEvents = True
End Sub

But looking at your code, it looks like you're putting the date in column B and
then formatting column D as a date.

From your original question, it looks like you want to use the cell to the left
(target.offset(0,-1)).

I'd use something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'when entering data in Col C
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then 'can be modified, see below
If Trim(Target.Value) <> "" Then
Me.Unprotect Password:="YourPasswordHere"
With Target.Offset(0, -1)
.NumberFormat = "mm-dd-yyyy"
.Value = Date
End With
Me.Protect Password:="YourPasswordHere"
End If
End If
enditall:
Application.EnableEvents = True
End Sub

 

[Guest]

Actually I put data into Column C, and the date is automatically entered in
Column B.
What's the difference between the 2 codes.
I'd rather use the more stable one. I'm not an expert (obviously), so if
you could, would you elaborate on the differences in the code.

I found the one I'm using here. It's actually a little variant that I
played with to get work.
I was intended for data input into column B, then date automatically input
into column A.
Also it used the NOW() function, which I changed to the date, and format. I
don't like the way NOW() changes things, pretty much if it feels like it, and
alters the date if I change the adjacent cell.

Any more information would be appreciated, and Thank You for your help.

 

[Dave Peterson]

These are the two portions that do the work:

n = Target.Row
If Excel.Range("C" & n).Value <> "" Then
me.unprotect password:="YourPasswordHere"
Excel.Range("B" & n).Value = Date
Target.Offset(0, 1).NumberFormat = "mm-dd-yyyy"
me.protect password:="YourPasswordHere"
End If

If Trim(Target.Value) <> "" Then
Me.Unprotect Password:="YourPasswordHere"
With Target.Offset(0, -1)
.NumberFormat = "mm-dd-yyyy"
.Value = Date
End With
Me.Protect Password:="YourPasswordHere"
End If

And remember that Target is the range that was changed.

Since you already checked to see if the change was in column C, then
"excel.range("C" & n)" is just refering to the range that you changed.

excel.range("B" & n) refers to column B of the same row.
but so does target.offset(0,-1).

Target.offset(0,1) doesn't refer to column B--it refers to the cell on column to
the right of the target (which is in column C), so it refers to column D.

One more version that I'd use if I were doing it:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'when entering data in Col C
Dim myPWD As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C:C")) Is Nothing Then
Exit Sub
End If

myPWD = "YourPassWordHere"

On Error GoTo enditall

Application.EnableEvents = False

Me.Unprotect Password:=myPWD
If Trim(Target.Value) = "" Then
Target.Offset(0, -1).ClearContents
Target.ClearContents
Else
With Target.Offset(0, -1)
.NumberFormat = "mm-dd-yyyy"
.Value = Date
End With
End If
Me.Protect Password:=myPWD

enditall:
Application.EnableEvents = True
End Sub

I check to make sure that only one cell is changed. If more than one, then get
out. (But you could do anything you wanted).

I like to use "intersect(target,me.range("c:c"))". I find it easier to read and
change when the range changes.

I put the password into a variable--so I only have to type it in correctly once.

If the user clears the cell in column C, then I wipe out the date in column B.
That may not be what you want though.

Except for the column B vs column D, most of this stuff is just a difference in
style, though.

Actually I put data into Column C, and the date is automatically entered in
Column B.
What's the difference between the 2 codes.
I'd rather use the more stable one. I'm not an expert (obviously), so if
you could, would you elaborate on the differences in the code.

I found the one I'm using here. It's actually a little variant that I
played with to get work.
I was intended for data input into column B, then date automatically input
into column A.
Also it used the NOW() function, which I changed to the date, and format. I
don't like the way NOW() changes things, pretty much if it feels like it, and
alters the date if I change the adjacent cell.

Any more information would be appreciated, and Thank You for your help.

 

[Guest]

You are right about the Target.Offset(0, 1), it doesn't put the date into
cell D, it just makes cell D format numbers with a date.
I removed the whole line "Target.Offset(0, 1).NumberFormat = "mm-dd-yyyy""

That second macro that you gave me, I got an error. So I just removed that
line above, and added the password lines in. It seems to be working.
I'll try that new one, and let you know how that goes too.

Also I'd like it to wipe out cell B if cell C is changed so that I can find
out if things are being modified. Will that macro below do that. I'm a
little confused on your wording.

Thank You Very
Much
Appreciated
Ray

 

[Dave Peterson]

You want to lose the date if column C changes?

Do you mean that if column B contains the date, then you want it removed. If
column B is empty, then add it?

Personally, I don't think I'd do it that way. I'd just change the value in
column B to the date and time. Then you can know when the last time the cell
was changed.

If that seems resonable:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'when entering data in Col C
Dim myPWD As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C:C")) Is Nothing Then
Exit Sub
End If

myPWD = "YourPassWordHere"

On Error GoTo enditall

Application.EnableEvents = False

Me.Unprotect Password:=myPWD
If Trim(Target.Value) = "" Then
Target.Offset(0, -1).ClearContents
Target.ClearContents
Else
With Target.Offset(0, -1)
.NumberFormat = "mm-dd-yyyy"
.Value = Date
End With
End If
Me.Protect Password:=myPWD

enditall:
Application.EnableEvents = True
End Sub


But I'm not sure why you had a problem with the code. It worked fine for me.

You are right about the Target.Offset(0, 1), it doesn't put the date into
cell D, it just makes cell D format numbers with a date.
I removed the whole line "Target.Offset(0, 1).NumberFormat = "mm-dd-yyyy""

That second macro that you gave me, I got an error. So I just removed that
line above, and added the password lines in. It seems to be working.
I'll try that new one, and let you know how that goes too.

Also I'd like it to wipe out cell B if cell C is changed so that I can find
out if things are being modified. Will that macro below do that. I'm a
little confused on your wording.

Thank You Very
Much
Appreciated
Ray

 

[Guest]

Ooops, it did work, my fault, cell wasn't wide enough. I was original using
the mm/dd format.
You've been a big help. Thank You.
Hopefully you'll be able to answer another question that I recently posted.