Listbox selection follows hyperlink
 

Hi all,

I've created a form (with much needed help from those in this forum),
which displays a listbox listing data in 3 columns - A2:C35 in
sheets("DATA"). In Sheets("DATA") column J2:J35, I have a hyperlink
created from the data listed in columns A2:C35. My ultimate goal is to
be able to click on a row in my listbox in userform1 and press a command
button on the form that will open netscape and follow the hyperlink.
Here's what I have so far... Can anyone point me in the right direction
? Thanks so much for your time.

Private Sub CommandButton2_Click()
Dim sPath As String
Dim sURL As String
Dim dTaskID As Double
Dim i As Long
Dim txt As Control
i = Me.ListBox1.ListIndex + 2
sURL = Me.ListBox1.Value
Me.ListBox1.Value = Sheets("DATA").cells(i, 7).Value

sPath = "C:\Program Files\Netscape\Communicator\Program\netscape.exe"
'my browser

dTaskID = Shell(sPath & " " & sURL, vbNormalFocus)

If dTaskID = 0 Then
MsgBox "Could not open browser"
End If
End Sub

Oreg


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=477391

Listbox selection follows hyperlink
 

Hello Oreg,

From your description of the data on the worksheet, it wasn't clear t
me if column J2:J35 contains URL address strings or actual hyperlink
(blue underlined text).

I am going to assume that the "hyperlinks" are URL string addresses
since you call the Shell command. I hope you will let me know if thi
isn't the case.



Code
-------------------
Private Sub CommandButton2_Click()
Dim sPath As String
Dim sURL As String
Dim dTaskID As Double
Dim i As Long
Dim txt As Control

If ListBox1.ListIndex = -1 Then
MsgBox "Please select a URL from the list."
Exit Sub
End If

i = Me.ListBox1.ListIndex + 2
sURL = Sheets("DATA").cells(i, 7).Value

'This will run the default browser. If Netscape isn't your default browser
'There is another way to do this callin the browser of your choice.
'If you want that code let me know.

Call Shell("rundll32.exe url.dll,FileProtocolHandler " & sURL, vbNormalFocus)

End Sub

-------------------


If you have any questions, corrections, or need further help, contac
me at .

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47739

Listbox selection follows hyperlink
 

Hi Leith,

I've used a few functions to concatenate cells a:b. Column j is an
IF(I1<"",HYPERLINK.... function. So the text is blue, but column J is
a formula. I'm not sure exactly if the code you've provided fits the
scenario I've described, but I can't wait to try it out this morning!
If you could provide the code I can use to call netscape instead of IE
that would be great.

Once again, thank you..

Oreg


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=477391

Listbox selection follows hyperlink
 

Hello Oreg,

This macro module will allow to set the browser you want to use and
allow you to call up either a URL or email address. Insert a VBA Module
into your project and copy and paste the code into it.

TO SET YOUR BROWSER:
SetBrowserPath ("C:\Program
Files\Netscape\Communicator\Program\netscape.exe")

If you don't set the browser path, the system will use the default
browser. Once you have set the browser path, you won't need to set it
again, unless you decide to change browser. The setting is saved in the
registry.

GO TO A WEBSITE:

Using a String Variable...
URL = "http://www.google.com/"
GoToWebsite (URL)

Direct Assignment...
GoToWebsite ("http://www.google.com/")


MODULE CODE


Code:
--------------------
'////////////////////////////////////////////'
'/ /'
'/ This Module will connect to an Internet /'
'/ Website (URL) using the default browser /'
'/ or the browser of your choosing. /'
'/ /'
'/ This code works with Windows '95, '98, /'
'/ ME, 2000, and XP. /'
'/ /'
'/ /'
'/ Copyright Feb. 2001 /'
'/ Author: Leith Ross /'
'/ Company: Ross Associates /'
'/ /'
'/ This code may be used and distributed /'
'/ only with the inclusion of this notice. /'
'/ /'
'////////////////////////////////////////////'


'This will Launch the Browser and go to the URL
Private Declare Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

'This Call is Used to Find the Default Browser
Private Declare Function FindExecutable _
Lib "shell32.dll" _
Alias "FindExecutableA" _
(ByVal lpFile As String, _
ByVal lpDirectory As String, _
ByVal lpResult As String) As Long

'Returns the Handle of the Active Window
Private Declare Function GetActiveWindow _
Lib "User32.dll" () As Long


Private Function GetDefaultBrowser() As String

Dim FileName As String, Dummy As String
Dim RetVal As Long
Dim FileNumber As Integer
Dim DefaultBrowser As String * 260

' First, create a known, temporary HTML file
DefaultBrowser = Space(260)
FileName = "C:\Temphtm.htm"
FileNumber = FreeFile ' Get unused file number
Open FileName For Output As #FileNumber ' Create temp HTML file
Write #FileNumber, "<HTML <\HTML" ' Output text
Close #FileNumber

' Then find the application associated with it
RetVal = FindExecutable(FileName, Dummy, DefaultBrowser)
N = InStr(1, DefaultBrowser, vbNullChar)
DefaultBrowser = Left(DefaultBrowser, N)

Kill FileName

' Check If a Browser Exists
If RetVal <= 32 Or IsEmpty(DefaultBrowser) Then
MsgBox "Could not find associated Browser", vbExclamation, _
"Internet API"
GetDefaultBrowser = ""
End If

' Return the Fully Qualified Path to the Browser
GetDefaultBrowser = DefaultBrowser

End Function


Public Sub GoToWebSite(ByVal Site_Address As String)

Dim RetVal
Dim TaskID

Dim N As Long
Dim X As Long
Dim CurWin As Long
Dim NoData As String
Dim AppPath As String
Dim URL As String

Dim Msg As String
Dim Title As String
Dim Buttons As Integer

Buttons = vbOKOnly + vbCritical
Title = "Internet API"

CurWin = GetActiveWindow
NoData = vbNullString
URL = Site_Address

AppPath = GetSetting("URL Code", "Browser", "Path", "")
If AppPath = "" Then
AppPath = GetDefaultBrowser
End If

'Check for Address
N = InStr(1, URL, "://")

'Check for E-Mail
X = InStr(1, URL, "@")

'Default Protocol is Http
If N = 0 And X = 0 Then
URL = "http://" & URL
Else
'E-Mail Address
If N = 0 And X 0 Then
URL = "mailto:" & Trim(URL)
End If
End If

TaskID = ShellExecute(CurWin, "Open", AppPath, URL, NoData, vbNormalFocus)
'Call Shell("rundll32.exe url.dll,FileProtocolHandler " & URL, vbNormalFocus)

' Did Connection Fail? Errors are from 0 to 32
If TaskID < 33 Then
Msg = "Unable to Connect to " & URL & vbCrLf _
& "Error Number " & Str(TaskID)
RetVal = MsgBox(Msg, Buttons, Title)
End If

End Sub

Public Sub SetBrowserPath(ByVal Browser_Path As String)

SaveSetting "URL Code", "Browser", "Path", Browser_Path

End Sub

--------------------


--
Leith Ross


------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=477391

Listbox selection follows hyperlink
 

Hi Leith,


I can't say thank you enough. I'm now able to switch back and forth
between browsers dependant upon what I need to log in to. This is way
more than I ever expected !!!

THANK YOU ! :) :)

Oreg


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=477391