none
Worksheet modified date capture (not workbook) RRS feed

  • Question

  • Hi Champs,

    I am newbee to excel VBAs, I have workbook which contains around 10 worksheets. First worksheet is like home page that contains some basic info for rest of 10 workshets, in first I have one cell for each of those worksheets seperately, whose modified  or last saved date needs to be captured. Appreciate if you throw ideas 

    Thanks


    Vasu

    Tuesday, November 27, 2018 1:39 PM

Answers

  • Let's say you enter the names of the 10 sheets in A2:A11 on the 'home page' sheet.

    Format B2:B11 as date or date + time, as you prefer.

    Press Alt+F11 to activate the Visual Basic Editor.

    Double-click ThisWorkbook under Microsoft Excel Objects.

    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Dim ws As Worksheet
        Dim cel As Range
        Set ws = Worksheets("Home") ' use the name of the home page sheet
        If Sh.Name = ws.Name Then Exit Sub
        Set cel = ws.Range("A2:A11").Find(What:=Sh.Name, LookAt:=xlWhole, MatchCase:=False)
        If Not cel Is Nothing Then
            Application.EnableEvents = False
            cel.Offset(0, 1).Value = Now
            Application.EnableEvents = True
        End If
    End Sub

    Switch back to Excel and save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Vasu Miriyala Wednesday, November 28, 2018 8:51 AM
    Tuesday, November 27, 2018 2:57 PM

All replies

  • Let's say you enter the names of the 10 sheets in A2:A11 on the 'home page' sheet.

    Format B2:B11 as date or date + time, as you prefer.

    Press Alt+F11 to activate the Visual Basic Editor.

    Double-click ThisWorkbook under Microsoft Excel Objects.

    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Dim ws As Worksheet
        Dim cel As Range
        Set ws = Worksheets("Home") ' use the name of the home page sheet
        If Sh.Name = ws.Name Then Exit Sub
        Set cel = ws.Range("A2:A11").Find(What:=Sh.Name, LookAt:=xlWhole, MatchCase:=False)
        If Not cel Is Nothing Then
            Application.EnableEvents = False
            cel.Offset(0, 1).Value = Now
            Application.EnableEvents = True
        End If
    End Sub

    Switch back to Excel and save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Vasu Miriyala Wednesday, November 28, 2018 8:51 AM
    Tuesday, November 27, 2018 2:57 PM
  • Hi Vasu,

    Based on your description, I find that built-in features of Excel can't meet your needs. To better resolve your question, I will move it to Excel for Developer forum.

    Thanks for your understanding!

    Best Regards, 
    Herb

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Microsoft Team

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams

    Wednesday, November 28, 2018 8:20 AM
  • Thanks a ton Hans... that was wonderful. It worked like charm

    Vasu

    Wednesday, November 28, 2018 8:52 AM
  • Hi Vasu,

    Thanks Hans, you can also add a workbook save event that writes the time to the first worksheet when

    an event is triggered, which means that each worksheet is saved the same time.  

    Please refer to the following code:

    Private Sub Workbook_AfterSave(ByVal Success As Boolean) 
    
      Dim ws As Worksheet
      Set ws = Worksheets("Home")
    
      If Success Then 
        for i = 2 to 11
          ws.Range("B" & i).Value = now
        Next i
      End If 
    
    End Sub 

    Hopefully it helps you.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, November 28, 2018 10:02 AM
    Moderator