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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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
 

Forum statistics

Threads
1,141,427
Messages
5,706,396
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top