Question regarding comments

[Fusion1337]

Hi everyone.

I was wondering if it would be possible to make different comments to
appear in the same cell depending on a selection from a validation
drop-down menu.

Let me expand a little bit on this:

Cells A1, A2, A3 have values of 1, 2, 3

Cell B1 is where the validation menu would appear (choices 1, 2 and
3), and the cell which would have a comment

Cells C1, C2, C3 have values of "one", "two", "three"

Would it be possible for me to create a comment in B1 which would
change based on a selection in that same cell?
(so that when I select a number, comment changes to the corresponding
word)


thank you

 

[Otto Moehrbach]

Setup your Data Validation cell to not show an error if a wrong value is
entered. Then place this macro in the sheet module of your sheet. To
access that module, right-click on the sheet tab and select View Code.
Paste this macro into that module. "X" out of the module to return to your
sheet. Post back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TheText As String
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B1")) Is Nothing Then
Select Case Target.Value
Case 1: TheText = "One"
Case 2: TheText = "Two"
Case 3: TheText = "Three"
End Select
Application.EnableEvents = False
Target.Value = TheText
Application.EnableEvents = True
End If
End Sub

 

[Fusion1337]

Many thanks Otto, it worked like a charm.