asked on

Timestamp Excel Sheet Autofill after change protected document

Hello All,

I have a protected worksheet where i need column B to fill with the time when a change in column a is made
I also need the same to apply when a change is made in column G to update column H with sytem time. The problem I am having is upon run the document completely unlocks this is not the desired end result. The fields in question columns B C H I need to remain protected while write capability exists in the unprotected fields.

Any assistance would be great.

I have attached the code I received from another Expert however it gives me a run time error because the cells that need updated are locked from user edit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Me.Unprotect "internet"
        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 "internet"
    End Sub

Open in new window

ExcelGuide

so this vba already put a timestamp in column B and H, right? Why dont you unprotect the sheet first and protect the sheet again after macro has been runned?

Example:
Sheet1.Unprotect Password:="YourPass"

[Your Code]

Sheet1.Protect Password:="YourPass"

Andrew Bass

ASKER

Hey Psychotec,

Thanks for the response. The problem I am having is the macro is leaving the cells unlocked and still allowing end users to change the cell contents. I need the time to update in those cells without ever having the end users themselves be able to manipulate the data. This is essentially a time tracker and needs to maintain accuracy. Thanks for the tip any other suggestions that you can think of to accomplish this result?

SOLUTION

ExcelGuide

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrew Bass

ASKER

Psychotec that would be awesome if you wouldnt mind thanks. Hopefully this works

folderol

You can use a protection call in VBA that allows VBA to change the cells, but not the user. This way, you don't need to unprotect the sheet.

The VBA looks something like

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, userinterfaceonly:=True

The userinterfaceonly is the pertinent property. This can only be used in the active session. When the Workbook is closed, this property reverts to false, which means vba can no longer change the cells. You will need to re-invoke the protect method with the userinterfaceonly property each time the workbook opens.

folderol

If you use userinterfaceonly:=true then vba can write to locked cells. I should have stated that in my first post, just to be clear.

Andrew Bass

ASKER

Folderol are you able to show me in the code how to execute that? I know psychotec was going to touch on that in the code to show how to properly excute that as well

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

folderol

Oh, and delete the line
Me.Unprotect "internet"

from your macro, you don't need it.

Andrew Bass

ASKER

Thank you Both it works now, Awesome!