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

 

[Trevor G]

Welcome to the Forum,

You could record a macro to do this and at the time of recording assign a Keyboard shortcut or use the organiser once done. Simple option would be something like this. In the developer tab, tap the Use Relative Reference and then Tap the Record Button, assign the macro a name and use a shortcut, you will have to use Ctrl with a letter, maybe helpful to use the Shift Key as well so you don't over write any of the default shortcuts. Then in the cell Type in your initials and use Ctrl + semicolon which adds in the date. Stop recording and it should give you the code you need and when you use the shortcut keys it will add in your initials and date.

Example:

Sub mcrInitialDate()
'
' Shortcut keys Ctrl + Shift + M

    ActiveCell = Date
   
End Sub

 

[Berkoless]

Thanks Trevor,

I tried that in some variations, I encountered three issues:

1. It overwrites the content in the cell
2. It throws me out of the cell and I need to re enter and go to the end
3. It always does it on the same cell

The process i tried:
Started macro rec:
1. Clicked on a cell
2. hit the end button
3. Alt+enter
4. Entered my initials and shift+;
Ended the recording

What am I doing wrong?

Thanks

 

[Trevor G]

It would seem that the method I suggested doesn't work. I think it would need to be behind the Worksheet event for On Change that could do something like add a comment into a cell when something is changed automatically. Would inserting a comment work for you?

 

[Berkoless]

Thanks Trevor,

A comment as a small flag? In that case, no.
It should be in the cell, in a new line at the end and as a bonus - in bold if possible (not mandatory but nice to have).

I thought about creating a constant text or clip on the clipboard - does that sound like something that can be done?

 

[Trevor G]

The best I can come up with is to copy active cell content into the Clipboard and then paste back with line break and your initials and date. In the VBA screen you will either need to switch on Microsoft Forms or insert a UserForm from the Insert Menu, you don't need to use it as VBA then sees you have switched on the form object.

Once you have edited the cell come off it then select it then run the macro.

Sub CopyTextinToTheClipboard()
'Copy Cell value to the clipboard (using DataObject)
'Must enable Forms Library: Tools Menu > References > Microsoft Forms 2.0 Object Library
'Or just add a userform
'Declare object & string
Dim obj As New DataObject
Dim txt As String

'Add the cell content to the clipboard
  txt = ActiveCell.Value
  obj.SetText txt

'Add cell content as DataObject's into the Clipboard
  obj.PutInClipboard

'Reset the cell to include its content
'And add initials and date in line break
ActiveCell = txt & Chr(10) & "OB " & Date
End Sub

 

[Berkoless]

Thanks Trevor,

I didn't fully understand the process, i tried editing the UserForm and pasting the code you wrote - no success.
I have to say that I am a newbie and I am not so familiar with UserForm, macros,VBA and all this (I am following some YouTube tutorials).

Thanks

 

[Trevor G]

Ignore the userform you don't need to do anything with it. It purely triggers VBA to recognise you are using Form Objects. You just add the code into a New Module

 

[Berkoless]

Did this work for you?
I can't figure out what I'm doing wrong.

I open a new macro, copy+paste the vba code above and try yo run it.
It comes up with an error.

 

[Peter_SSs]

@Berkoless

1. Is this always in the same worksheet or same workbook or could it be in many different workbooks?
   
   
2. Do you need this a few times per day or hundreds of times per day?
   
   
3. Is this required in any particular column(s) or could be anywhere on the worksheet?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)