Creating a shortcut to insert initials and date

[Berkoless]

Hi,

In my work i have long Q&A excel lists where I typically write comments throughout - I always insert my initials and date before so people now its me and when.

Format initials and date (ddmm): [OB 2307]

I tried recording macros with no success.. Is there anyway to do so? so that I just click a shortcut and insert it streamlined while typing? (remaining in the typing field and not needing to enter the cell again)

Thanks

 

[Berkoless]

@Peter_SSs

Thanks, I will update.

I need this about 20 times a day and usually its the same column but occasionally could be anywhere.

 

[Peter_SSs]

Thanks for the profile update. .

What about question 1?

 

[Berkoless]

Sorry - missed that.

Yes - Its usually the same worksheets.

Thanks

 

[Dermot]

Maybe I'm misunderstanding, but this works for me.

I put this code in the Doubleclick event (of either a specific sheet or the Workbook itself)

 Dim txt
  txt = InputBox("Enter comment")
  If txt = "" Then Exit Sub
  ActiveCell.Formula = ActiveCell.Formula & vbCrLf & "[OB" & Format(Now, "DDMM") & "] " & txt
  Cancel = True

so when I doubleclick I get prompted for a comment and the result is this

Book1
	F	G
10	Before	Text
11
12	After	Text [OB2607] My comment
Sheet1

 

[Peter_SSs]

OK, I haven't quite got the detail that I was after but let's see if this is headed in the right direction.
I didn't get anything definitive about the number of workbooks so for now I am assuming this is just one workbook and 2 particular sheets in that workbook, Worksheets are 'Notes' and 'Data' but all that is easily changed in the code.

Also assumed that you might want this in any column. I haven't attempted the bold part yet but see if this would be any use for getting the new lines, initials & date.

While in the cell and typing your notes, just type zzz (this could be something else if you want) wherever you want the insertion. Once you leave the cell, the "zzz" should get replaced with the desired linefeed & text. If you happen to enter text including zzz into multiple cells at once (with Ctrl+Enter or Copy/Paste) it should do them all at once.

This code goes in the 'ThisWorkbook module of the workbook in question. In the vba window, find your project and double-click ThisWorkbook. Code gets pasted in the right hand pane there.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim sRepl As String

  sRepl = Chr(10) & Format(Date, "\[OB ddmm\]")

  Select Case Sh.Name
    Case "Notes", "Data"
      Application.EnableEvents = False
      Sh.UsedRange.Replace What:="zzz", Replacement:=sRepl, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Application.EnableEvents = True
  End Select
End Sub

 

[Berkoless]

Maybe I'm misunderstanding, but this works for me.

I put this code in the Doubleclick event (of either a specific sheet or the Workbook itself)

 Dim txt
  txt = InputBox("Enter comment")
  If txt = "" Then Exit Sub
  ActiveCell.Formula = ActiveCell.Formula & vbCrLf & "[OB" & Format(Now, "DDMM") & "] " & txt
  Cancel = True

so when I doubleclick I get prompted for a comment and the result is this

Book1
	F	G
10	Before	Text
11
12	After	Text [OB2607] My comment
Sheet1

Yup This works for me too.
Is there anyway to make it bold?

 

[Berkoless]

OK, I haven't quite got the detail that I was after but let's see if this is headed in the right direction.
I didn't get anything definitive about the number of workbooks so for now I am assuming this is just one workbook and 2 particular sheets in that workbook, Worksheets are 'Notes' and 'Data' but all that is easily changed in the code.

Also assumed that you might want this in any column. I haven't attempted the bold part yet but see if this would be any use for getting the new lines, initials & date.

While in the cell and typing your notes, just type zzz (this could be something else if you want) wherever you want the insertion. Once you leave the cell, the "zzz" should get replaced with the desired linefeed & text. If you happen to enter text including zzz into multiple cells at once (with Ctrl+Enter or Copy/Paste) it should do them all at once.

This code goes in the 'ThisWorkbook module of the workbook in question. In the vba window, find your project and double-click ThisWorkbook. Code gets pasted in the right hand pane there.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim sRepl As String

  sRepl = Chr(10) & Format(Date, "\[OB ddmm\]")

  Select Case Sh.Name
    Case "Notes", "Data"
      Application.EnableEvents = False
      Sh.UsedRange.Replace What:="zzz", Replacement:=sRepl, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Application.EnableEvents = True
  End Select
End Sub

Thanks,
I tried this and it didn't do anything.
Perhaps I am missing something else?

 

[Dermot]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim txt, i
  txt = InputBox("Enter comment")
  If txt = "" Then Exit Sub
  With ActiveCell
    .Formula = ActiveCell.Formula & vbCrLf & "[OB" & Format(Now, "DDMM") & "] " & txt
    i = InStr(.Formula, "[OB")
    .Characters(Start:=i, Length:=8).Font.FontStyle = "Bold"
  End With
  Cancel = True
End Sub

 

[Peter_SSs]

I tried this and it didn't do anything.
Perhaps I am missing something else?

Possibly entered the code in the wrong place, possible didn't adjust the sheet name(s) in the code, possibly other things - hard to tell.
If Dermot's suggestion seems like it is what you want, why don'y you follow that for a while?

Questions though.

1. Would you be entering more than one comment in a particular cell during the the day? If so, would you want the initials & date repeated for the 2nd & subsequent comments on that day or just the first time?
   
   
2. Would you be entering comments in the same cell over multiple days?