Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links on / off (ExcelXP)

    Don't know where I find them but here's another nasty one I'm putting my teeth in...

    I have two ranges: a source-range and (in another sheet -but in the same workbook-) a target range.

    The target-range is visible to the end user and just shows the source-range (in most cases). So, it's very simple (and standard Excel):

    Target range:

    <table border=1><td>Cell address</td><td>Formula</td><td>B10</td><td>=SourceSheet!F33</td><td>B11</td><td>=SourceSheet!F34</td><td>B12</td><td>=SourceSheet!F35</td></table>

    So far, so good. Apart from some stuff I do in VBA this is basically what it boils down to. Now here's the problem... in some situations I want to display a link in a target-cell.

    What I did was make a (very) small VBA sub, and organize things in such a way that I can enter the link into the source range as TEXT preceded with the word "Link:". The code recognizes this, removes the "Link:" part and creates a hyperlink in the target-cell for the rest of the string (ideally I'd also like to control the displayed text here but that's an enhancement, for now I just display <<Linked Cell>>)

    Example:
    In SourceSheet!F34, I enter: Link: www.wopr.com
    Now, I want my target cell (B11), to hyperlink to www.wopr.com (but display the text: << Linked Cell>>)

    The code:
    Sub t()
    If LCase(Left(Range(Target).Value & " ", 5)) = "link:" Then
    ActiveSheet.Hyperlinks.Add Anchor:=Range(Target), Address:= Trim(Mid(Range(Target), 6)), TextToDisplay:="<< Linked Cell >>"
    Else
    Range("f10").Hyperlinks.Delete
    End If
    End Sub

    Some observations:
    > When I create the Hyperlink in the target-cell, the formula (that links to the source) is NOT removed (which is a GOOD thing for me here!)
    > I cannot really make this work (e.g. I don't see the "TextToDisplay" field)... what's wrong?
    > When I remove the "Link:" in the source-sheet, I want to remove the Hyperlink in the target-location and revert back to the normal behavior & display. When I delete the hyperlink however my cell formatting completely disappears... (I can of course use VBA to set that back up but I don't like it this way...)

    Any suggestion is welcome... hope I explained things clear enough....

    EJ

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links on / off (ExcelXP)

    Not completely clear to me.
    Why not just use the hyperlink function?:

    =HYPERLINK("http://www.wopr.com","<<Linked cell>>")

    If you want to be able to remove the term "Link:" and have things change (automatically) you will have to use a change event and run code to do what you need. Removing a hyperlink removes the formatting (I don't think, there is a way around this directly). You could add conditional formatting as this is not removed when a hyperlink is deleted.

    The hyperlink function when removed from the cell also does not change the formatting. This could be a problem since it also keeps the blue underlined or magenta underlined text of the hyperlink.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links on / off (ExcelXP)

    Yeah.. I thought about that one... actually here's a formula I implemented earlier (as I'm always trying to do things directly like this before I revert back to VBA):

    This is a test where the source cell is in F7: =IF(LOWER(LEFT(F7 & " ",5)="link:"),HYPERLINK(TRIM(MID(F7,6,100)), "<<Linked cell>>"),F7)

    So basically, if F7 starts with "Link:", I enter the HYPERLINK-formula (cutting off the "Link:" part first), else I just enter F7

    Indeed I found out that with conditional formatting I can hide the link-formatting (didn't know that, thanks for that tip!).

    The problem however is that even if I'm NOT displaying a Link in the cell (when F7 does NOT start with "Link:"), the cell always REMAINS behaving like a cell with a hyperlink (even if I delete the hyperlink from the cell manually [right-click: remove hyperlink] or via VBA [Range(Target).Hyperlinks.Delete])... now if that could be solved...

    EJ

    PS. I could of course clear the cell in VBA each time and re-write the right formula in it... so either the "=Hyperlink()" part or just "=F7". Problem here is that I simplified a bit... F7 isn't really F7 but something a little more complex.... It might be possible however, just looking for an easier solution.... ;-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links on / off (ExcelXP)

    I can confirm that in XL97 (don't know if different in later versions) that if you use HYPERLINK within a formula, it makes the cell a hyperlink. This is good if you are in the TRUE part of your code and want the hyperlink, but is bad in the FALSE part (with does not use the hyperlink at all) since it tries to link to whatever is in the reference cell, which is no longer a hyperlink.

    I know know way around this directly.

    You could add a Change event based on the cells that you are going to have/not have the "LINK:" added and if they change, change the cell to be linked. The problem would be keeping track of the dependencies.

    Could you be more specific about what you are trying to accomplish? If you are going to edit the cells to add or remove link, why not just edit the link?

    You could also have based on change or calc event and look for the cells which have formulas containing "<<Linked Cell>>,
    If the value is not <<Linked Cell>> change the formula to Mispell "hyperlink" and it will be broken (but not give an error)
    If the value is <<Linked Cell>> then make sure the formula has "hyperlink" spelled correctly

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links on / off (ExcelXP)

    I assume that whether you have the "link:" at the beginnig of F7 is based on a formula in F7 (not physically editing it), so F7 will change based on a calc event not a change event, so you could use something like this code. It will loop thru all the formulas in the active sheet and ones without links will have the Hyperlink portion renamed so that it is not a link and ones that are listed as <<LinkedCell>> will have the formula

    The code goes in the sheet object not a regular module so it is triggered by a calculation.
    Steve

    <pre>Private Sub Worksheet_Calculate()
    Dim sLink As String
    Dim sHL As String
    Dim sAHL As String
    Dim sFind As String
    Dim sRepl As String
    Dim rForms As Range
    Dim rCell As Range
    sLink = "<<Linked cell>>"
    sHL = "HYPERLINK("
    sAHL = "HYPER-LINK("
    Set rForms = UsedRange.SpecialCells(xlFormulas)
    For Each rCell In rForms
    If InStr(rCell.Formula, sHL) + InStr(rCell.Formula, sAHL) <> 0 Then
    If IsError(rCell.Value) Then
    sFind = sAHL
    sRepl = sHL
    ElseIf rCell.Value = sLink Then
    sFind = sAHL
    sRepl = sHL
    Else
    sFind = sHL
    sRepl = sAHL
    End If
    Application.EnableEvents = False
    rCell.Formula = Application.WorksheetFunction.Substitute _
    (rCell.Formula, sFind, sRepl)
    Application.EnableEvents = True
    End If
    Next
    End Sub</pre>


  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links on / off (ExcelXP)

    Cool... looks an interesting approach... thanks! Agree this might work... Will give it a try and report back. Plse give me a few days... busy busy... ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •