How Do You Stop Annoying Excel Message Boxes?

[Harlan Grove]

Could this be extended , I wonder , to cover any hyperlink within a
named range of cells? More generic , so that any hyperlink clicked with
the named range would be run?

....

Define RunEXE as Sheet1!A3:A6 and enter the following into A3:A6.

winver
calc
notepad
mspaint

Enter foobar into Sheet1!A7. Make each of these a hyperlink pointing
to its own cell. Then use the event handler in Sheet1's class module.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell Target.Range.Text, vbNormalFocus
End With
End Sub

 

[Colin Hayes]

...

Define RunEXE as Sheet1!A3:A6 and enter the following into A3:A6.

winver
calc
notepad
mspaint

Enter foobar into Sheet1!A7. Make each of these a hyperlink pointing
to its own cell. Then use the event handler in Sheet1's class module.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell Target.Range.Text, vbNormalFocus
End With
End Sub

Hi

OK Thanks!

I set the named area and placed the hyperlinks in. I'm not sure what
foobar is I'm afraid , or what you meant by have each point to their own
cell. I placed the code under the sheet tab.

It's very interesting , but gave mixed results I found. One of the
hyperlinks worked perfectly with no popup (!), but another in the Named
area gave a VBA run-time error 'file not found'. This line was
highlighted in the debugger :

Shell Target.Range.Text, vbNormalFocus


I tried another sheet with the same results. I Named B4:G19 as RunEXE. I
placed 4 hyperlinks in that area , and placed your code under the tab on
the sheet. Save , close and reopen. Again , it ran the first hyperlink
successfully , but gave the above run-time error for each after. It also
selected and highlighted in blue the RunEXE area.

I really hope we can get this working because it would be a perfect
solution. It's very close. Ideally I'd like to be able to Name a broad
range of cells , placing hyperlinks with that area - running each from
the hyperlink cell without warning popups , or selecting and
highlighting.

Grateful for your advice on this.



Best Wishes

 

[Harlan Grove]

....

This was supposed to have set up a range, RunEXE, within which
hyperlinks would run external programs based on the cell contents
(hyperlink label) of the cells containing the hyperlinks. I included
foobar as a hyperlink outside the RunEXE range as a hyperlink that
would NOT run via the event handler.

. . . I'm not sure . . . what you meant by have each point to their
own cell. . . .

....

I mean cell A3 initially contains the text 'winver' (without quotes),
you move to cell A3, run Insert > Hyperlink, link to a Place in This
Document and set the cell reference to A3. So the hyperlink refers to
the cell containing it. Similarly for cells A4:A7.

I used 'points to' instead of 'refers to'. My bad.

It's very interesting , but gave mixed results I found. One of the
hyperlinks worked perfectly with no popup (!), but another in the
Named area gave a VBA run-time error 'file not found'. This line was
highlighted in the debugger :

Shell Target.Range.Text, vbNormalFocus ....
. . . Again , it ran the first hyperlink successfully , but gave
the above run-time error for each after. It also selected and
highlighted in blue the RunEXE area.

Which would indicate that you copied and pasted one hyperlink into a
multiple cell range rather than defining each one separately. When you
copy a cell containing a hyperlink and paste into a multiple cell
range, Excel seems to treat all the hyperlinks in the pasted range as
hyperlinks referring to the entire multiple cell range. The Target
hyperlink parameter in the event handler must always be a singe cell
range for this to work. So you need to create hyperlinks in each cell
separately. Tedious but necessary.

You could make the cell contents for these hyperlinks anything AS LONG
AS each cell's contents is distinct from that of all the other cells.
Then you could use a lookup table with these hyperlink labels in the
first column and the actual hyperlink addresses/references in the
second column, name the table RunTBL, and change the event handler to

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Me.Parent.Names
If Not Intersect(Target.Range, _
.Item("RunEXE").RefersToRange) Is Nothing Then _
Shell Application.WorksheetFunction.VLookup( _
Target.Range.Text, .Item("RunTBL").RefersToRange, 2), _
vbNormalFocus
End With
End Sub

 

