Results 1 to 9 of 9
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Logbook Application

    I'm trying to develop a logbook application using Word or Excel. I prefer Word, and I've inquired about it in the Word forum HERE, but the responses are bogging down. Maybe I can do it in Excel.

    I need the application to do this: 1) Insert a timestamp when I press a hotkey. 2) Let me type in the log entry. Usually these would be a line or two, but they could be longer. 3) As time passes, somehow keep track of the time that has passed since the timestamp, and print it next to the timestamp. This could be done by the passage of time, or whenever the file is opened, or by taking some sort of action, etc.

    The attached file does most of this, and the elapsed time is updated automatically, but there's no hotkey to insert a timestamp. I surely don't want to use TODAY(), since that updates itself every day.

    How do I insert a timestamp that won't update itself? It's probably easy, but I can't figure out how to do it.
    Attached Files Attached Files
    Last edited by Lou Sander; 2016-02-17 at 14:51. Reason: all > most
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    What is wrong with just typing in the date? Am I missing something?
    LouLog.PNG

    You could then use a Worksheet_Change event to insert the formula and do the formatting. Or if you want to get fancier you could create a macro to display a form for the entry of the date and entry then take the contents of the form insert the data, add the formula and do the formatting.

    Example to insert the formula:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim isect As Range
       
       Set isect = Application.Intersect(Range("B:B"), Target)
       If isect Is Nothing Then
         'Do Nothing
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
         ActiveCell.Offset(0, 1).FormulaR1C1 = "=" & Chr(34) & "(" & Chr(34) & _
            "R2C1-RR" & " days ago)"
         Application.EnableEvents = True '*** Reset Events ***
       End If
       
    End Sub 'Worksheet_Change
    Test file: Logbook-RGv1.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    When I type a new entry, I want it to date itself. I'm willing to press a hotkey to start the entry. It's not good to rely on a guy like me to know what day it is, even if he's just looked it up, or to type it in correctly, even if he knows it.

    It's like saying "Captain's log: Stardate 2413.8", except I'm gonna rely on the computer to put in the Stardate when I start a new log entry.

    I don't need dates popping up when my intention is not to start a new entry. I might want to edit an old one some time after it's started, and it wouldn't be good to redate.

    BTW, it's interesting that in Word, making the hotkey is a piece of cake, but updating the "days ago" seems to be impossible or at least is very challenging, while in Excel, the opposite seems to be true.

    I prefer Word and a timestamp macro because I can put the cursor wherever I want, in any document, inside a table or elsewhere, then hit the hotkey and start typing. That gives me my timestamp, and at the moment, that's what I'm doing.

    I can live without it, but I'm hoping to find a way to do the "days ago" thing. Maybe the timestamp could include a special character or series of characters, possibly hidden, that a macro could search for and put a "days since" next to.
    Last edited by Lou Sander; 2016-02-17 at 16:51. Reason: "not". Sheesh!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Ok, let's give this a try

    I've modified the code so that when you type a new comment it will automatically insert today's date and the formula to calculate days old.

    I moved the formatting out of the formula and into a Custom Number Format:
    "("#,###" days ago)";"Error";" "
    This format will display the the days as you wish along with two enhancements, if it is the current day it will show a blank and if the date is in the future it will display the word Error.

    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim isect As Range
       
       Set isect = Application.Intersect(Range("D:D"), Target)
       If (isect Is Nothing) Or (Target.Cells.Count > 1) Then
         'Do Nothing
       Else
         If Target.Offset(0, -2) = "" Then
           '***Prevent following code from refiring Change Event ***
           Application.EnableEvents = False
           With Target
           
              '*** Insert Current date and Aging Formula ***
              .Offset(0, -2).Value = Format(Now(), "mm/dd/yyyy")
              .Offset(0, -1).FormulaR1C1 = "=R2C2-RC[-1]"
              
              '*** Copy Formatting ***
              Range(.Offset(-1, -2), .Offset(-1, 0)).Copy
              .Offset(0, -2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                       SkipBlanks:=False, Transpose:=False
              Application.CutCopyMode = False
              
           End With 'Target
           
           Application.EnableEvents = True '*** Reset Events ***
           
         End If  'Target.Offset
         
       End If   'isect = ...
       
    End Sub 'Worksheet_Change
    LouLog.PNG

    Note: the code is also designed to allow you to make changes to the log entry w/o changing the Entry Date and you can also select an entire row and delete or clear contents without the macro causing errors.

    Test File: Logbook-RGv2.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    ....Insert a timestamp when I press a hotkey
    Hi Lou,

    As an alternative to a hotkey, you can try this piece of code in the worksheet module. Double clicking a cell in column B will add the current date. Version 1 will prevent an existing date from being overwritten when double clicked. Version 2 will allow a cell that is double clicked to have its date updated. You can integrate this into RG's fine code.

    HTH,
    Maud

    Version 1
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
        If Target = "" Then
            Target = Date
        End If
    End If
    End Sub
    Version 2
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
        Target = Date
    End If
    End Sub

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Lou

    ..is this logbook for use just by yourself???
    ..how many log entries do you anticipate???
    ..will you need to search the log entries???
    ..do you need to prevent changes being made to 'historical' entries???
    ..will it be used by others???

    zeddy

  7. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    zeddy,

    ..right now, it's just for me. I'm using it to keep a diary of symptoms, etc., so my physician (Doc Martin?) can see what I'm experiencing and when. I only see him every few months, so it's good to keep track of dates. It's basically very routine stuff, but I want to both log it and relieve all parties from the burden of doing mental date arithmetic.

    ..I don't anticipate as many as 100 entries, but we all know how things grow. 1,000 would be a lot -- maybe overkill. I'd probably want to start a new log after every visit.

    ..I don't anticipate much need to search log entries, but it would be a good capability to have.

    ..I don't need to prevent changes being made to 'historical' entries, but in general that's a nice feature to have in logging schemes (I spent time aboard ships, where it's important not to change things)

    ..I don't plan to share it with others, but of course if it's good enough it could be of interest to a larger circle.

    I posted a similar request in the Word forum. It wasn't going anywhere, so I tried it over here. The Word request ended up being responded to, and now I have what I originally thought I needed in Word, which is just a timestamped/elapsed time record for open ended text entries.

    I can see a really good place for an Excel version where, for example, one could be logging blood pressure readings or other measurements, where you are basically filling in a form with repetitive readings. In fact I plan to use the Excel version for blood pressure. I know the doc will be thrilled, since he's always after me to take my own BP. I did it for a while, but it got to be tiresome typing in all those dates and times. BTW, the time is important in BP, etc., but the date is usually enough for plain text.

    One could see the benefit of keeping track of things like phases of the moon (for lunatics and lunatic wannabes) and local sidereal time (for ESP investigators) and even time with regard to sunrise / sunset. The more I think about it, the more I see the advantages that Excel has in these areas. There's a lot going on with wearable monitors these days. An Excel version might be more powerful, more tailorable, etc., or maybe not.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I just downloaded RG's version and noted Maudibe's variations (I've been occupied elsewhere for a few days). Pretty slick! I reformatted the cells so they all are top-aligned. That keeps everything straight if the log entries wrap over to multiple lines.

    I'm going to use this to keep an ongoing record of my weight and blood pressure, just to see how it works and how useful it will be in practice. When people are keeping this sort of record, it's common for there to be long interruptions (when they get tired of doing it, etc.). The "days ago" feature will be helpful in these cases, I think.

    One good addition would be if it provided a prompt where the next entry is to be.

    Another one, if it's not going to open a can of worms, would be to allow three or more columns of data (for weight and the two blood pressure readings, for example), and to do a timestamp if any one or more of the columns get an entry. (For example if I weigh myself but don't take my blood pressure.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Just place the following code in Module 1 (BTW: you can delete all the code that is there as it is just test code and not used by the application).

    Code:
    Sub Auto_Open()
       
       '*** Locate cursor at next empty row ***
       [D4].End(xlDown).Offset(1, 0).Select
       
    End Sub
    HTH
    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
  •