Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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

0
Andrew Bass
Asked:
Andrew Bass
  • 2
  • 2
1 Solution
 
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
 
Andrew BassTechnical SupportAuthor Commented:
Awesome Thanks!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now