[Colin Hayes]

Hi

Thanks again for your help. It's certainly more complicated than I
thought it might be.

As I understand it now , this is the routine :

1. I'll need to attribute Names to individual cells in the region I want
to use. So for example , I might Name A1 to 'CellA1' and A2 to 'CellA2'

2. Then I set up individual hyperlinks within each cell , setting the
'place in this cell' value to the Name of the cell that the hyperlink is
in.

3, Then in the sheet tab I place this code :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Me.Parent.Names("CellA1")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell Target.Range.Text, vbNormalFocus
End With

With Me.Parent.Names("CellA2")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell Target.Range.Text, vbNormalFocus
End With

End Sub


....And then when any of the hyperlinks is clicked , it will open the
target application with no warning pop up. Do I have this correct now?

Thanks.

^_^

 

[Harlan Grove]

As I understand it now , this is the routine :

1. I'll need to attribute Names to individual cells in the region I
want to use. So for example , I might Name A1 to 'CellA1' and A2 to
'CellA2'

Not exactly. Each cell containing a hyperlink should display something
different than any of the other cells. NONE of the cells that would
contain hyperlinks need to have defined names referring to them. You
only need to define a name for (ideally) the smallest range containing
the cells with hyperlinks you want to process through the
FollowHyperlink event handler.

Repeat: NONE of the individual cells containing hyperlinks NEEDS to be
named, i.e., given a defined name.

2. Then I set up individual hyperlinks within each cell , setting
the 'place in this cell' value to the Name of the cell that the
hyperlink is in.

'Place in This DOCUMENT'?

I suppose you could do this, but all you need to do is enter each
cell's own cell addresses as their cell reference.

3, Then in the sheet tab I place this code :

....

Your code might work, but it's unreasonably repetitive and fragile.

I already provided a working setup - tested on my own system. If you
haven't tried it, consider doing so. If you don't like it, perhaps
someone else could provide you with something you'd like better.

 

[Colin Hayes]

Hi

OK Thanks for your help and patience. I do read everything you write and
do my best to implement it correctly , I promise. I did run your
suggested setup and it gave a runtime error on my machine. I just set it
up fresh and it gave the same error.

This is what I did :

1. I highlighted and defined A1:E8 as RunEXE

2. I pasted your routine into the sheet 1 code tab :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell Target.Range.Text, vbNormalFocus
End With
End Sub

3. I put a hyperlink address into A1 , and made sure the cell reference
in 'Place in the document is A1. Save and reopen. Tested it - worked
with no popup. Perfect.

4 I put a hyperlink address into A3 , within the RunEXE area , and made
sure the cell reference in 'Place in this document' is A3. Save and
reopen. Tested it - gave run time error pointing to this line in the
debugger :

Shell Target.Range.Text, vbNormalFocus

5. I put a hyperlink address into A5 , within the RunEXE area , and made
sure the cell reference in 'Place in this document' is A5. Save and
reopen. Tested it - gave run time error pointing to this line in the
debugger :

Shell Target.Range.Text, vbNormalFocus

In summary , it seems that the first hyperlink in the RunEXE area works
fine , but subsequent links in the same area give the error. If you can
advise on this , I'd be grateful.

I'm sure you will wish to abandon me with exasperation now , but I'm
working hard to interpret your advice given the limits of my expertise ,
I promise.

Thanks again.

 

[Harlan Grove]

4 I put a hyperlink address into A3 , within the RunEXE area , and
made sure the cell reference in 'Place in this document' is A3. Save
and reopen. Tested it - gave run time error pointing to this line in
the debugger :

Shell Target.Range.Text, vbNormalFocus

This indicates that the cell doesn't evaluate to something Shell can
launch as a program.

I didn't test this thoroughly. While Windows's own Run dialog can run
urls directly, it appears VBA's Shell statement can't. To accommodate
the Shell statement's limitations, change the event handler to


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim cmd As String

cmd = Environ("COMSPEC") & " /c start "

With Me.Parent.Names("RunRng")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell cmd & Target.Range.Text, vbHide
End With

