Controlling OnAction of PASTE button on toolbar

  • Hi guyz,


    My second post on this awesome forum :)


    Can I control the OnAction of the PASTE button on the standard commandbar. I'm able to control the same for Paste option under the EDIT menu as well as the Paste option on the right-click menu via the following set of instructions:


    However when I add:

    Code
    CommandBars("Standard").Controls("&Paste").OnAction = "PasteValuesOnly"


    to the CustomizeEditMenu subroutine a run-time error '438' - OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD appears:(
    I have tried searching on the forum as well but maybe I'm not searching the right threads.
    I'm going crazy figuring this one out...I'll appreciate all you GURUS to try 'n help me maintain my sanity :yikes:


    ThanX in advance
    Rana

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi buddy,



    Try this...


    [vba]
    Sub Pastevaluesonly()
    Selection.PasteSpecial Paste:=xlvalues
    End Sub
    [/vba]





    HTH.

  • Re: Controlling OnAction of PASTE button on toolbar


    Howdy bro,


    Seems like u're the only one who is replying to threads posted by me (Refer: Controlling paste method) :)


    Well if you read my entire post I already have defined the macro PasteValuesOnly() and it works. The problem is not the macro, the problem is the following line of code:

    Code
    CommandBars("Standard").Controls("&Paste").OnAction = "PasteValuesOnly"


    The moment I use OnAction for the Paste BUTTON in the toolbar, I get a run-time error.
    Hope this makes more sense in explaining my problem.
    ThanX again for your reply...awaiting another helpful response...


    Many ThanX
    Rana

  • Re: Controlling OnAction of PASTE button on toolbar


    Hey,


    You are correct..Am bit over excited...sorry for that.(I edited my post as well)..


    yeah...I remember that I have replied to your earlier posts..!


    But this one... how come it did worked for me..let me check again..but it should be an easy one for a Guru..you will get the answer...


    Anyway you seems to be in a better position than what I am.


    Congrat for finding the answer for the other two(Edit&popup)...


    All the best...


    Thanx

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi Pankaj,


    Try this:(am not sure if it helps..as I have not tested this)
    [vba]
    Application.CommandBars("Worksheet Menu Bar").Controls("&Paste").OnAction = "Pastevaluesonly"
    [/vba]


    I have not tested this as I messed up with my own worksheet controls. When press the Paste button it says "Book6.xls" not found(I recorded the earlier code in this book only). Though I don't much use this control.


    HTH.

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi,


    "..you are the only one...not" <g>


    Here you go. It's not the 'Standard toolbar you're looking for. It's the Edit menu.



    Code
    Sub menu()
    CommandBars("Edit").Controls("&paste").OnAction = "PasteValuesOnly"
    End Sub
    
    
    Sub PasteValuesOnly()
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End Sub


    PS you should also look at disabling <Ctrl+V> with the .Onkeys method...




    'til


    dr

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi dr,


    First of all my apology for not acknowledging your response earlier (I was away from my work and cooling heels on my weekend :) )


    "Here you go. It's not the 'Standard toolbar you're looking for. It's the Edit menu."


    Here's the thing...if you read my code that I gave in my original post of this thread, it already takes care of what you've suggested PLUS I've also taken care of the CTRL+V key combination. However, the problem that remains is the PASTE BUTTON that appears on the STANDARD TOOLBAR.


    I sincerely that hope I'm more clear now and that you might be able to bail me out.


    Many ThanX,
    Rana

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi,


    Sorry about missing the BUTTON reference! What verson of Exel are you using? Your code works fine for me but there are some differences in the Commandbars structure that may the issue here.


    Cheers,


    dr

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi dr,


    FINALLY SOMEONE UNDERSTOOD MY PROBLEM!!! :)
    A huge thanx for the advise on version checking bro...I never realized that it could be one of the problems and now that I've cross verified on Excel 2000, I understand that it is a version problem as the same code works on Excel 2000 but not on Excel 2002 and above. So for now, at least we seem to have identified one part of the problem, which is good newz :) but now that we know that the peice of code written on the OnAction of the PASTE BUTTON does not work on Excel XP and above, the next part is to tweak/change the code to make it compatible with the higher versions. As far as I'm concerned, I'm already on the verge of losing my sanity trying to figure out this one :(
    However, now that I'm aware of one part of the problem, the solution that comes right away to my mind is to enable the OnAction code for Excel 2000 and disable it for higher versions but that would be the last ditch effort.
    I'll appreciate if you can verify my problem on a higher version and advise as to what I'm doing wrong and what is it that I need to do to make my code work.


    Thx for your time bro...it is innumerable number of people like you who make this forum so cool.


    CHEER,
    Rana

  • Re: Controlling OnAction of PASTE button on toolbar


    Hi,


    I finally got a chance to work with a higher version of Excel (2002). The only reference to paste values I could find was an argument to the pastespecial function (as before).


    Since recording the clicking of the paste BUTTON results in a simple:


    'Activesheet.Paste'


    then your code for the BUTTON might have to read like this:


    CommandBars("Standard").Controls("&Paste").OnAction ="Selection.Pastespecial Paste:=xlvalues"


    Cheers,


    dr

  • Re: Controlling OnAction of PASTE button on toolbar


    Thx for the reply.
    However, the code didn't work. It throws a "compile error: Expected End of statement" :(
    I'm COMPLETELY lost now!

  • Re: Controlling OnAction of PASTE button on toolbar


    Wow, total newbie mistake! i FORGOT THE QUOTES on the argument! should have read = "Selection...=xlvalues" as below.


    Code
    CommandBars("Standard").Controls("&Paste").OnAction ="Selection.Pastespecial Paste:=xlvalues"


    Cheers,


    dr

  • Re: Controlling OnAction of PASTE button on toolbar


    Got a run-time error '438': OBJECT DOSEN'T SUPPORT THIS PROPERTY OR METHOD.
    This is what I did:

    Code
    'Wrote this subroutine in the module and 
    'set a reference to the same in the Workbook_Open() subroutine
    Sub Test()
        CommandBars("Standard").Controls("&Paste").OnAction _ 
    = "Selection.PasteSpecial Paste:=xlValues"
    End Sub


    What am I doing wrong, is there something else too that I need to do to make this code work???
    Please advise...


    Not much to cheers :(
    Rana

  • Re: Controlling OnAction of PASTE button on toolbar


    OKOKOKOKOK!


    Finally got it. Sat down with two machines, two diffferent versions of XL - '97 and 2003. Excel 97 SR1 has a paste button. period. no menu or submenus on that button.


    Excel 2002 (2nd version I managed to get) has a paste button with a menu and a dialog box for "Pastespecial"... BIG difference!


    Your original code works fine in Excel 97 on the single function paste button. So I wrote a macro to put the same button into Excel 2002 and voilà it works.


    For Excel 97 use your original code.


    For Excel 2002 (and ?)



    yay!.



    dr

  • Re: Controlling OnAction of PASTE button on toolbar


    YIPPPPPPPIIIIIIIIIEEEEEEEEEEEEEEEEE!!!


    FINALLY-FINALLY-FINALLY...IT WORKED!!!


    Though by now I had this feeling that the only way I could acheive this was by putting in place MY OWN PASTE button but I had ABSOLUTELY NO IDEA that it could be acheived with such a simple code (at least you've made it look simple my good friend!). A trillion thx for a persistent effort to help out a total stranger and that too a newbee (I know how difficult it becomes at times to make a rookie understand even the simplest of code lines...but then I guess this true effort of yours is what makes you guys out there soooo cool).
    My salute to all you maestros out there.


    Thx DR...now your friend, Rana has TOO MUCH TO CHEERS ABOUT!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!