Automatically add note to cell in dynamic range based on value

**StedDOOM** · 08-29-2022, 03:25 PM

Howdy!

I am working on a project containing a table that will track multiple services performed on multiple pieces of equipment. Additionally, I have a hidden table with three columns to identify the equipment: Unit #, Equipment, and Serial #.

The first column of the main table is a drop down list using data validation. The list is from the Unit # column of the hidden table.

What I am trying to do is that in the first column of the main table, when a drop down selection is chosen, I want Excel to automatically add a note to the cell that shows that Unit #'s corresponding Equipment and Serial # from the hidden table. For example, the first row of my hidden table could show 12345, D10 Dozer, 123456789. When "12345" is selected from the drop down in the main table, I would want the comment attached to it to say:

EQUIPMENT:
D10 Dozer

Serial #:
123456789

I've toyed around with a few ways in VBA but nothing is giving me quite what I'm looking for, and a lot of what I'm finding in forums are for fixed ranges or cells, but this main table could ostensibly go on forever as more and more services are performed on the different equipment.

For what it's worth, I also currently have two hidden columns in the main table that are just using VLOOKUP to auto-fill the corresponding Equipment and Serial # based on what drop down was selected in the first column. I realize the solution likely won't need that, but it's there for if I do need it.

I hope I've explained this well enough, but if I need to clarify further I'd be happy to try.

Any help would be greatly appreciated. Thanks for your time!

**nimrod1313** · 08-29-2022, 04:25 PM

hi StedDoom;
You've indicated you've got a vlookup that retrieves the information so my code assumes you know the values you want to enter into the note field.
So for demo purposes i've just hardcoded in the equipment and serial number
Without sample sheets this is about the best I can do

Private Sub Worksheet_Change(ByVal Target As Range)
   'IF Cell in column A AND only single cell changed AND cell has value
   If Target.Column = 1 And Target.Count = 1 And Target.Value <> "" Then
     Call addComment(Target, "D10 Dozer", "123456789")
   End If
End Sub


Public Sub addComment(targCell As Range, equipment As String, Serial As String)
    Dim note
    'BUILD NOTE
    note = "EQUIPMENT:" & vbNewLine
    note = note & equipment & vbNewLine & vbNewLine
    note = note & "Serial #:" & vbNewLine
    note = note & Serial & vbNewLine
    
    'ADD NOTE TO CELL
    With targCell
        On Error Resume Next
        .Comment.Delete
        On Error GoTo 0
        .addComment (note)
    End With
End Sub

if this has helped please consider clicking "add reputation", thx

Automatically add note to cell in dynamic range based on value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Automatically add note to cell in dynamic range based on value

Re: Automatically add note to cell in dynamic range based on value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dynamic Calculating range based on cell value

[SOLVED] Warning Sign/Note when cell value outside of number range

Dynamic range based on cell value

VBA to clear dynamic range based on cell value

Dynamic cell range based on a formula

copying dynamic range based on cell outside of range

Run macro automatically when a cell in a dynamic range changes

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions