software

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(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
Excel.Range(quot;Bquot; amp; n).Value = Date
Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
End If
End If
enditall:
Application.EnableEvents = True
End Sub
__________________________________________________ ________________

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

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.

Ray wrote:
gt;
gt; I have a date macro. It automatically puts the date into the cell left of the
gt; cell that I put data into.
gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; Here is the macro:
gt; __________________________________________________ __________
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; 'when entering data in Col C
gt; On Error GoTo enditall
gt; Application.EnableEvents = False
gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; n = Target.Row
gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; End If
gt; End If
gt; enditall:
gt; Application.EnableEvents = True
gt; End Sub
gt; __________________________________________________ ________________
gt;
gt; Is there a workaround or something that I could add to the macro?

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; worksheet protected.
gt;
gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; worksheet.
gt;
gt; Ray wrote:
gt; gt;
gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; cell that I put data into.
gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; Here is the macro:
gt; gt; __________________________________________________ __________
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; 'when entering data in Col C
gt; gt; On Error GoTo enditall
gt; gt; Application.EnableEvents = False
gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; n = Target.Row
gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; End If
gt; gt; End If
gt; gt; enditall:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt; __________________________________________________ ________________
gt; gt;
gt; gt; Is there a workaround or something that I could add to the macro?
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
me.unprotect password:=quot;YourPasswordHerequot;
Excel.Range(quot;Bquot; amp; n).Value = Date
Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
me.protect password:=quot;YourPasswordHerequot;
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) lt;gt; quot;quot; Then
Me.Unprotect Password:=quot;YourPasswordHerequot;
With Target.Offset(0, -1)
.NumberFormat = quot;mm-dd-yyyyquot;
.Value = Date
End With
Me.Protect Password:=quot;YourPasswordHerequot;
End If
End If
enditall:
Application.EnableEvents = True
End Sub
Ray wrote:
gt;
gt; How do I unprotect and reprotect a cell with a macro?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; gt; worksheet protected.
gt; gt;
gt; gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; gt; worksheet.
gt; gt;
gt; gt; Ray wrote:
gt; gt; gt;
gt; gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; gt; cell that I put data into.
gt; gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; gt; Here is the macro:
gt; gt; gt; __________________________________________________ __________
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; 'when entering data in Col C
gt; gt; gt; On Error GoTo enditall
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; n = Target.Row
gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; enditall:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt; __________________________________________________ ________________
gt; gt; gt;
gt; gt; gt; Is there a workaround or something that I could add to the macro?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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.

quot;Dave Petersonquot; wrote:

gt; You actually want to unprotect the worksheet -- not lock the cell.
gt;
gt; If the cell is locked and the worksheet is protected, then the user can't change
gt; that cell.
gt;
gt; If you unprotect the worksheet, then it won't matter if the cell is locked or
gt; unlocked.
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; 'when entering data in Col C
gt; On Error GoTo enditall
gt; Application.EnableEvents = False
gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; n = Target.Row
gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; me.protect password:=quot;YourPasswordHerequot;
gt; End If
gt; End If
gt; enditall:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; But looking at your code, it looks like you're putting the date in column B and
gt; then formatting column D as a date.
gt;
gt; From your original question, it looks like you want to use the cell to the left
gt; (target.offset(0,-1)).
gt;
gt; I'd use something like:
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; 'when entering data in Col C
gt; On Error GoTo enditall
gt; Application.EnableEvents = False
gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; With Target.Offset(0, -1)
gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; .Value = Date
gt; End With
gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; End If
gt; End If
gt; enditall:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt;
gt;
gt; Ray wrote:
gt; gt;
gt; gt; How do I unprotect and reprotect a cell with a macro?
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; gt; gt; worksheet protected.
gt; gt; gt;
gt; gt; gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; gt; gt; worksheet.
gt; gt; gt;
gt; gt; gt; Ray wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; gt; gt; cell that I put data into.
gt; gt; gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; gt; gt; Here is the macro:
gt; gt; gt; gt; __________________________________________________ __________
gt; gt; gt; gt;
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt; enditall:
gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt; __________________________________________________ ________________
gt; gt; gt; gt;
gt; gt; gt; gt; Is there a workaround or something that I could add to the macro?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

