Hyperlinks in a ComboBox

nightviperdark

New Member
Joined
Sep 8, 2006
Messages
5
Hiya
Can anyone assist me with placing hyperlinks into a comboBox in Excel.
I have seen some threads saying it can be done , but being a novice at this Im finding a bit difficult.

How do you do it ?

Anyone that thinks they could help, please contact me....

Cheers
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, nightviperdark
Welcome to the Board !!!!!

"placing hyperlinks into a comboBox" is not really what happens
you can pick an item and then use code to "goto" another location in the workbook or whatever

can you be more specific and provide some sampledata ?

kind regards,
Erik
 

nightviperdark

New Member
Joined
Sep 8, 2006
Messages
5
Erik
What I have is a spreadsheet that has several company contracts within it.
Lets say across the top of the sheet I have the facilities the companies provide ie,health & safety,agreements,letters,contract/tenders etc.
Now what i need to do is insert PDF files below these colums BUT able to multi pick.As each facility does not always have just one PDF file to go with it.
The reason I want a ComboBox is to make the spreadsheet user friendly as I do not want hundreds of lines showing the PDF file route below each column.
Cheers
Nightviperdark
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
not clear for 100%
"insert PDF files " you mean the path+name

as I do not want hundreds of lines showing the PDF file route below each column
but you will need to have them somewhere ...
You can hide the rows and when clicking a header the combobox would appear. If you think of something else then explain.

do you want to open a file when the combobox is clicked ?
 

nightviperdark

New Member
Joined
Sep 8, 2006
Messages
5

ADVERTISEMENT

Erik
Yes , When i click the ComboBox i want the file to appear.
The files (PDF) are currently in a folder on my desktop.

How would I do the following "You can hide the rows and when clicking a header the combobox would appear."

sorry if its a bit confusing , but will try to add more if you require

Nightviperdark
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
perhaps you don't need to store the filenames in your workbook

try the following
create a combobox using the CONTROLS-toolbar
accept the name ComboBox1
code to fill it with filenames
Code:
Sub test()
Dim file_name As String
Dim def As String
Dim ctrl As Object
Set ctrl = ActiveSheet.ComboBox1

def = "D:\path\*.pdf"

file_name = Dir(InputBox("give directory", "DIRECTORY", def))
    With ctrl
    .Clear
        Do Until file_name = ""
        .AddItem file_name
        file_name = Dir
        Loop
    End With

End Sub
to open the files when combobox1 changes
Code:
Private Sub ComboBox1_Change()
ActiveWorkbook.FollowHyperlink Address:=ComboBox1, NewWindow:=True
End Sub

take this as a start: play with it :)

greetings,
Erik
 

nightviperdark

New Member
Joined
Sep 8, 2006
Messages
5

ADVERTISEMENT

Erik
Thanks for your quick response , but i havnt a clue on how to do what you say.Could someone guide me through what i need to do ?

Cheers
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
second menu from the left / toolbars / controls toolbar
select combobox
draw on sheet
accept default name combobox1
rightclick combobox
choose "view code"
paste this code
Code:
Option Explicit

Private Sub ComboBox1_Change()
ActiveWorkbook.FollowHyperlink Address:=ComboBox1, NewWindow:=True
End Sub

Private Sub ComboBox1_GotFocus()
Dim file_name As String
Dim def As String
Dim ctrl As Object
Set ctrl = ActiveSheet.ComboBox1

def = "D:\path\*.pdf"

file_name = Dir(InputBox("give directory", "DIRECTORY", def))
    With ctrl
    .Clear
        Do Until file_name = ""
        .AddItem file_name
        file_name = Dir
        Loop
    End With
End Sub
edit the filepath
def = "........"
return to sheet
quit edit mode (click upperleft icon on controlsd-toolbar)

click combobox to see what happens
to get rid of the inputbox asking for the path
replace
Code:
def = "D:\path\*.pdf"

file_name = Dir(InputBox("give directory", "DIRECTORY", def))
by
Code:
file_name = "D:\path\*.pdf"

some work to do :)
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
error in my reply
last part of code shoud read
Code:
file_name = Dir("D:\path\*.pdf")
still other little error discovered, wait a moment and read in 15 minutes
...
FIXED
I'm short of time now.
experiment with this
(no time to setup with messagebox for now, but perhaps you don't need it)
it works for me
Code:
Option Explicit

Private Sub ComboBox1_Change()
ActiveWorkbook.FollowHyperlink Address:="D:\bus\sw\excel\" & ComboBox1, NewWindow:=True
End Sub

Private Sub ComboBox1_GotFocus()
Dim file_name As String
Dim ctrl As Object
Set ctrl = ActiveSheet.ComboBox1

file_name = Dir("D:\bus\sw\excel\*.pdf")
    With ctrl
    .Clear
        Do Until file_name = ""
        .AddItem file_name
        file_name = Dir
        Loop
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,112,799
Messages
5,542,579
Members
410,561
Latest member
Sasha Lawrence
Top