[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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

0
Andrew Bass
Asked:
Andrew Bass
  • 4
  • 4
  • 2
2 Solutions
 
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 BassAuthor 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
 
ExcelGuideConsultantCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Andrew BassAuthor 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 BassAuthor 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:
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
 
folderolCommented:
Oh, and delete  the line
Me.Unprotect "internet"

from your macro, you don't need it.
0
 
Andrew BassAuthor Commented:
Thank you Both it works now, Awesome!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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