Excel VB Time Stamp Cell

Hey all,
Have an excel spreadsheet I want to update cell c with the current system time when cell a is populated with data.  My goal is to not have the end users be able to make changes but I want the VB Script still to be able to run in the cell.  Protecting the docume nt to do this seems to be preventing VB from running.  Ive attached the Snippet of VB if you can help with this.

Additional Note:
Sheet is already set to trust but still not allowing update when protecting.  Run Time Error 1004 received.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("A3:A150"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "hh:mm:ss"
                        .Value = Time
                    End With
                End If
                Application.EnableEvents = True
            End If
            
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("G3:G150"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "hh:mm:ss"
                        .Value = Time
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
    End Sub

Open in new window

LVL 1
Andrew BassTechnical SupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hello NinjasVsPirates,

If you protected the worksheet, then issue this statement to turn it off:

Me.Unprotect "password"

and this to turn it back on:

Me.Protect "password"

Regards,

Patrick
0
Andrew BassTechnical SupportAuthor Commented:
Hey Matthew,
Thanks for the quick response, would doing this in the change event accomplish this or would I want to do it in the load event?

If possible can you insert the code into the snippet for least cause of resistance.  Thanks
0
Patrick MatthewsCommented:
Do it in the Change event.  Something like this:



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Me.Unprotect "password"
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("A3:A150"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "hh:mm:ss"
                        .Value = Time
                    End With
                End If
                Application.EnableEvents = True
            End If
           
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("G3:G150"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "hh:mm:ss"
                        .Value = Time
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
Me.Protect "password"
    End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andrew BassTechnical SupportAuthor Commented:
Awesome Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.