Listbox-excel to external files

[harryward27]

[FONT=Times New Roman]Good evening
I am trying to link data in a listbox within a form with excel to external files,ie pdfs.

I can hyperlink cell value but not listbox values.

Can anybody help.

[Gareth]

Hi Harry,

One suggestion would be to create hyperlinks dynamically. i.e. when the
user selects a list box item create a hypertext hyperlink, follow it and
then delete it.

For example, assuming you have ListBox1 embedded on your worksheet,
place the below code in a VBA module.

Sub ListBox1_DoubleClick()

Dim HL As Hyperlink

With ActiveSheet.Shapes("List Box 1").OLEFormat.Object
If .List(.ListIndex) <> "" Then
Debug.Print .List(.ListIndex)
'create a hyperlink in range A1, say
Set HL = ActiveSheet.Hyperlinks.Add _
(ActiveSheet.Range("A1"), .List(.ListIndex))
HL.Follow
HL.Delete
End If
End With

End Sub

HTH,
Gareth


harryward27 wrote:
> [FONT=Times New Roman]::Good evening
> I am trying to link data in a listbox within a form with excel to
> external files,ie pdfs.
>
> I can hyperlink cell value but not listbox values.
>
> Can anybody help.
> ::
>
>

[robhargreaves]

Harry maybe you could use indirect and write refs into a sheet then you dont have to alter the code either.

[harryward27]

Thank you for your replies.

I wanted not to put anything in any cells but I have changed tac on the project.

I still would like to know if it is possible to link to listbox without putting any thing in a cell.

Cheers

[STEVE BELL]

One of many ways is to just create a variable

Dim tbox1 as String

tbox1 = Userform1.Textbox1.Value


than do what ever you want with tbox1

--
steveB

Remove "AYN" from email to respond
"harryward27" <[email protected]>
wrote in message
news:[email protected]...
>
> Thank you for your replies.
>
> I wanted not to put anything in any cells but I have changed tac on
> the project.
>
> I still would like to know if it is possible to link to listbox without
> putting any thing in a cell.
>
> Cheers
>
>
> --
> harryward27
> ------------------------------------------------------------------------
> harryward27's Profile:
> http://www.excelforum.com/member.php...o&userid=25499
> View this thread: http://www.excelforum.com/showthread...hreadid=389501
>

[Gareth]

Hi Harry,

Do you mean that you want a hyperlink to "launch" when the user clicks
on the listbox item - and not mess about with the worksheet at all?
(Although it might be nicer to have them select a link and click ok.)

You could place the below code in a VBA module (as before). This code
just launches Acrobat and whatever file is in the listbox. There may
well be a tidier way to launch Acrobat thouhg.

Sub ListBox1_DoubleClick()

Dim AcroID As Double

With ActiveSheet.Shapes("List Box 1").OLEFormat.Object
If .List(.ListIndex) <> "" Then
AcroID = _
Shell("c:\program files\adobe\acrobat 6.0\reader\acrord32.exe " _
& .List(.ListIndex), vbNormalFocus)
AppActivate AcroID
End If
End With

End Sub

HTH,
Gareth


harryward27 wrote:
> Thank you for your replies.
>
> I wanted not to put anything in any cells but I have changed tac on
> the project.
>
> I still would like to know if it is possible to link to listbox without
> putting any thing in a cell.
>
> Cheers
>
>