End Sub


For urls to .EXE files, this will display a dialog asking if you want
to run or save the file, which puts you back in the same place you
started at, though with a different dialog. I didn't test for that
before now either.

You could use the freely available utility wget to download .EXE files
pointed to by urls into the TEMP directory, run the downloaded .EXE
files, then delete them. Messy, but it works.

 

[Colin Hayes]

Hi Harlan

OK I ran the new code. Thanks for your expertise with this.

The three links on my sheet within the Named RunEXE area are just links
to programs on the C drive - none to URLs on the net. For the 'text to
display' for each link in turn I've put 'Access' , 'Word' and Front
Page'. These are in A1 , A3 and A5 respectively.

With the code in place , I click on 'Access' in A1 and it runs the link
with no popup - perfect.

I click on 'Word' in A2 , and it gives the error 'Windows cannot find
'Word'. Make sure you typed the name correctly and then try again. To
search for a file , click the Start button , and the click search' I
click Front Page , it gives the same error , saying 'Windows cannot Find
'Front' ..........

This is very interesting. It's clearly recognizing the RunEXE area , and
happily running the one link for Access , but not the other two. I did
move the hyperlinks around , and moved Word to A1 , and it gave the same
error. It's only Access which consistently runs correctly , irrespective
of its position on the sheet. Very curious.

I hope this helps. I do think it's close. Would it be of use if I sent
my small file on to you?



Best Wishes


Colin

 

[Harlan Grove]

With the code in place , I click on 'Access' in A1 and it runs the
link with no popup - perfect.

I click on 'Word' in A2 , and it gives the error 'Windows cannot
find 'Word'. Make sure you typed the name correctly and then try
again. To search for a file , click the Start button , and the click
search' I click Front Page , it gives the same error , saying
'Windows cannot Find 'Front' ..........

....

To run any local program without preceding the program's filename with
its full drive/directory path the program must either be in a
directory that's included in the PATH environment variable or is
included as a subkey of either of the following registry keys.

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths

HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths

Anything else, and Excel/VBA will complain it can't find the file.
Furthermore, programs with spaces in their filenames must be enclosed
in double quotes, and once you do so you'll need to provide the
optional title parameter to the start command.

Since the Access .EXE file is named MSACCESS.EXE, I suspect you have a
batch file or script file with base filename ACCESS somewhere in your
PATH. That's fine, but it'd be more robust to launch Access with its
actual filename, MSACCESS.

For the heck of it create a table somewhere else named RunTBL with the
following records.

Access MSACCESS
Word WINWORD
Front Page "Front Page"

