Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 31 of 31

Thread: Get Underlying Hyperlinks Out of Friendly Names

  1. #21
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi all :hi

    Unlike other boards I like to see that we also include error-handling in the provided solutions, especially when they are supposed to be used by less skilled user.

    Therefore I made the following add-ons:

    Option Explicit
    Sub Do_It()
    Dim rnSource As Range, rnStart As Range, rnTarget as Range
    If ActiveSheet.ProtectContents = False Then
        If TypeName(Selection) = "Range" Then
            With Selection
                If .Columns.Count <> 1 Then
                    MsgBox "Please make sure that the selection only contains one column.", vbInformation
                    GoTo ExitHere
                ElseIf .Areas.Count <> 1 Then
                    MsgBox "Please make sure that the selection only covers one area.", vbInformation
                    GoTo ExitHere
                Else
                    Set rnSource = Selection
                End If
            End With
        Else
            MsgBox "You need to select a range before executing this procedure.", vbExclamation
            GoTo ExitHere
        End If
    Else
        MsgBox "You need to unprotect the worksheet before executing this procedure.", vbExclamation
        GoTo ExitHere
    End If
    On Error Resume Next
    Set rnStart = Application.InputBox( _
    Prompt:="Please select the first cell in the target range:", _
    Title:="Select targetrange", _
    Type:=8)
    On Error GoTo 0
    If rnStart Is Nothing Then
        GoTo ExitHere
    Else
        Set rnTarget = rnStart.Resize(rnSource.Rows.Count, 1)
        Transfer rnSource, rnTarget
    End If
    ExitHere:
    Exit Sub
    End Sub
    
    Private Sub Transfer(ByRef rng As Range, ByRef Target As Range)
    Application.ScreenUpdating = False
    rng.Copy
    Target.PasteSpecial (xlPasteAll)
    With rng
        .Hyperlinks.Delete
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    End Sub
    Kind regards,
    Dennis
    Last edited by XL-Dennis; 05-27-2004 at 04:06 AM.
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  2. #22
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Quote Originally Posted by TonyJollans
    Well, well, well - I didn't tag my second piece of 'code' but the system identified it as VBA - very clever!
    Not exactly...
    A little birdie happened to see it...
    ~Anne Troy

  3. #23
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I like the idea of always having full error handling, but it's going to put a load on the approvers.

    BTW, you need rnTarget As Range
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #24
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Tony,

    Thanks for the heads up - I added it in the original post

    Yes, I agree but I believe it should only be necessary for the KB-articles.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #25
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location

    Lightbulb

    In my book a procedure should never make incidental changes to the environment. I know this is not always possible in VBA, but with ScreenUpdating, I think your example should have ..

    Private Sub Transfer(ByRef rng As Range, ByRef Target As Range) 
    Dim CallerScreenUpdating as boolean
            CallerScreenUpdating = Application.ScreenUpdating
            Application.ScreenUpdating = False 
    ' rest of code 
    Application.ScreenUpdating = CallerScreenUpdating 
    End Sub
    Without this, the caller may have set it False only to find it True after calling a sub-routine which he wouldn't want.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  6. #26
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    G8 -

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  7. #27
    Quote Originally Posted by TonyJollans
    Yes, Mark, the UI and the functionality are different beasts - it was Anne's second post that I thought brought the UI into scope, and there is probably a bigger issue here to do with run instructions.
    Huh? Are you referring to the bit where Anne says she doesn't want the range hard-coded?

    I guess I agree with the last two comments from Tony and Dennis about environment and error trapping, but really, it all seems like overkill for a relatively simple post. Perhaps an example of these techniques in the KB would be easier to simply refer to than to answer every question with 30 lines of code.

  8. #28
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    This code, intended for the KB, would be utilitarian: once and done, most likely.
    It would be cool if the person didn't have to choose the range, but merely only needed to ensure that wherever their hyperlinks reside, then the cell to the right is blank and can be populated with the hyperlinks.

    I don't think we need to get into a big deal with a typical KB entry like this.

    However! Were we to implement this feature into the addin...that's a different story!
    ~Anne Troy

  9. #29
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    it all seems like overkill for a relatively simple post
    Yupp, but I like to see where we stand when it comes to some aspects for the KB-articles. A general write-up about error-handling is great but as I said I like to see some stuff different here.

    So let the comments come about it

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  10. #30
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    But not here, Den. Can we take this discussion where it belongs?

    http://www.vbaexpress.com/forum/showthread.php?t=66

    ~Anne Troy

  11. #31
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    Hi Dreamboat,
    here another approach. If I read the question correctly then you want to have the hyperlink address in column B. Hereby I asume that the cell contains a value which is shown like "test". Underlying there is a link towards http://www.test.com. This last value you want to see in column B. IN this case you might think about using a macro like:


    Sub HyperlinkAddr()
    Application.ScreenUpdating = False
    rowsA = Cells(Rows.Count, 1).End(xlUp).row
    For i = 1 To rowsA
        If Cells(i, 1).Hyperlinks.Count > 0 Then
            Cells(i, 1).Offset(0, 1).value = Cells(i, 1).Hyperlinks(1).Address
        End If
    Next i
    Application.ScreenUpdating = True
    End Sub

    I hope this is what you needed.

    regards,
    Jeroen

Posting Permissions

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