Workbook becomes hidden after link to webpage

Hi

I have a link in a workbook that is created by a user-defined
worksheet function. Typically when I click the link, and then go back
to Excel, the workbook becomes hidden. Occasionally it works (and if I
remember correctly it used to work all the time).

Anyone encountered this? Any ways to quash it mightily? I'd like to
understand how to avoid it because I send such spreadsheets to my
users.

Cheers
Simon

I have a link in a workbook that is created by a user-defined
worksheet function. Typically when I click the link, and then go back
to Excel, the workbook becomes hidden. Occasionally it works (and if I
remember correctly it used to work all the time).

Sorry, that's rather stupid of me, this forum would be about linking
to other data whereas I'm talking about a hyperlink. Oh well, I guess
my question stands for anyone game...

Simon

Hi Simon,
I get kind of the opposite reaction.

=HYPERLINK("http://www.ibm.com","IBM")

if I click on the above, it opens up a new window in Internet Explorer.
I can click on Excel on the taskbar and I return to Excel not hidden.
Or I can click on the browser's back button (or Alt+LtArrow) to go
back to Excel. From Excel I can use the Forward web toolbar button
to return to the IBM webpage. But note before hitting the forward button
I don't see the pertainent IE icon on the taskbar because it disappeared,
but it will reappear when I hit the forward button or click on the link again.

How are you returning to Excel, are you returning from the taskbar.
The taskbar may have more than one row and you have to scroll.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Simon Hobbs" wrote in message om...
I have a link in a workbook that is created by a user-defined
worksheet function. Typically when I click the link, and then go back
to Excel, the workbook becomes hidden. Occasionally it works (and if I
remember correctly it used to work all the time).

Sorry, that's rather stupid of me, this forum would be about linking
to other data whereas I'm talking about a hyperlink. Oh well, I guess
my question stands for anyone game...

Simon

I can click on Excel on the taskbar and I return to Excel not hidden.

Yes, I click on Excel Icon on the taskbar and Excel returns but the
workbook is not there. I click WindowUnhide and I can unhide the
workbook. After I've done this once then everything works ok.

I have replicated the problem using this simpler version of my
function:
***CODE START***

Public Function tester(ThisCell As Range) As String
ActiveSheet.Hyperlinks.Add Anchor:=ThisCell, Address:= _
"http://www.ibm.com"
End Function

***CODE END***
So I created a new workbook.
I then typed into A1: "=tester(B1)"
I then saved the file
I then clicked the link
I then select excel on the taskbar and excel appeared with the
workbook hidden.

Or I can click on the browser's back button (or Alt+LtArrow) to go
back to Excel. From Excel I can use the Forward web toolbar button
to return to the IBM webpage.

Yes, this works fine thankyou, I didn't know about this functionality.
So, unless you can show me how to avoid the problem (because sometimes
my users might want to keep the new window open) I will encourage
people to use the Back button.

thx
S.

A function can only return a value -- so your function is not valid.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Simon Hobbs" wrote in message om...
I can click on Excel on the taskbar and I return to Excel not hidden.

Yes, I click on Excel Icon on the taskbar and Excel returns but the
workbook is not there. I click WindowUnhide and I can unhide the
workbook. After I've done this once then everything works ok.

I have replicated the problem using this simpler version of my
function:
***CODE START***

Public Function tester(ThisCell As Range) As String
ActiveSheet.Hyperlinks.Add Anchor:=ThisCell, Address:= _
"http://www.ibm.com"
End Function

***CODE END***
So I created a new workbook.
I then typed into A1: "=tester(B1)"
I then saved the file
I then clicked the link
I then select excel on the taskbar and excel appeared with the
workbook hidden.

Or I can click on the browser's back button (or Alt+LtArrow) to go
back to Excel. From Excel I can use the Forward web toolbar button
to return to the IBM webpage.

Yes, this works fine thankyou, I didn't know about this functionality.
So, unless you can show me how to avoid the problem (because sometimes
my users might want to keep the new window open) I will encourage
people to use the Back button.

thx
S.

David McRitchie wrote:
A function can only return a value -- so your function is not valid.


That was my first reaction too, until I tried it.
Whether it is a bug or a feature is debatable.
If you modify the function to

Public Function tester(ThisCell As Range, site As String) As String
ActiveSheet.Hyperlinks.Add Anchor:=ThisCell, Address:= _
"http://www." & site & ".com"
End Function

and enter
=tester(C4,"abc")
you get a link to www.abc.com

If you then change the formula to
=tester(C4,"ibm")
the text in C4 doesn't change to show ibm but the hyperlink does
change. If you then select C4 and press Del the text changes to agree
with the hyperlink.
Interesting.....


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

"David McRitchie" wrote in message ...
A function can only return a value -- so your function is not valid.

My pc had to deactivate it's logic circuits to get around that one!

It is turning out to be very useful actually. If I get a large list of
client ID's in a text file:
- I import into XL
- enter the function in B1 to build a link around the client ID from
A1
- copy and paste the function down the page to instantly create
multiple links.

v cool
s.

"David McRitchie" wrote in message ...
A function can only return a value -- so your function is not valid.

Oh yes, you're right. I accidently cut out the line that specifies the
return value. Full code below:

***CODE START***
Public Function PSM_ViewCLient(ClientID As Long, ThisCell As Range) As
String
PSM_ViewCLient = ClientID
ActiveSheet.Hyperlinks.Add Anchor:=ThisCell, Address:= _
"http://au.csl.prismsweb.com/prisms/clients/info.tcl?eid=" &
ClientID
End Function
***CODE START***

Hi Simon,
What I am saying is that it is not the way a function is supposed
to be used on a worksheet; and therefore it is unsupported.
That means you are using it at your own risk as to whether it works,
how it works, whether it will continue to work, or whether it
will work in a later version.

Every once in while someone posts a demonstration of how to
make a function act as a subroutine, but it is strictly posted as
an oddity and not something that they would ever use in a client's
workbook.

So if it doesn't work exactly the way you want, is not consistent, or
has any problems, you would never get help from Microsoft because
it is unsupported, and such use would be extremely risky..

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Simon Hobbs" wrote in message om...
"David McRitchie" wrote in message ...
A function can only return a value -- so your function is not valid.

Oh yes, you're right. I accidently cut out the line that specifies the
return value. Full code below:

***CODE START***
Public Function PSM_ViewCLient(ClientID As Long, ThisCell As Range) As
String
PSM_ViewCLient = ClientID
ActiveSheet.Hyperlinks.Add Anchor:=ThisCell, Address:= _
"http://au.csl.prismsweb.com/prisms/clients/info.tcl?eid=" &
ClientID
End Function
***CODE START***

"David McRitchie" wrote in message ...
So if it doesn't work exactly the way you want, is not consistent, or
has any problems, you would never get help from Microsoft because
it is unsupported, and such use would be extremely risky..

Yes, I have to acknowledge your point. In this case I am simply
whipping up adhoc single-use solutions. Whenever I contract to
implement a system (that I have to walk away from) I avoid adding code
altogether.