Comments using Validation input

[Don Cameron]

A few days ago a question relating to comments grabbed my attention.
The reply informed us that the comment in question was able to be seen by
clicking the
cell it was attached to and as long as that cell remained active the comment
could be seen.
So we have a comment which is not a comment visible only when the cell is
selected as opposed
to a comment which is visible when the cursor is over the cell. Sounds
confusing but it achieves the result of showing a "floating" comment which
remains in the same place on the screen even when it applies to a cell in a
'frozen' section and the rest of the cols or rows are moved.
I know now that this type of comment is created with the validation
procedure and I have found several opportunities to use it but, I have
another question.

Is there any way of formatting this comment (width,height,font,colour etc)
in a manner similar to what is available for a regular comment ?

As an example
I have a spreadsheet that records data relating to product complaints
Column BQ contains a code number indicating the type of fault (currently 17
groups) and rather than record the text just the code number is shown. There
is a list giving numbers against the fault description but lookup is not
satisfactory. My idea was to have a comment attached to the header cell but
space is limited - the comment needs 18 lines, and the header is only 2
lines which are 'frozen' because as time goes by, the complaint listing
increases line by line and typically can reach 200 - 300 by year end.
The first time I tried it a discovered that fault description 3 which reads
3 Back-Cover Not Trimmed Correctly will not fit in 1 line and it doesn't
matter what I do - underscore between words, no spaces, etc I cannot make
the comment wider (or narrower if it comes to that).

Can anyone suggest how formatting may be achieved

Thanks

 

[Debra Dalgleish]

No, the data validation input message can't be formatted.

You could add a textbox to the worksheet, and display it when a specific
cell is selected. For example, with a text box name "txtMyMessage":

'================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$D$1" Then
Shapes("txtMyMessage").Visible = True
Else
Shapes("txtMyMessage").Visible = False
End If
End Sub
'=========================

Copy the code to the worksheet module, as described here:

http://www.contextures.com/xlvba01.html#Worksheet

 

[doncam65]

Thank you Debra
Your solution solves my problem.