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]

Any Ideas?

All I am trying to do is use either a list box, combo box or data validation controls to list resources. Once the resource has been selected, the user would then beable to click on the name in the cell and send an email to that resource or open another spread sheet(depending on the resource)

I am very very new to VBA and control boxs so please be patient with me.

Any help at all here?

 

[Andrew Poulsom]

You can't put a Hyperlink in a drop down, but you can achieve the effect you want.

Name your list of Hyperlinks eg List, by selecting all the cells, typing List in the Name box and pressing Enter. Set your Data Validation to Allow List with Source =List. Right click the worksheet tab and choose View Code. Paste this code into the window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim r As Long
    If Target.Address <> "$C$2" Then Exit Sub
    Set Rng = Range("List")
    r = WorksheetFunction.Match(Target.Value, Rng, False)
    Rng.Cells(r, 1).Hyperlinks(1).Follow
End Sub

The code assumes that your Data Validation dropdown is in C2 - adjust line 3 to suit. It also assumes you created the name List as above.

Press Alt+F11 to return to your workbook and try it out.

 

[zendog1960]

here is my attempt

ok..it tried what you wrote and to no avail. I have encluded the following to show you what I have at this point:

the main Lay out:

Tasks.xls
	A	B	C	D	E	F	G	H	I	J
5	Monday
6				Hours		Date Due
7	Done	Tasks	Resources	Planned	Actual	Planned	Completed	Handoff	Follow-Up	Notes
8			Project Engineer
9
10
11
12
13
14
15	Tuesday
16				Hours		Date Due
17	Done	Tasks	Resources	Planned	Actual	Planned	Completed	Handoff	Follow-Up	Notes
18			Project Engineer
19
20
21
22
23
Sheet1

The Range called "List"

Tasks.xls
	W	X	Y	Z	AA
7
8					Project Manager
9					Project Engineer
10					Senior Designer
Sheet1

and the sub you had written. Since the first vadlidation code was in C8, I modified the code to reflect that per your instructions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim r As Long
If Target.Address<> "$C$8" Then Exit Sub
Set Rng = Range("List")
r = WorksheetFunction.Match(Target.Value, Rng, False)
Rng.Cells(r, 1).Hyperlinks(1).Follow
End Sub

One more thing. There is going to be multiple validation boxes on each page. Once you see my layout I think you will understand what I am trying to accomplish.

I hope you guys can help me out here. Keep in mind that I am very new to VBA so explain like it was my very first time!

 

[zendog1960]

Continued.....

If you think I have left something out I will certainly provide the information to you. Just let me know if I forgot something or you need more information. I can even email you the spreadsheet if that will make this easier. Whatever it takes~!

 

[Andrew Poulsom]

What version of Excel are you using? Do you have macros enabled?

It worked when I tested in in Excel 2000.

 

[zendog1960]

that works if I have only one validation box per sheet. What do I do if I have say 15 validation boxes and they all reference the same list? =List is the source for each box? It doesn't work then...

 

[Andrew Poulsom]

Select all your cells which contain Data Validation by clicking the first then Ctrl+Clicking the subsequent ones. Choose Insert|Name|Define, enter VCells in the Name box and click OK.

Change this line of code:

If Target.Address <> "$C$8" Then Exit Sub

to:

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

and try it out.

 

[zendog1960]

that worked on a simple test sheet. I tried to put in to play in the sheet I created and it is giving me a run-time error 1004 "Method 'Range" of object '_worksheet' failed.

Any clues?

 

[Andrew Poulsom]

that worked on a simple test sheet. I tried to put in to play in the sheet I created and it is giving me a run-time error 1004 "Method 'Range" of object '_worksheet' failed.

Any clues?

On what line of code did it fail?