Data Validation/Input Message

[belang46]

Hello all,

I am trying to create a template that allows the user to select exchange rate codes from a drop down menu. For example, the list will have MXP and USD when the down arrow is clicked.

Basically, when the user clicks the drop down and the mouse is highlighting "USD", my goal is to have an input message that says "US Dollar"...or if the user scrolls down to MXP, the input message says "Mexican Peso". I guess you'd say I was trying to make the input message conditional on what's highlighted.

I can't add columns to the spreadsheet, or I would have just added a column with a simple if statement that defines what is selected. Does anyone know if there's any way to do this?

Thank you much for any guidance you may be able to provide.

Regards,
Marcus

 

[VoG]

You can do this using the DV InPut Message tab.

 

[belang46]

Thanks Peter...i know I can make a common message amongst all choices in the validation list...but can I make the message change based on what is selected out of the list? That's the issue i'm having.

 

[VoG]

Try like this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Select Case Target.Value
        Case "USD": MsgBox "US Dollars"
        Case "MXP": MsgBox "Mexican Pesos"
        'add more cases here
    End Select
End If
End Sub

Change A1 to the cell with validation and complete the Select Case values.

 

[AlanAnderson]

Hi There,

Can't you add an additional sheet and build lookup table on new sheet?

That would allow for "user maintainable" variables rather than hard coding.

Regards,

Alan

 

[belang46]

Hi all,

I appreciate the feedback...it's been pushing me along as i try to figure this out.

I putzed around for a couple hours and decided to use comments instead. However, I am having to problems...don't know if anyone can lend a hand. I am still very new to the whole VBA thing, so forgive my lack of understanding basic concepts (learning out of a book).

1.) I was hoping to have the comment show only as the cell is selected...when the cell is no longer selected, the comment would go away

2.) I also wanted to apply this to multiple ranges of cells in the same column. I currently am only working with code that works for 1 cell only...and i'm stuck on how to make this apply to C5:C10 and C15:C20.

Again, thanks for your time and checking into these issues...us novices really appreciate it...you're a great help.

Regards,
Marcus

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then 'need to expand this function to apply to C5:C8 and C10:C13
Select Case Target.Value
Case "USD", "MXP" 'various currencies
Target.ClearComments ' erase any old
Target.AddComment 'add new
Target.Comment.Visible = True 'now need to make comments visible only when cell is selected
If Target.Value = "USD" Then Target.Comment.Text Text:="US Dollar"
If Target.Value = "MXP" Then Target.Comment.Text Text:="Mexican Peso"
Range("C5").Comment.Shape.Select True 'only works when comment is visible...below is to auto size
'the comment box.  Need this to expand to same ranges as above
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
End Select
End If
End Sub