Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    paste special values

    Can I customise the right click/context menu in Excel 97 to include a paste special values option?

    Stuck

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste special values

    Stuck,

    Hope this code unsticks you.(sorry, couldn't resist it!)

    Sub bcrtPSV()
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End Sub

    Sub brooke_modify_menu21()
    'On Error Resume Next
    With Application.CommandBars(21).Controls
    With .Add(msoControlButton)
    .Caption = "Paste Special values"
    .OnAction = "bcrtPSV"
    End With
    End With
    End Sub

    Dump both the above into a VBA code module and run brooke_modify_menu21. the workbook containing bcrtPSV will open when you use the amendment so unless you have a file that is normally open, use personal.xls. Let us know if you need more help.

    Brooke

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste special values

    Thanks Brooke, I'm sort of sorted...

    I inadvertantly ran the code twice so I've got two paste values entries now, any cure? For the double entry not my stupidity that is.

    (not quite un)Stuck

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste special values

    The simplest way is to go to the immediate window in the VBE (it's under View if it's not already open), type the following: "commandbars(21).reset" and hit enter. That should do the trick. You could of course put that into a macro of your own if you want to continually change the commandbar between the two states.

    Brooke

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste special values

    All fixed, thanks again.

    (un)Stuck
    PS glad you took the bait on my login name, 'twas carefully selected!

Posting Permissions

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