Can Excel Data Validation Input Message Come from List?

In Excel I have a two-column list of codes in a worksheet named "Codes."  The table looks something like this:

Code   Description
A          Description A
B          Description B
C          Description C

I am using the 1st column (A,B,C) in cell validation so people can only choose A, B or C and we don't have any accidents caused by mistyping.  So in my DV Settings tab I refer to the column containing ABC.  

I would also like to have an input message that looks like the table above so people know what A means, etc. as they are in the drop-down.

Since the table is already all typed out, I hate to duplicate my efforts by retyping the codes and descriptions into the input message field.  Is there a way to just refer to the codes and descriptions?  That would save time, plus, if and when the codes get changed the input message would update also.

Any way to do this?
witzph1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try in the sheet module

Private Sub Worksheet_Activate()
Set validationCell = ActiveSheet.Range("C1")
strAddr = Right(validationCell.Validation.Formula1, Len(validationCell.Validation.Formula1) - 1)
For Each c In Range(strAddr)
    strText = strText & c & " => " & c.Offset(, 1) & vbCrLf
Next
validationCell.Validation.InputMessage = strText
End Sub

Open in new window

Regards
0
witzph1Author Commented:
I probably put your script in the wrong place.  And I may not have described what I need correctly. Take a look at the attached.  In tab "AB," column N has validation turned on and is referring to the table of codes in tab "Codes."  The source is dynamic via the =INDIRECT("ProspTypes[Codes]" formula.

But if you look in the Input Message tab, I've had to hand type the information found in the codes table into the field.  Will your script replace my having to type that information?
Template.xlsm
0
Martin LissOlder than dirtCommented:
I would do it slightly differently. Change MyNR to the name of your Named Range. (I have no problem with all the credit going to Rgonzo1971)
Private Sub Worksheet_Activate()
Dim c As Range
Dim strText As String
For Each c In Range("MyNR")
    strText = strText & c & vbCrLf
Next
With Range("MyNR").Validation
    .InputTitle = "Please enter"
    .InputMessage = strText
End With
End Sub

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

witzph1Author Commented:
Martin or Rgonzo, if you open the attached spreadsheet a couple of entries up, am I putting the script in the right sheet, the "codes" sheet? I'm getting an error whenever I click on the Codes tab.
0
Martin LissOlder than dirtCommented:
Try this workbook.
28736107.xlsm
0
witzph1Author Commented:
That looks like it worked...as long as what is showing in the DV input message is coming as a result of the script, and dynamically updating.  

To test, I added a new row to the validation table.  When I go back into the AB tab, and click on a field in column N, the input message doesn't seem to include the newly added row of the table.  Am I doing something wrong?
Copy-of-28736107.xlsm
0
Martin LissOlder than dirtCommented:
The values will change when you activate the AB sheet which will happen when you open the workbook or select another sheet and then go back to AB. If that doesn't work or that's not acceptable then please let me know.
0
Martin LissOlder than dirtCommented:
I think I fixed it. Change the Worksheet_Activate event to this.

Private Sub Worksheet_Activate()
Dim strText As String

Dim lngRow As Long
With Sheets("Codes").Range("ProspTypes")
    For lngRow = 1 To .Rows.Count
        strText = strText & .Cells(lngRow, 1) & " = " & .Cells(lngRow, 2) & vbCrLf
    Next
End With

With Sheets("AB").Range("N7:N" & Sheets("AB").UsedRange.Rows.Count).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(""ProspTypes[Code]"")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Valid types are:"
        .ErrorTitle = "Please Enter Prospect Code"
        .InputMessage = strText
        .ErrorMessage = _
        "PI   = Prospect Initiated" & Chr(10) & "CR  = Client Referral" & Chr(10) & "LR   = Leveraged Referral" & Chr(10) & "PIR = Producer Initiated Prospect" & Chr(10) & "IA   = Internal Agency Referral" & Chr(10) & "C    = Cross Selling" & Chr(10) & "R    = Rounding"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
witzph1Author Commented:
Martin, so sorry for the delay in getting back to this.  When I left Friday I had intentions of getting back to this on Monday, but my Mondays can be crazy.  

I tried your macro and it works perfectly.  That is so great!!  Thank you so much for lending your great talents!!
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
witzph1Author Commented:
Could I ask you one more quick question?  Sorry to bother you with a "closed" question.

On the script line that reads:

With Sheets("AB").Range("M7:M" & Sheets("AB").UsedRange.Rows.Count).Validation

Is there any way to replace the worksheet name "AB" with a variable that would allow the script to work on any sheet?  The reason I ask is because I will need to, from time to time, copy the worksheet named "AB" and rename it.  It would be cool if I didn't have to go into the script for the new sheet and change "AB" to the new worksheet name.

If not, no big deal.
0
Martin LissOlder than dirtCommented:
Change

With Sheets("AB").Range("N7:N" & Sheets("AB").UsedRange.Rows.Count).Validation

to

With ActiveSheet.Range("N7:N" & ActiveSheet.UsedRange.Rows.Count).Validation
0
witzph1Author Commented:
NICE!  Thanks again!
0
Martin LissOlder than dirtCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.