Can you stop the follow hyperlink function if a listbox or listview is empty on click?

[laurawr]

Hi,

I have a listview and a list box in 2 different userforms, and when i click on a line of data I am using the followhyperlink command to open the hyperlink in that line. However if the listbox or listview is empty and have no data in, can you stop the follow hyperlink function?

here is my code for each..

LISTVIEW -

Private Sub ListView1_Click()
 
Dim navigatetoURL As Variant
navigatetoURL = ListView1.SelectedItem.SubItems(1)
ActiveWorkbook.FollowHyperlink Address:=ListView1.SelectedItem.SubItems(1), NewWindow:=True
 
End Sub

LISTBOX -

Private Sub ListBox1_Click()
Dim navURL
navURL = ListBox1.Value
ActiveWorkbook.FollowHyperlink Address:=navURL, NewWindow:=True
End Sub

Help is much appreciated

Thanks

L.

 

[Gary McMaster]

You may have to use "IsEmpty" or "IsNull" instead of what I've shown below but can't you just check to make sure a URL, or at least something, exists before you try and follow it? Same for the ListView.

Gary

Private Sub ListBox1_Click()

Dim navURL
navURL = ListBox1.Value

If navURL = "" Then
    MsgBox "Invalid URL"
Else
    ActiveWorkbook.FollowHyperlink Address:=navURL, NewWindow:=True
End If

End Sub

 

[laurawr]

Thats great!!

Tust another thing,
When a user opens a hyperlink and the security msgbox in excel comes up to confirm opening it,
When i press cancel i would like it to return to the userform, but however it brings up a debug error as it cannot follow the hyperlink

any ideas on how to do this?

Thanks
L

 

[Gary McMaster]

I'm not familiar with the error but you could try:

On Error Resume Next

You can put that in your code wherever you expect an error to occur. It causes all errors to be ignored from that point on.

After the point where the error is expected, it is good practice to add:

On Error Goto 0

This turns off the effects of On Error Resume Next. Help says it is turned off when the procedure ends but another member (PGC01) has proven that statement in VBA help to be innacurate.

You could also include a formal Error Handler, please see help for that

Gary

 

[laurawr]

hmm.
It isnt working but will have a go at other suggestions you made.

When i press cancel on the default security msgbox excel gives for opening hyperlinks "harmful files etc", i press cancel and it blocks the follow hyperlink command from working as it cannot open the destination and the onerror command doesnt work either :/

Thanks for your help

Much appreciated

 

[Gary McMaster]

Just to be sure I didn't confuse you, the On Error Resume Next should go before the statement that tries to follow the hyperlink, not after.

In fact you can add it as the very first statement in your procedure. That will remove any doubt that you may have it in the wrong place.

Gary

 

[laurawr]

Oh!

I feel silly now haha

Works perfectly !

Thanks!