Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to disable hyperlinks? (97 sr2)

    I have created a "graphical menu" of various spreadsheets by putting descriptive names in cells and then adding a hyperlink to that sheet. Occasionally I want to rearrange or edit these description-cells and it becomes a real pain to select one without being jumped into another sheet or book.

    Is there any way to temporarily deactivate the hyperlink jumps and then turn them back on when I am done editing the cells? Maybe an event or click option somewhere ?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: How to disable hyperlinks? (97 sr2)

    The following two macros do rely on all the hyperlinks in your sheet starting with http://
    perhaps someone else will have some other suggestions.
    I'd attach both to buttons.
    <pre>Sub TurnLinksOFF()
    Cells.Find(What:="http://", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate

    Selection.Hyperlinks.Delete
    End Sub
    </pre>

    <img src=/w3timages/blueline.gif width=33% height=2>
    <pre>Sub TurnLinksON()
    Dim strAddress As String
    Cells.Find(What:="http://", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    strAddress = ActiveCell.Value
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    strAddress, TextToDisplay:=strAddress

    End Sub
    </pre>


    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to disable hyperlinks? (97 sr2)

    Hi Catherine,

    Nice code. Of course it only works when the display text is the same as the link text.

    Also, it just finds the very first hyperlink and changes that to normal txt.

    If the restriction that the link text equals the display text is no problem, try this (in a normal module):

    Option Explicit

    Dim oLinkCells As Object

    Sub RemoveAndRemember()
    Dim oLink As Object
    Set oLinkCells = Nothing
    For Each oLink In ActiveSheet.Hyperlinks
    If oLinkCells Is Nothing Then
    Set oLinkCells = oLink.Range
    Else
    Set oLinkCells = Union(oLink.Range, oLinkCells)
    End If
    oLink.Delete
    Next
    End Sub

    Sub SetThemAgain()
    Dim oCell As Range
    For Each oCell In oLinkCells.Cells
    ActiveSheet.Hyperlinks.Add Anchor:=oCell, Address:= _
    oCell.Value, TextToDisplay:=oCell.Value
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: How to disable hyperlinks? (97 sr2)

    An alternative approach is to just right click the cells with hyperlinks when you want to select them, you can then cut/copy and paste them where you want, or just click in the formula bar or title bar to leave the cell selected.

    StuartR

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: How to disable hyperlinks? (97 sr2)

    I <img src=/S/bow.gif border=0 alt=bow width=15 height=15> to the master <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    So I'm curious about that Union command, can you tell me more about it?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to disable hyperlinks? (97 sr2)

    On line Help is quite clear as usual <vbg>:

    Union Method


    Returns the union of two or more ranges.

    Syntax

    expression.Union(Arg1, Arg2, ...)

    expression Optional. An expression that returns an Application object.

    Arg1, Arg2, ... Required Range. At least two Range objects must be specified.

    In legible English <g>:

    Union combines two ranges into one selection. A very handy method to collect a set of cells you find somehow into one single object so you can step through them again later on as I did in the sample code above.

    I used this method because removing the hyperlinks also removes the possibility to step through them to recreate them later on. So I added all cells with a hyperlink into one object that was declared globally in the top of the module. After adding each cell to that collection, I could safely remove it's hyperlink whilst retaining a pointer to the cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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