Re: run a macro in a locked cell

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

> You actually want to unprotect the work*** -- not lock the cell.
>
> If the cell is locked and the work*** is protected, then the user can't change
> that cell.
>
> If you unprotect the work***, 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
>
>
>
> Ray wrote:
> >
> > How do I unprotect and reprotect a cell with a macro?
> >
> > "Dave Peterson" wrote:
> >
> > > If it doesn't work with the cell locked, maybe it's because you have the
> > > work*** protected.
> > >
> > > Add a line that unprotects the work***, makes the change and reprotects the
> > > work***.
> > >
> > > Ray wrote:
> > > >
> > > > 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
> > >
>
> --
>
> Dave Peterson
>
.

Quantcast