Auto note / comment

[fionalofthouse2]

Hi,

I'm very much a Excel novice, so please bear with me.

For some context; at work we have an excel document with each employees name and the dates. We fill in which job each employee has been working on, which is then linked to other sheets to figure out labour costs for profit reviews etc. and also keeps a track of where people have been.

Our job references are a 4 digit number which just goes in order, for example we are currently on job 2360 and the next job will be 2361. It's then followed by a / and then 2 - 4 letters dependent on the name of the job. For example, if it was St Wilfrids Fire doors, we'd use SWFD.

When filling in where employees have been, we just use the number part of the reference.

The point of this post is to try and find a way to automatically put a note (or comment, but ideally a note) dependent on the job number put in. So when we hover over the number, it tells us which job it is.

Is anyone able to help? Thanks in advance!

 

[saboh12617]

Hello,

It is possible via VBA. I attach a small image as i think it can be useful to understand the structure.

With a Sub like this one, you can add a comment on a cell based on its number. It performs an XLOOKUP and stores the result in the comment. Then you can call this sub in the worksheet.change event for example.

Public Sub AddCom(rng As Range)
  Dim jobName
  On Error Resume Next
  jobName = WorksheetFunction.XLookup(rng, Range("F3:F12"), Range("G3:G12"))
  On Error GoTo 0
  If Err.Number <> 0 Then Exit Sub
 
  With rng
    If Not .Comment Is Nothing Then
       .Comment.Delete
    End If
    .AddComment jobName
  End With
End Sub

 

[fionalofthouse2]

Hi,

Thank you for this. I've not got any experience with VBA codes so all very alien to me!

Would the same be possible, but having the data (job ref & 4 number codes) on a separate tab?

 

[saboh12617]

Yes absolutely, you see in this line of the code

jobName = WorksheetFunction.XLookup(rng, Range("F3:F12"), Range("G3:G12"))

We are using the XLOOKUP function, exactly as if it was entered in Excel. So you just have to adapt the "F3:F12" (numbers searched) and "G3:G12" (names) to match their addresses in the workbook.
For instance if they were on tab "Sheet2", you would write

jobName = WorksheetFunction.XLookup(rng, Range("Sheet2!F3:F12"), Range("Sheet2!G3:G12"))

Then if you are very new to VBA, to add all the comments you can run a macro like this (again i refer to my screenshot, adapt the addresses to your data):

Sub AddAllComments()
Dim empljob as Range
For each empljob in Range("Sheet1!B3:B12")
  AddCom empljob
Next empljob
End Sub