| View previous topic :: View next topic |
| Author |
Message |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
 Posted: Sat Dec 24, 2005 3:18 am Post subject: |
 |
|
| Yes, HYPERLINK() removes the grey background and if you read the online-help, you see how to do it right. |
|
| Back to top |
|
 |
MLReynolds
General User
Joined: 21 Dec 2005
Posts: 9
|
| Posted: Sat Dec 24, 2005 5:47 am Post subject: Hyperlink text format in Calc |
|
|
Villeroy - Thank you, and my apologies.
Yes, the HYPERLINK() functions does remove the gray background.
What I meant to say was that the result was not a "clickable" link.
This is what I have done:
Cell A3 contains a working hyperlink to another worksheet.
Cell B3 contains; =HYPERLINK(A3)
The result in B3 is the displayed text of the link in A3, without the gray shading background.
However, even though my mouse cursor indicates that B3 contains a link, nothing happens when I click on it.
If I'm over looking something, I can't imagine what it is. I appreciate very much the suggestions I've recieved in this Forum. Especially from people like you, Villroy, who take the time to post more than once when necessary. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Sat Dec 24, 2005 12:33 pm Post subject: |
|
|
Excuse my confusion in last posting. I had already sent, when I realized that
plain formatting without HYPERLINK()-formula does not remove grey shading.
Online help on HYPERLINK():
HYPERLINK (URL) or HYPERLINK (URL;Cell Text)
| Quote: | | I'm using the “Account Name” as a clickable link to a named cell near the bottom of the sheet for the named account so only the last ten to twenty entries are in view. |
Our HYPERLINK($A1) uses the account-name as URL.
We need 2 columns. One for the URLs and one for the account-names.
Assuming column A displaying the names and B for URLs and a new column C for
the Hyperlink()-formula:
Formula in C1 =HYPERLINK($B1;$A1)
should create white hyperlinks with URLs from column B1 displaying the text of
A1. Since you use some ranges within the same document as link targets:
The URLs are something like #Accounts.A100, where "Accounts" is the
sheet-name.
Unfortunately it is difficult to get your original URLs(much selecting and clicking)
You lucky one! I can provide a macro.
Tools>Macros>Organize>Basic
Button "Organizer"
Select library "Standard" in container "my Macros"
Button "New..."
Accept default name "Module1" or something alike
Button "Edit"
Replace all content of your new module with the following code:
| Code: |
sub extract_1st_HyperlinkURL_to_RightNeighbour
with thiscomponent.currentcontroller.selection
oSheet = .SpreadSheet
oEnum = .queryContentCells(com.sun.star.sheet.CellFlags.STRING).getCells.createEnumeration
end with
while oEnum.hasMoreElements
oSourceCell = oEnum.NextElement
oTargetAddr = oSourceCell.CellAddress
oTargetCell = oSheet.getCellByPosition(oSourceCell.CellAddress.Column +1,oSourceCell.CellAddress.Row)
if oSourceCell.Textfields.Count >0 then
URL = oSourceCell.Textfields.getByIndex(0).URL
oTargetCell.setString(URL)
endif
wend
end Sub
|
In your document select a range (one column width!) with grey text-hyperlinks
and provide free cells right of those cells.
Tools>Macro>Execute
Browse to the macro in
"myMacros">Standard>Module1>extract_1st_HyperlinkURL_to_RightNeighbour
It fills the right neighbours of currently selected cells with the URLs,
detected in the selected cells. Ignores cells without a hyperlink, ignores
more than 1 hyperlink per cell. That's it.
Now you can reassemble your hyperlinks from the displayed text of the old,
grey hyperlinks and the extracted URLs using the HYPERLINK()-function.
The hyperlinks, created by the function do not have the grey shading and they
are editable easily through their source-cells.
Merry Christmas! |
|
| Back to top |
|
|
MLReynolds
General User
Joined: 21 Dec 2005
Posts: 9
|
| Posted: Sun Dec 25, 2005 2:10 am Post subject: Hyperlink Text Format in Calc - Solved! |
|
|
Villeroy - Bingo!
Thank you very much.
I can be slow at times, but eventually come around.
| Quote: | Our HYPERLINK($A1) uses the account-name as URL.
We need 2 columns. One for the URLs and one for the account-names.
Assuming column A displaying the names and B for URLs and a new column C for
the Hyperlink()-formula:
Formula in C1 =HYPERLINK($B1;$A1)
should create white hyperlinks with URLs from column B1 displaying the text of
A1. Since you use some ranges within the same document as link targets:
The URLs are something like #Accounts.A100, where "Accounts" is the
sheet-name. |
My original confusion was using; C1 =HYPERLINK($A!;$B!)
As I said; I can be slow.
The macro you provided did make the operation much easier.
Thank you for your patience, and for not giving up on me. I hope that I may return the favor some day. If not to you, then to someone as slow as myself. You are a credit to this forum community.
Merry Christmas, and a very Happy New Year to you.
Best Regards,
Michael Reynolds |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Sun Dec 25, 2005 2:25 am Post subject: Hyperlink shading from text document setting |
|
|
MLReynolds, you are wrong. I have adjusted the setting in question and the grey shading in my Calc hyperlinks has gone! I found it after posting to the effect that the grey shading could not be altered - I was only playing with the possibility and lo!, no more grey shading.
Have you tried it? |
|
| Back to top |
|
|
MLReynolds
General User
Joined: 21 Dec 2005
Posts: 9
|
| Posted: Mon Dec 26, 2005 12:07 am Post subject: |
|
|
noranthon - Thank you very much for your help.
I have made the same settings changes you mentioned, yet the shading hasn't gone away in Calc on my PC. Don't know why, but it is still there. Perhaps different OS?
Any way, have found acceptable work around and am happy.
Thanks again...Happy New Year!
Michael |
|
| Back to top |
|
|
|
