Hyperlinks in a ComboBox

[nightviperdark]

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

 

[erik.van.geit]

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]

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]

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]

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]

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

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

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

take this as a start: play with it

greetings,
Erik

 

[nightviperdark]

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]

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

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

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

file_name = Dir(InputBox("give directory", "DIRECTORY", def))

by

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

some work to do

 

[erik.van.geit]

error in my reply
last part of code shoud read

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

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

 

[nightviperdark]

That works........

Many Thanks

No where to come in future when I get stuck