need help getting combobox to open a file

[neowok]

this is a bit hard to explain and im pretty new with excel.

what i need is a combobox (will have to add about 20 of these) which
lists a value of my choosing, and when a user clicks one of these items
in the list, it opens a file for that item. It would be useful if the
items in the combobox were added/removed as i add and remove the items
from the list that will be used to fill the box.

the filenames will NOT be the same as the text in the combobox, and
they will not be excel files, and for some of them it may just need to
open a folder rather than a file.

The idea ive had so far is have a hidden sheet with the list of text
that i want in the combobox, and also for each of these text items make
a hyperlink to the file or folder that should be opened when that item
is clicked from the combobox. This way, all that needs to be done is
when the user clicks the relevant item in the combobox, excel simply
follows the hyperlink which should then open whatever its pointing to.
It also makes it quick and easy to maintain since when adding a new
item to the list, all i have to do is type the text i want in the
combobox and then in the same cell add a hyperlink to the file it
should open. I am pretty sure this is possible. for example purposes
ill put the combobox on "sheet1" and the list of items that need to go
in it on "sheet2".

i cant use validation box because i need the dropdown to be visible at
all times since this is going to be used by people that dont know
anything about excel and so wouldnt know the list was there without the
arrow being visible all the time.

so can anyone explain exactly how to do this as im not a complete
beginner (i can get the activex combobox onto the form and open the vb
editor) but have no idea how to write the code to do this or where to
put it (i assume it all goes into the combobox click event).

thanks

 

[Tom Ogilvy]

That is correct, it would go in the combobox click event, so to get to that
event, after you place the combobox, double click on it. This will take you
to the click event.

Since you list will by dynamic, you probably will want to create a dynamic
name to define it.

do instert=>Name=>Define
Name: List
RefersTo: =Offset(Sheet2!$A$1,0,0,CountA(Sheet2!$A$1:$A$200),1)

Make the 200 as long as the largest number of entries in the combobox you
will have.

Now in the properties of the combobox, put your listfillrange property to
Workbookname!List

then in the click event use code something like this:

Private Sub ComboBox1_Click()
Dim rng As Range
Dim hl As Hyperlink
If ComboBox1.ListIndex <> -1 Then
Set rng = Range("List")(ComboBox1.ListIndex + 1)
Set hl = rng.Offset(0, 1).Hyperlinks(1)
ThisWorkbook.FollowHyperlink Address:=hl.Address, _
SubAddress:=hl.SubAddress, NewWindow:=True
End If
End Sub

Using the combobox to add new entries just add complexity.
If you want to add more entries, go to sheet 2 and add it at the end of the
current list.

You might have to force a full recalc to get the combobox list to update.

 

[neowok]

ok thanks ill try that, it looks simpler than i thought.

yeah to add new entries i will not be using the combobox, that box wil
ONLY be for users to select an item which then opens a file or folde
(as pointed to by the hyperlink). for adding new items (the most wil
be about 15 anyway) ill just add them to the list