Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Signature date stamp

    Hi,

    I am trying to condition cells in excel to date stamp the cell if someone writes in the cell next to it. My coworker found a macro that does it but the problem is that if someone writes in the wrong cell, the date stamp does not go away. On top of the date stamp we do not want the stamp to work if the date matches column A in the same row. We are protecting the cells so they will not be editable but my macro skills are very limited. I've attached how I want the spreadsheet to look, but I need the formatting to make it work. Our contracted office trainer suggested this site and given the threads I've read, I think I've found the people who will have the answer!

    Thanks
    ~Kris
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kris,

    Welcome to the lounge as a first time poster.

    Place the following code in the Sheet1 area of the project {Press Ctrl+F11 then Click on Sheet1}.
    Protect the sheet before attempting to run the macro. BTW this macro kicks it self off every time a cell is changed on the sheet.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
      Dim zToday As String
      
      zToday = Format(Now(), "mm/dd/yy")
    
      If Not Intersect(Target, Range("H:H")) Is Nothing Then
      
        Application.EnableEvents = False
        If ActiveSheet.ProtectContents Then _
          ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
                              Scenarios:=False, userinterfaceonly:=False
    
        If Target.Value = "" Then
          Target.Offset(0, 1).Value = ""
        Else
          If Target.Offset(0, -7).Value = zToday Then
          Else
            Target.Offset(0, 1).Value = zToday
          End If
        End If
        
        If Not ActiveSheet.ProtectContents Then
          ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
                              Scenarios:=True, userinterfaceonly:=True
          ActiveSheet.EnableSelection = xlUnlockedCells
        End If
        Application.EnableEvents = True
        
      End If
    
    End Sub    '*** Worksheet_Change ***
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-11-09 at 17:52.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just FYI, if you right-click the sheet tab and choose View Code, you will jump straight to the worksheet's code module.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks, I'm so used to Ctrl+F11 I forgot about that one!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Nov 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Guys!

    That seemed to add the date but I'm going to need to tweek it as its affecting my should be editable dates. I know so little about what you did that I don't know what to ask to resolve it but I'm going to have someone take a look and see if we can get it all ironed out. Thanks bunches!


  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kris,

    From your first post:
    We are protecting the cells so they will not be editable but my macro skills are very limited.
    So the macro turns protection off to do it's thing then turns it back on. You should format any cell ranges you want to be editable and on the Protection tab UNCHECK the Locked check box. Then when the sheet is locked these cells will still be editable but the others will not.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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