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

LVL 1
Andrew BassTechnical SupportAsked:
Who is Participating?
 
folderolConnect With a Mentor Commented:
On the sheet in question, start with the sheet unprotected.  Click Tools, Macro, Record new macro, pick a name etc, click okay, then select Tools, Protection, Protect Sheet, and check off the boxes you want for this sheet.  Click Okay, and end macro recording.  This will give you the proper Protect method syntax to use.  I typically use

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True, AllowUsingPivotTables:=True

Next, paste the userinterfaceonly:=true to the command, which you can't do through the menus in Excel.

For invoking it on workbook open, click View, Project Explorer, then double-click ThisWorkbook in the Excel Objects list.

If there is no Workbook_Open() macro, you can paste this snippet, but change "Sheet1" to "yourSheetName" whatever that is.


If your recorded macro has a different .Protect syntax, then substitute yours in the Workbook_Open() macro.




Private Sub Workbook_Open()
        worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, userinterfaceonly:=True
End Sub

Open in new window

0
 
ExcelGuideConsultantCommented:
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"
0
 
Andrew BassTechnical SupportAuthor Commented:
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?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ExcelGuideConnect With a Mentor ConsultantCommented:
im sorry, didnt see that you already was protecting the sheet via VBA.

but then you can lock the cells after the update by, i.e.:
.Locked = True
.FormulaHidden = True

you want me to include this in your code?
0
 
Andrew BassTechnical SupportAuthor Commented:
Psychotec that would be awesome if you wouldnt mind thanks.  Hopefully this works
0
 
folderolCommented:
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.
0
 
folderolCommented:
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.
0
 
Andrew BassTechnical SupportAuthor Commented:
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
0
 
folderolCommented:
Oh, and delete  the line
Me.Unprotect "internet"

from your macro, you don't need it.
0
 
Andrew BassTechnical SupportAuthor Commented:
Thank you Both it works now, Awesome!
0
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.

All Courses

From novice to tech pro — start learning today.