These are the two portions that do the work:

n = Target.Row
If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
me.unprotect password:=quot;YourPasswordHerequot;
Excel.Range(quot;Bquot; amp; n).Value = Date
Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
me.protect password:=quot;YourPasswordHerequot;
End If

If Trim(Target.Value) lt;gt; quot;quot; Then
Me.Unprotect Password:=quot;YourPasswordHerequot;
With Target.Offset(0, -1)
.NumberFormat = quot;mm-dd-yyyyquot;
.Value = Date
End With
Me.Protect Password:=quot;YourPasswordHerequot;
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
quot;excel.range(quot;Cquot; amp; n)quot; is just refering to the range that you changed.

excel.range(quot;Bquot; amp; 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 gt; 1 Then Exit Sub
If Intersect(Target, Me.Range(quot;C:Cquot;)) Is Nothing Then
Exit Sub
End If

myPWD = quot;YourPassWordHerequot;

On Error GoTo enditall

Application.EnableEvents = False

Me.Unprotect Password:=myPWD
If Trim(Target.Value) = quot;quot; Then
Target.Offset(0, -1).ClearContents
Target.ClearContents
Else
With Target.Offset(0, -1)
.NumberFormat = quot;mm-dd-yyyyquot;
.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 quot;intersect(target,me.range(quot;c:cquot;))quot;. 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.Ray wrote:
gt;
gt; Actually I put data into Column C, and the date is automatically entered in
gt; Column B.
gt; What's the difference between the 2 codes.
gt; I'd rather use the more stable one. I'm not an expert (obviously), so if
gt; you could, would you elaborate on the differences in the code.
gt;
gt; I found the one I'm using here. It's actually a little variant that I
gt; played with to get work.
gt; I was intended for data input into column B, then date automatically input
gt; into column A.
gt; Also it used the NOW() function, which I changed to the date, and format. I
gt; don't like the way NOW() changes things, pretty much if it feels like it, and
gt; alters the date if I change the adjacent cell.
gt;
gt; Any more information would be appreciated, and Thank You for your help.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; You actually want to unprotect the worksheet -- not lock the cell.
gt; gt;
gt; gt; If the cell is locked and the worksheet is protected, then the user can't change
gt; gt; that cell.
gt; gt;
gt; gt; If you unprotect the worksheet, then it won't matter if the cell is locked or
gt; gt; unlocked.
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; 'when entering data in Col C
gt; gt; On Error GoTo enditall
gt; gt; Application.EnableEvents = False
gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; n = Target.Row
gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; me.protect password:=quot;YourPasswordHerequot;
gt; gt; End If
gt; gt; End If
gt; gt; enditall:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; But looking at your code, it looks like you're putting the date in column B and
gt; gt; then formatting column D as a date.
gt; gt;
gt; gt; From your original question, it looks like you want to use the cell to the left
gt; gt; (target.offset(0,-1)).
gt; gt;
gt; gt; I'd use something like:
gt; gt;
gt; gt; Option Explicit
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; 'when entering data in Col C
gt; gt; On Error GoTo enditall
gt; gt; Application.EnableEvents = False
gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; gt; With Target.Offset(0, -1)
gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; .Value = Date
gt; gt; End With
gt; gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; gt; End If
gt; gt; End If
gt; gt; enditall:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt;
gt; gt; Ray wrote:
gt; gt; gt;
gt; gt; gt; How do I unprotect and reprotect a cell with a macro?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; gt; gt; gt; worksheet protected.
gt; gt; gt; gt;
gt; gt; gt; gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; gt; gt; gt; worksheet.
gt; gt; gt; gt;
gt; gt; gt; gt; Ray wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; gt; gt; gt; cell that I put data into.
gt; gt; gt; gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; gt; gt; gt; Here is the macro:
gt; gt; gt; gt; gt; __________________________________________________ __________
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; enditall:
gt; gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; __________________________________________________ ________________
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there a workaround or something that I could add to the macro?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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 quot;Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;quot;

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
quot;Dave Petersonquot; wrote:

gt; These are the two portions that do the work:
gt;
gt; n = Target.Row
gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; me.protect password:=quot;YourPasswordHerequot;
gt; End If
gt;
gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; With Target.Offset(0, -1)
gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; .Value = Date
gt; End With
gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; End If
gt;
gt; And remember that Target is the range that was changed.
gt;
gt; Since you already checked to see if the change was in column C, then
gt; quot;excel.range(quot;Cquot; amp; n)quot; is just refering to the range that you changed.
gt;
gt; excel.range(quot;Bquot; amp; n) refers to column B of the same row.
gt; but so does target.offset(0,-1).
gt;
gt; Target.offset(0,1) doesn't refer to column B--it refers to the cell on column to
gt; the right of the target (which is in column C), so it refers to column D.
gt;
gt; One more version that I'd use if I were doing it:
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; 'when entering data in Col C
gt; Dim myPWD As String
gt;
gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; If Intersect(Target, Me.Range(quot;C:Cquot;)) Is Nothing Then
gt; Exit Sub
gt; End If
gt;
gt; myPWD = quot;YourPassWordHerequot;
gt;
gt; On Error GoTo enditall
gt;
gt; Application.EnableEvents = False
gt;
gt; Me.Unprotect Password:=myPWD
gt; If Trim(Target.Value) = quot;quot; Then
gt; Target.Offset(0, -1).ClearContents
gt; Target.ClearContents
gt; Else
gt; With Target.Offset(0, -1)
gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; .Value = Date
gt; End With
gt; End If
gt; Me.Protect Password:=myPWD
gt;
gt; enditall:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; I check to make sure that only one cell is changed. If more than one, then get
gt; out. (But you could do anything you wanted).
gt;
gt; I like to use quot;intersect(target,me.range(quot;c:cquot;))quot;. I find it easier to read and
gt; change when the range changes.
gt;
gt; I put the password into a variable--so I only have to type it in correctly once.
gt;
gt; If the user clears the cell in column C, then I wipe out the date in column B.
gt; That may not be what you want though.
gt;
gt; Except for the column B vs column D, most of this stuff is just a difference in
gt; style, though.
gt;
gt;
gt; Ray wrote:
gt; gt;
gt; gt; Actually I put data into Column C, and the date is automatically entered in
gt; gt; Column B.
gt; gt; What's the difference between the 2 codes.
gt; gt; I'd rather use the more stable one. I'm not an expert (obviously), so if
gt; gt; you could, would you elaborate on the differences in the code.
gt; gt;
gt; gt; I found the one I'm using here. It's actually a little variant that I
gt; gt; played with to get work.
gt; gt; I was intended for data input into column B, then date automatically input
gt; gt; into column A.
gt; gt; Also it used the NOW() function, which I changed to the date, and format. I
gt; gt; don't like the way NOW() changes things, pretty much if it feels like it, and
gt; gt; alters the date if I change the adjacent cell.
gt; gt;
gt; gt; Any more information would be appreciated, and Thank You for your help.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; You actually want to unprotect the worksheet -- not lock the cell.
gt; gt; gt;
gt; gt; gt; If the cell is locked and the worksheet is protected, then the user can't change
gt; gt; gt; that cell.
gt; gt; gt;
gt; gt; gt; If you unprotect the worksheet, then it won't matter if the cell is locked or
gt; gt; gt; unlocked.
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; 'when entering data in Col C
gt; gt; gt; On Error GoTo enditall
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; n = Target.Row
gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; me.protect password:=quot;YourPasswordHerequot;
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; enditall:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; But looking at your code, it looks like you're putting the date in column B and
gt; gt; gt; then formatting column D as a date.
gt; gt; gt;
gt; gt; gt; From your original question, it looks like you want to use the cell to the left
gt; gt; gt; (target.offset(0,-1)).
gt; gt; gt;
gt; gt; gt; I'd use something like:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; 'when entering data in Col C
gt; gt; gt; On Error GoTo enditall
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; gt; gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; gt; gt; With Target.Offset(0, -1)
gt; gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; .Value = Date
gt; gt; gt; End With
gt; gt; gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; enditall:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Ray wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; How do I unprotect and reprotect a cell with a macro?
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; gt; gt; gt; gt; worksheet protected.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; gt; gt; gt; gt; worksheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Ray wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; gt; gt; gt; gt; cell that I put data into.
gt; gt; gt; gt; gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; gt; gt; gt; gt; Here is the macro:
gt; gt; gt; gt; gt; gt; __________________________________________________ __________
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; enditall:
gt; gt; gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt; __________________________________________________ ________________
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Is there a workaround or something that I could add to the macro?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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 gt; 1 Then Exit Sub
If Intersect(Target, Me.Range(quot;C:Cquot;)) Is Nothing Then
Exit Sub
End If

myPWD = quot;YourPassWordHerequot;

On Error GoTo enditall

Application.EnableEvents = False

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

enditall:
Application.EnableEvents = True
End SubBut I'm not sure why you had a problem with the code. It worked fine for me.
Ray wrote:
gt;
gt; You are right about the Target.Offset(0, 1), it doesn't put the date into
gt; cell D, it just makes cell D format numbers with a date.
gt; I removed the whole line quot;Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;quot;
gt;
gt; That second macro that you gave me, I got an error. So I just removed that
gt; line above, and added the password lines in. It seems to be working.
gt; I'll try that new one, and let you know how that goes too.
gt;
gt; Also I'd like it to wipe out cell B if cell C is changed so that I can find
gt; out if things are being modified. Will that macro below do that. I'm a
gt; little confused on your wording.
gt;
gt; Thank You Very
gt; Much
gt; Appreciated
gt; Ray
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; These are the two portions that do the work:
gt; gt;
gt; gt; n = Target.Row
gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; me.protect password:=quot;YourPasswordHerequot;
gt; gt; End If
gt; gt;
gt; gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; gt; With Target.Offset(0, -1)
gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; .Value = Date
gt; gt; End With
gt; gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; gt; End If
gt; gt;
gt; gt; And remember that Target is the range that was changed.
gt; gt;
gt; gt; Since you already checked to see if the change was in column C, then
gt; gt; quot;excel.range(quot;Cquot; amp; n)quot; is just refering to the range that you changed.
gt; gt;
gt; gt; excel.range(quot;Bquot; amp; n) refers to column B of the same row.
gt; gt; but so does target.offset(0,-1).
gt; gt;
gt; gt; Target.offset(0,1) doesn't refer to column B--it refers to the cell on column to
gt; gt; the right of the target (which is in column C), so it refers to column D.
gt; gt;
gt; gt; One more version that I'd use if I were doing it:
gt; gt;
gt; gt; Option Explicit
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; 'when entering data in Col C
gt; gt; Dim myPWD As String
gt; gt;
gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; If Intersect(Target, Me.Range(quot;C:Cquot;)) Is Nothing Then
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; myPWD = quot;YourPassWordHerequot;
gt; gt;
gt; gt; On Error GoTo enditall
gt; gt;
gt; gt; Application.EnableEvents = False
gt; gt;
gt; gt; Me.Unprotect Password:=myPWD
gt; gt; If Trim(Target.Value) = quot;quot; Then
gt; gt; Target.Offset(0, -1).ClearContents
gt; gt; Target.ClearContents
gt; gt; Else
gt; gt; With Target.Offset(0, -1)
gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; .Value = Date
gt; gt; End With
gt; gt; End If
gt; gt; Me.Protect Password:=myPWD
gt; gt;
gt; gt; enditall:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; I check to make sure that only one cell is changed. If more than one, then get
gt; gt; out. (But you could do anything you wanted).
gt; gt;
gt; gt; I like to use quot;intersect(target,me.range(quot;c:cquot;))quot;. I find it easier to read and
gt; gt; change when the range changes.
gt; gt;
gt; gt; I put the password into a variable--so I only have to type it in correctly once.
gt; gt;
gt; gt; If the user clears the cell in column C, then I wipe out the date in column B.
gt; gt; That may not be what you want though.
gt; gt;
gt; gt; Except for the column B vs column D, most of this stuff is just a difference in
gt; gt; style, though.
gt; gt;
gt; gt;
gt; gt; Ray wrote:
gt; gt; gt;
gt; gt; gt; Actually I put data into Column C, and the date is automatically entered in
gt; gt; gt; Column B.
gt; gt; gt; What's the difference between the 2 codes.
gt; gt; gt; I'd rather use the more stable one. I'm not an expert (obviously), so if
gt; gt; gt; you could, would you elaborate on the differences in the code.
gt; gt; gt;
gt; gt; gt; I found the one I'm using here. It's actually a little variant that I
gt; gt; gt; played with to get work.
gt; gt; gt; I was intended for data input into column B, then date automatically input
gt; gt; gt; into column A.
gt; gt; gt; Also it used the NOW() function, which I changed to the date, and format. I
gt; gt; gt; don't like the way NOW() changes things, pretty much if it feels like it, and
gt; gt; gt; alters the date if I change the adjacent cell.
gt; gt; gt;
gt; gt; gt; Any more information would be appreciated, and Thank You for your help.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; You actually want to unprotect the worksheet -- not lock the cell.
gt; gt; gt; gt;
gt; gt; gt; gt; If the cell is locked and the worksheet is protected, then the user can't change
gt; gt; gt; gt; that cell.
gt; gt; gt; gt;
gt; gt; gt; gt; If you unprotect the worksheet, then it won't matter if the cell is locked or
gt; gt; gt; gt; unlocked.
gt; gt; gt; gt;
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; me.protect password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt; enditall:
gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; But looking at your code, it looks like you're putting the date in column B and
gt; gt; gt; gt; then formatting column D as a date.
gt; gt; gt; gt;
gt; gt; gt; gt; From your original question, it looks like you want to use the cell to the left
gt; gt; gt; gt; (target.offset(0,-1)).
gt; gt; gt; gt;
gt; gt; gt; gt; I'd use something like:
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; gt; gt; gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; With Target.Offset(0, -1)
gt; gt; gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; .Value = Date
gt; gt; gt; gt; End With
gt; gt; gt; gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt; enditall:
gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Ray wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; How do I unprotect and reprotect a cell with a macro?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; gt; gt; gt; gt; gt; worksheet protected.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; gt; gt; gt; gt; gt; worksheet.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Ray wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; gt; gt; gt; gt; gt; cell that I put data into.
gt; gt; gt; gt; gt; gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; gt; gt; gt; gt; gt; Here is the macro:
gt; gt; gt; gt; gt; gt; gt; __________________________________________________ __________
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; gt; enditall:
gt; gt; gt; gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt; gt; __________________________________________________ ________________
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Is there a workaround or something that I could add to the macro?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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.

quot;Dave Petersonquot; wrote:

gt; You want to lose the date if column C changes?
gt;
gt; Do you mean that if column B contains the date, then you want it removed. If
gt; column B is empty, then add it?
gt;
gt; Personally, I don't think I'd do it that way. I'd just change the value in
gt; column B to the date and time. Then you can know when the last time the cell
gt; was changed.
gt;
gt; If that seems resonable:
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; 'when entering data in Col C
gt; Dim myPWD As String
gt;
gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; If Intersect(Target, Me.Range(quot;C:Cquot;)) Is Nothing Then
gt; Exit Sub
gt; End If
gt;
gt; myPWD = quot;YourPassWordHerequot;
gt;
gt; On Error GoTo enditall
gt;
gt; Application.EnableEvents = False
gt;
gt; Me.Unprotect Password:=myPWD
gt; If Trim(Target.Value) = quot;quot; Then
gt; Target.Offset(0, -1).ClearContents
gt; Target.ClearContents
gt; Else
gt; With Target.Offset(0, -1)
gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; .Value = Date
gt; End With
gt; End If
gt; Me.Protect Password:=myPWD
gt;
gt; enditall:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt;
gt; But I'm not sure why you had a problem with the code. It worked fine for me.
gt;
gt;
gt;
gt; Ray wrote:
gt; gt;
gt; gt; You are right about the Target.Offset(0, 1), it doesn't put the date into
gt; gt; cell D, it just makes cell D format numbers with a date.
gt; gt; I removed the whole line quot;Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;quot;
gt; gt;
gt; gt; That second macro that you gave me, I got an error. So I just removed that
gt; gt; line above, and added the password lines in. It seems to be working.
gt; gt; I'll try that new one, and let you know how that goes too.
gt; gt;
gt; gt; Also I'd like it to wipe out cell B if cell C is changed so that I can find
gt; gt; out if things are being modified. Will that macro below do that. I'm a
gt; gt; little confused on your wording.
gt; gt;
gt; gt; Thank You Very
gt; gt; Much
gt; gt; Appreciated
gt; gt; Ray
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; These are the two portions that do the work:
gt; gt; gt;
gt; gt; gt; n = Target.Row
gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; me.protect password:=quot;YourPasswordHerequot;
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; gt; gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; gt; gt; With Target.Offset(0, -1)
gt; gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; .Value = Date
gt; gt; gt; End With
gt; gt; gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; And remember that Target is the range that was changed.
gt; gt; gt;
gt; gt; gt; Since you already checked to see if the change was in column C, then
gt; gt; gt; quot;excel.range(quot;Cquot; amp; n)quot; is just refering to the range that you changed.
gt; gt; gt;
gt; gt; gt; excel.range(quot;Bquot; amp; n) refers to column B of the same row.
gt; gt; gt; but so does target.offset(0,-1).
gt; gt; gt;
gt; gt; gt; Target.offset(0,1) doesn't refer to column B--it refers to the cell on column to
gt; gt; gt; the right of the target (which is in column C), so it refers to column D.
gt; gt; gt;
gt; gt; gt; One more version that I'd use if I were doing it:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; 'when entering data in Col C
gt; gt; gt; Dim myPWD As String
gt; gt; gt;
gt; gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; gt; If Intersect(Target, Me.Range(quot;C:Cquot;)) Is Nothing Then
gt; gt; gt; Exit Sub
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; myPWD = quot;YourPassWordHerequot;
gt; gt; gt;
gt; gt; gt; On Error GoTo enditall
gt; gt; gt;
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt;
gt; gt; gt; Me.Unprotect Password:=myPWD
gt; gt; gt; If Trim(Target.Value) = quot;quot; Then
gt; gt; gt; Target.Offset(0, -1).ClearContents
gt; gt; gt; Target.ClearContents
gt; gt; gt; Else
gt; gt; gt; With Target.Offset(0, -1)
gt; gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; .Value = Date
gt; gt; gt; End With
gt; gt; gt; End If
gt; gt; gt; Me.Protect Password:=myPWD
gt; gt; gt;
gt; gt; gt; enditall:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; I check to make sure that only one cell is changed. If more than one, then get
gt; gt; gt; out. (But you could do anything you wanted).
gt; gt; gt;
gt; gt; gt; I like to use quot;intersect(target,me.range(quot;c:cquot;))quot;. I find it easier to read and
gt; gt; gt; change when the range changes.
gt; gt; gt;
gt; gt; gt; I put the password into a variable--so I only have to type it in correctly once.
gt; gt; gt;
gt; gt; gt; If the user clears the cell in column C, then I wipe out the date in column B.
gt; gt; gt; That may not be what you want though.
gt; gt; gt;
gt; gt; gt; Except for the column B vs column D, most of this stuff is just a difference in
gt; gt; gt; style, though.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Ray wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Actually I put data into Column C, and the date is automatically entered in
gt; gt; gt; gt; Column B.
gt; gt; gt; gt; What's the difference between the 2 codes.
gt; gt; gt; gt; I'd rather use the more stable one. I'm not an expert (obviously), so if
gt; gt; gt; gt; you could, would you elaborate on the differences in the code.
gt; gt; gt; gt;
gt; gt; gt; gt; I found the one I'm using here. It's actually a little variant that I
gt; gt; gt; gt; played with to get work.
gt; gt; gt; gt; I was intended for data input into column B, then date automatically input
gt; gt; gt; gt; into column A.
gt; gt; gt; gt; Also it used the NOW() function, which I changed to the date, and format. I
gt; gt; gt; gt; don't like the way NOW() changes things, pretty much if it feels like it, and
gt; gt; gt; gt; alters the date if I change the adjacent cell.
gt; gt; gt; gt;
gt; gt; gt; gt; Any more information would be appreciated, and Thank You for your help.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; You actually want to unprotect the worksheet -- not lock the cell.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If the cell is locked and the worksheet is protected, then the user can't change
gt; gt; gt; gt; gt; that cell.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If you unprotect the worksheet, then it won't matter if the cell is locked or
gt; gt; gt; gt; gt; unlocked.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; gt; me.unprotect password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; gt; me.protect password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; enditall:
gt; gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; But looking at your code, it looks like you're putting the date in column B and
gt; gt; gt; gt; gt; then formatting column D as a date.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; From your original question, it looks like you want to use the cell to the left
gt; gt; gt; gt; gt; (target.offset(0,-1)).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I'd use something like:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; gt; If Trim(Target.Value) lt;gt; quot;quot; Then
gt; gt; gt; gt; gt; Me.Unprotect Password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; gt; With Target.Offset(0, -1)
gt; gt; gt; gt; gt; .NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; gt; .Value = Date
gt; gt; gt; gt; gt; End With
gt; gt; gt; gt; gt; Me.Protect Password:=quot;YourPasswordHerequot;
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; enditall:
gt; gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Ray wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; How do I unprotect and reprotect a cell with a macro?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; If it doesn't work with the cell locked, maybe it's because you have the
gt; gt; gt; gt; gt; gt; gt; worksheet protected.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Add a line that unprotects the worksheet, makes the change and reprotects the
gt; gt; gt; gt; gt; gt; gt; worksheet.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Ray wrote:
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I have a date macro. It automatically puts the date into the cell left of the
gt; gt; gt; gt; gt; gt; gt; gt; cell that I put data into.
gt; gt; gt; gt; gt; gt; gt; gt; I works fine if I don't lock the cell, but does nothing it it's locked.
gt; gt; gt; gt; gt; gt; gt; gt; Here is the macro:
gt; gt; gt; gt; gt; gt; gt; gt; __________________________________________________ __________
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt; gt; gt; gt; gt; gt; gt; 'when entering data in Col C
gt; gt; gt; gt; gt; gt; gt; gt; On Error GoTo enditall
gt; gt; gt; gt; gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; gt; gt; gt; gt; If Target.Cells.Column = 3 Then 'can be modified, see below
gt; gt; gt; gt; gt; gt; gt; gt; n = Target.Row
gt; gt; gt; gt; gt; gt; gt; gt; If Excel.Range(quot;Cquot; amp; n).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; gt; gt; gt; gt; Excel.Range(quot;Bquot; amp; n).Value = Date
gt; gt; gt; gt; gt; gt; gt; gt; Target.Offset(0, 1).NumberFormat = quot;mm-dd-yyyyquot;
gt; gt; gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; gt; gt; enditall:
gt; gt; gt; gt; gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt; gt; gt; __________________________________________________ ________________
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Is there a workaround or something that I could add to the macro?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;