Consulting

Results 1 to 2 of 2

Thread: Auto save workbook when locked cell is modified

  1. #1

    Auto save workbook when locked cell is modified

    Hi!

    I'm fairly new to VBA and typically just try to mesh code together that I find through google (some of which came from this site!)

    What I'm looking to do is double-click on a locked cell, have a timestamp auto-populate, re-lock the cell, and have the workbook automatically save. I was able to get the following two codes to work separately, but I'm not experienced enough to make them work together.

    Thank you in advance to anyone able to give me guidance on this!

    - Noel













    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    If Not Intersect(Range("E:F"), Target) Is Nothing Then
    Target = Now()
    Target.NumberFormat = "hh:mm:ss AM/PM"
    ActiveWorkbook.Save
    End If
    End Sub


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range

    Set MyRange = Intersect(Range("E:F"), Target)
    If Not MyRange Is Nothing Then
    Sheets("Production Log").Unprotect Password:="hello"
    MyRange.Locked = True
    Sheets("Production Log").Protect Password:="hello"
    End If
    End Sub

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim MyRange As Range
        Cancel = True
        Set MyRange = Intersect(Range("E:F"), Target)
        If Not MyRange Is Nothing Then
            Sheets("Production Log").Unprotect Password:="hello"
            Target = Format(Now(), "hh:mm:ss AM/PM")
            MyRange.Locked = True
            Sheets("Production Log").Protect Password:="hello"
            ActiveWorkbook.Save
        End If
    End Sub
    Semper in excretia sumus; solum profundum variat.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •