Data Validation & Combo Boxes

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?

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:


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.

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:

The Range called "List"

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!

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~!

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

It worked when I tested in in Excel 2000.

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...

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.

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?

zendog1960 said:

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?

Click to expand...

On what line of code did it fail?