Listbox

[harryward27]

::Good evening
I am trying to link data in a listbox within a form with excel t
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

 

[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]...

 

[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