Data Validation & Combo Boxes

[zendog1960]

Hello Folks,

I am trying to create either a combo-box list or a data validation list for a end user selction of resources. I can do that except I want the list to be somewhat interactive. I have created a sample of what I am talking about.

in AA20 thru AA21 is a list of items I want in the pulldown portion of the form. The hyperlink portion of the cells do not come in to the list however. I want them to use a pulldown to selct from a list, the if they select the name it will use the hyperlink to email tat person. Am I making sense here? Here is what I have just can not get the hyperlinks to come into the validation or the list box.

Book2
	U	V	W	X	Y	Z	AA
17				Test Area
18							Resources
19
20					Manager		Manager
21							Assistant
22
23
24
Sheet1

 

[zendog1960]

ok I found out why....

I made the change but

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim r As Long
If Application.Intersect(Target, Range("VCells")) Is Nothing Then Exit Sub

I didn't change this line with the right range name. I changed it and it works great!

Set Rng = Range("Resources")
r = WorksheetFunction.Match(Target.Value, Rng, False)
Rng.Cells(r, 1).Hyperlinks(1).Follow
End Sub

 

[zendog1960]

I have one final question. In the list of values I am using for a validation, I am including a blank cell. This is so we can have a clean slate each week and to no confuse the end user. When I slect the blank cell I get a run time error as follows:

Runtime error 1004:

Unable to get the Match property of the WorksheetFunction class

How do I handle the blank in the validation list so it does not bring up this error?

 

[zendog1960]

Help With this final Question

What can be done about a blank item on the validation list? I keep getting those 1004 run time erros and it stops the macro from running. I want the blank but don't want the error. How do we accomplish this?

 

[Andrew Poulsom]

Try:

If Application.Intersect(Target, Range("VCells")) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Exit Sub

 

[zendog1960]

That worked perfectly. I thank you for all your help!