Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Enabling Cut,Copy,Paste,Paste-Special Functions

  1. #1
    Join Date
    6th September 2004
    Location
    Dubai UAE
    Posts
    12

    Enabling Cut,Copy,Paste,Paste-Special Functions

    Hello There,
    I need some help...
    I tried the "DISABLE CUT/COPY IN A WORKBOOK" code that was given in the Ozgrid news letter sent on 14th Dec.
    My problem is that now whenever I use excel, the cut,copy,copy paste functions are disabled in the Edit tool bar as well as in the options we get on right click. The only way i can use these functions is by key board short cuts. I need urgent help to enable these funtions.
    Thanks..
    Shyam


    The code used is as below, have copied it from the mail:
    The code below must be placed in the Private Module of the Workbook Object (ThisWorkbook). To get there easily, right click on the Excel icon, top left next to File and choose View Code. In here paste the code below, close & save and then re-open.
    VB:
    Private Sub Workbook_Activate() 
        Dim oCtrl As Office.CommandBarControl 
         
         'Disable all Cut menus
        For Each oCtrl In Application.CommandBars.FindControls(ID:=21) 
            oCtrl.Enabled = False 
        Next oCtrl 
         
         'Disable all Copy menus
        For Each oCtrl In Application.CommandBars.FindControls(ID:=19) 
            oCtrl.Enabled = False 
        Next oCtrl 
         
        Application.CellDragAndDrop = False 
         
    End Sub 
     
     
    Private Sub Workbook_Deactivate() 
        Dim oCtrl As Office.CommandBarControl 
         
         'Enable all Cut menus
        For Each oCtrl In Application.CommandBars.FindControls(ID:=21) 
            oCtrl.Enabled = True 
        Next oCtrl 
         
         'Enable all Copy menus
        For Each oCtrl In Application.CommandBars.FindControls(ID:=19) 
            oCtrl.Enabled = True 
        Next oCtrl 
         
        Application.CellDragAndDrop = True 
         
    End Sub 
     
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
        With Application 
            .CellDragAndDrop = False 
            .CutCopyMode = False 'Clear clipboard
        End With 
    End Sub 
     
     
    Note the use of the FindControls Method, In particular the use of the optional ID argument. One can determine the ID of any Control via some simple code Like shown below. 
     
    Sub Copy_Id() 
        MsgBox CommandBars("Worksheet Menu Bar") _ 
        .Controls("Edit").Controls("Copy").ID 
    End Sub 
    
    
    Last edited by royUK; December 21st, 2005 at 16:53. Reason: add code tags, please use when posting codes

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    India
    Posts
    1,039

    Re: Enabling Cut,Copy,Paste,Paste-Special Functions

    Hi I_Shyam:

    First of all try to follow the forum Rules. Especially the code tags.

    The code works fine for me. I think you put the code in any one of the workbooks opening up on the start-up of excel.

    The code should enable all cut/Copy functions when you deactivate (close) the workbook which has the code.

    Try to run "Enable all Cut menus" macro by putting in a seperate module. That will enable all cut/copy functions.




    Regards


    Maqbool

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th September 2004
    Location
    Dubai UAE
    Posts
    12

    Re: Enabling Cut,Copy,Paste,Paste-Special Functions

    Thanks a ton Maqbool for the response, its ok now. And royuk thanks for including the tags.

    Apologies for my error, Im not too familiar with VBA, but am keen to learn more about it, hence tried experimenting with the info given in the newsletter.

    Thank you once again!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy & Add New Workbook To Paste Special
    By danneedham in forum EXCEL HELP
    Replies: 4
    Last Post: August 18th, 2008, 18:17
  2. Copy & Paste Special
    By abbeville in forum EXCEL HELP
    Replies: 2
    Last Post: May 4th, 2006, 04:29
  3. Copy and paste special
    By GeethaGupta in forum EXCEL HELP
    Replies: 2
    Last Post: June 5th, 2005, 19:26
  4. Copy and paste special
    By eddie57 in forum EXCEL HELP
    Replies: 2
    Last Post: April 21st, 2005, 00:42
  5. Copy & Paste Special for Different Worksheets
    By thampw in forum EXCEL HELP
    Replies: 2
    Last Post: November 30th, 2004, 13:10

Bookmarks

Posting Permissions

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