Update values in comments using VBA [SOLVED]

[Sammy]

Hello Champs!


I have a dashboard that shows month to date and week to date information and there is not enough space to show all the values! Hence a slight problem for me.


What I am looking for is to have a comment display the remaining information when a user hovers/selects the respective cells (eg: Occupancy - I would like to have the target, program achievement, threshold etc... to be displayed as a comment). The input value for the comment will be in a different cell (Merged), which will also change based on user inputs.


Any help will be much appreciated on this.


Thanks,

Sam

 

[Hui]

Sam


Say you had 3 cells C4:E4

and you wanted them to have comments from C2:E2

(2 Rows above)


Use the following VBA code

[pre]

Sub Add_Comments()
Dim c As Range
Dim Row As Integer, Col As Integer

Row = -2 'Change to suit
Col = 0 'Change to suit

For Each c In Range("C4:E4") 'Change to suit
c.AddComment
c.Comment.Text Text:=c.Offset(Row, Col).Text
Next

End Sub

[/pre]

Adjust the Row and Col variables and Ranges to suit

 

[Sammy]

Perfect! Thanks a ton, Hui. Much appreciated.