And change the FollowHyperlink event handler code to


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Not Intersect(Target.Range, _
Evaluate("RunEXE")) Is Nothing Then _
Shell Environ("COMSPEC") & " /c start """" " & _
Application.WorksheetFunction.VLookup(Target.Range.Text, _
Evaluate("RunTBL"), 2, 0), vbHide
End Sub

 

[Colin Hayes]

Hi Harlan

OK I changed the three names in my sample file to be those of the actual
file , and all ran perfectly with no popups! I tried this out first
before trying the table idea , using your previous code.

Good stuff!

Next , I tried to apply this to the actual worksheet in the actual
workbook that I want it to run in , and encountered some problems.

I'm sure I'm doing exactly as in my trial file , but this time , the
links when run get stuck on the black cmd screen and go no further. I
can't imagine why this should be. This is on wksht 2 , and I'm putting
the code under the tab. I'm sure I've set it up exactly the same. I was
so encouraged after success in my trial set up.

Is there any configuration of an excel worksheet which would interfere
with the operation of the code?

I assume this too this will only work with .exe files. I did try to use
it with a .chm file , which should be equally executable I would have
thought , but found it objected (It gives the 'Windows cannot find ... '
warning)


Best Wishes


Colin

 

[Harlan Grove]

Next , I tried to apply this to the actual worksheet in the actual
workbook that I want it to run in , and encountered some problems. ....
I'm sure I'm doing exactly as in my trial file , but this time , the
links when run get stuck on the black cmd screen and go no further.

....

Different workbooks, but are you running both on the same computer? Is
this other workbook opened from a network location or also from a
local drive? Does anything appear in the console window? Actually, the
Shell statement shouldn't be displaying console windows since its 2nd
argument is vbHide. If you're seeing console windows when you're
trying to run GUI executables, something is going wrong in the start
command that Shell is running. So what are the command names in the
second workbook? If one of these commands were foo bar (just a
placeholder - replace it with your actual command), what happens when
you open the Run dialog (hold down the Windows logo key, usually just
to the left of the left [Alt] key, and press the R key) and enter the
command

%COMSPEC% /c start "" "foo bar"

Again: replace "foo bar" with your actual commands.

I assume this too this will only work with .exe files. I did try to
use it with a .chm file , which should be equally executable I would
have thought , but found it objected (It gives the 'Windows cannot
find ... ' warning)

Works for me using command lines like the one above.

 

[Colin Hayes]

Hi Harlan

Interesting this : I tried it all out as you suggested , I'll give in
line results :

Different workbooks, but are you running both on the same computer?

Yes , same machine.

Is
this other workbook opened from a network location or also from a
local drive?

On the same local drive.

Does anything appear in the console window?

Yes after each is run -

C\msoffice\excel>_

Cursor is blinking.

Actually, the
Shell statement shouldn't be displaying console windows since its 2nd
argument is vbHide. If you're seeing console windows when you're
trying to run GUI executables, something is going wrong in the start
command that Shell is running. So what are the command names in the
second workbook?

I'm running them from the hyperlink , so with the code in place the
command must be

%COMSPEC% /c start "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE"

If one of these commands were foo bar (just a
placeholder - replace it with your actual command), what happens when
you open the Run dialog (hold down the Windows logo key, usually just
to the left of the left [Alt] key, and press the R key) and enter the
command

%COMSPEC% /c start "" "foo bar"

Again: replace "foo bar" with your actual commands.

I entered this command via run :

%COMSPEC% /c start "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE"

This gives the black cmd box with

C:\windows\system32>_

cursor is flashing

Works for me using command lines like the one above.

Very strange - all the hyperlinks in my second workbook give this cmd
window result , whereas all the hyperlinks in my first trial workbook
connect successfully..

I must be overlooking something.

Hopefully the above will give some clue. I'd be happy to email both
books on to you if you'd like to see them first hand, They're both v
small.

Best Wishes


Colin

 

[Colin Hayes]

Harlan

As an extra to that below , when I run the command

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

form the run box , the program opens immediately and correctly. Same
with the other examples of Winword and Frontpg.

With the addition of

%COMSPEC% /c start

to the start of the command , the cmd box results.


Colin

Hi Harlan

Interesting this : I tried it all out as you suggested , I'll give in
line results :

Different workbooks, but are you running both on the same computer?

Yes , same machine.

Is
this other workbook opened from a network location or also from a
local drive?

On the same local drive.

Does anything appear in the console window?

Yes after each is run -

C\msoffice\excel>_

Cursor is blinking.

Actually, the
Shell statement shouldn't be displaying console windows since its 2nd
argument is vbHide. If you're seeing console windows when you're
trying to run GUI executables, something is going wrong in the start
command that Shell is running. So what are the command names in the
second workbook?

I'm running them from the hyperlink , so with the code in place the
command must be

%COMSPEC% /c start "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE"

If one of these commands were foo bar (just a
placeholder - replace it with your actual command), what happens when
you open the Run dialog (hold down the Windows logo key, usually just
to the left of the left [Alt] key, and press the R key) and enter the
command

%COMSPEC% /c start "" "foo bar"

Again: replace "foo bar" with your actual commands.

I entered this command via run :

%COMSPEC% /c start "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE"

This gives the black cmd box with

C:\windows\system32>_

cursor is flashing

Works for me using command lines like the one above.

Very strange - all the hyperlinks in my second workbook give this cmd
window result , whereas all the hyperlinks in my first trial workbook
connect successfully..

I must be overlooking something.

Hopefully the above will give some clue. I'd be happy to email both
books on to you if you'd like to see them first hand, They're both v
small.

Best Wishes


Colin

 

[Harlan Grove]

I entered this command via run :

%COMSPEC% /c start "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE"

This gives the black cmd box with

C:\windows\system32>_

You need to follow my instructions EXACTLY! And you need to stop
guessing that there may be typos in my instructions. If something I've
included doesn't make sense to you, you should ask about it.

The "" before "foo bar" in my sample command line WASN'T optional.

Try the following EXACT command line.

%COMSPEC% /c start "" "C:\Program Files\Microsoft Office
\OFFICE11\MSACCESS.EXE"

The start command treats the "" as it's window title argument, and
it's NECESSARY when you need to put quotes around the command name
because it has embedded spaces.

 

[Colin Hayes]

You need to follow my instructions EXACTLY! And you need to stop
guessing that there may be typos in my instructions. If something I've
included doesn't make sense to you, you should ask about it.

The "" before "foo bar" in my sample command line WASN'T optional.

Try the following EXACT command line.

%COMSPEC% /c start "" "C:\Program Files\Microsoft Office
\OFFICE11\MSACCESS.EXE"

The start command treats the "" as it's window title argument, and
it's NECESSARY when you need to put quotes around the command name
because it has embedded spaces.

Hi Harlan

Thanks - I do see what you're saying , and do always try to paste
directly so as not to make typos. However it's this code , rather than
the one you highlight above , which gives the error of the hanging cmd
screen :


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim cmd As String

cmd = Environ("COMSPEC") & " /c start "

With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell cmd & Target.Range.Text, vbHide
End With

End Sub


Any idea what might be causing this?


Best Wishes


Colin

 

[Harlan Grove]

. . . However it's this code , rather than the one you highlight
above , which gives the error of the hanging cmd screen :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim cmd As String

cmd = Environ("COMSPEC") & " /c start "

With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell cmd & Target.Range.Text, vbHide
End With

End Sub

Any idea what might be causing this?

Yes. You're not implementing all the changes I've been throwing at
you. If you're not going to implement a table mapping cell contents
from hyperlinks in the RunEXE range into command names, and you're not
going to include double quotes in the cell contents for the hyperlinks
in RunEXE, then you'll need to put those double quotes into the event
handler.

REPLACE THE CODE ABOVE WITH THE FOLLOWING **EXACT** CODE.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim cmd As String

cmd = Environ("COMSPEC") & " /c start """" "

