Listbox
 

[font=Times New Roman]::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. :)
:

--
harryward2
-----------------------------------------------------------------------
harryward27's Profile: http://www.excelforum.com/member.php...fo&userid=2549
View this thread: http://www.excelforum.com/showthread.php?threadid=38950

Listbox
 

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. :)
::

Listbox
 

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


--
robhargreaves
------------------------------------------------------------------------
robhargreaves's Profile: http://www.excelforum.com/member.php...o&userid=25519
View this thread: http://www.excelforum.com/showthread...hreadid=389501

Listbox
 

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

Listbox
 

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"
wrote in message
...

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

Listbox
 

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 :)