Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Toolbar icon for this? (excel 2000)

    I cannot find a toolbar icon for the "Edit/Paste Special/Paste Link" menu item. Am I correct? Thanks.

  2. #2
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toolbar icon for this? (excel 2000)

    Hi,

    There's nothing in-built (that I can find) but I'm sure some code could be constructed which you could then assign to your own toolbar button.

    I think the reason it's not in-built is that not all items on the clipboard can be inserted as a link...Excel gives you a different screen, for example, if you copy a picture or item which you have drawn. Your code would have to determine whether the (default) item in clipboard can be pasted with a link. My tiny brain doesn't stretch to that today, but maybe someone else could help out.

    HTH

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toolbar icon for this? (excel 2000)

    Selection.Copy
    ActiveSheet.Paste Link:=True

    Just making a macro from the recorder, one gets the above. When I run the macro , and try take my copied cell reference and paste as a link on another worksheet, it doesn't work. One gets a reference to the cell itself. An example of how the Recorder doesn't always give you what you want. If anyone knows the code-answer, they can post it, otherwise, I will attempt to figure it out. I imagine some property of the selected cell has to be overwritten with the copied cell's info. Thanks.

  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: Toolbar icon for this? (excel 2000)

    How about this?
    Select the region to copy, run the macro, it will prompt for the destination (it can even be in another workbook) and once chosen will then paste the link.

    Steve

    <pre>Option Explicit
    Sub CopyPasteLink()
    Dim sPath As String
    Dim lRow As Long
    Dim iCol As Integer
    Dim rngSelect As Range
    Dim rngDest As Range
    Set rngSelect = Selection
    Set rngDest = Application.InputBox( _
    "Where do you want to copy?", Type:=8)

    With rngSelect
    sPath = "'[" & .Parent.Parent.Name & "]" & .Parent.Name & "'!"
    For lRow = 1 To .Rows.Count
    For iCol = 1 To .Columns.Count
    rngDest.Offset(lRow - 1, iCol - 1).Formula = _
    "=" & sPath & .Cells(lRow, iCol).Address
    Next
    Next
    End With
    End Sub</pre>


  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toolbar icon for this? (excel 2000)

    Let me try it. Usually, I'm just picking up one location and want to take that reference to some line on some schedule and paste it as a link. So I have a 1 cell range, I guess, to target. Would that simplify the code? My whole goal here was to have a button to click on my custom toolbar. And thanks alot.

  6. #6
    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: Toolbar icon for this? (excel 2000)

    You can add the button to a toolbar.

    You would select the range to copy (can be 1 cell)
    Push the custom button
    When it prompts for a location, goto the location, (window and sheet tabs both "selectable" to change).
    Select the cell (upper left) <ok>
    The link(s) are pasted

    Steve

Posting Permissions

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