With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell cmd & """" & Target.Range.Text & """", vbHide
End With

End Sub

 

[Colin Hayes]

Yes. You're not implementing all the changes I've been throwing at you. If you're
not going to
implement a table mapping cell contents from hyperlinks in the RunEXE range into
command
names, and you're not going to include double quotes in the cell contents for the
hyperlinks in
RunEXE, then you'll need to put those double quotes into the event handler.

REPLACE THE CODE ABOVE WITH THE FOLLOWING **EXACT** CODE.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim cmd As String

cmd = Environ("COMSPEC") & " /c start """" "

With Me.Parent.Names("RunEXE")
If Not Intersect(Target.Range, .RefersToRange) Is Nothing Then _
Shell cmd & """" & Target.Range.Text & """", vbHide
End With

End Sub

Hi Harlan

OK Thanks. I'm grateful for your perseverance.

I pasted the code into a new sheet , set up several hyperlinks and it
worked perfectly immediately. Hooray! Except on .chm.

I then pasted it into the actual wb I want to use it in , and it opens
the program folder that the wb itself is in , displaying the files. (!).
Most curious. There must be something in my existing wb that is
interfering with the code , that is not present in a completely new
sheet.

I wonder if something is going awry in the email transport.

I'll keep playing with it. I hesitate to prolong this now , as you been
so helpful. I don't want to keep trying your patience. Hopefully I can
get it going in a reliable and predictable fashion. I'll let you know ,
of course.



Best Wishes


Colin