Announcement

Collapse
No announcement yet.

Enabling Cut,Copy,Paste,Paste-Special Functions

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    Code:
    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, 16:53. Reason: add code tags, please use when posting codes

  • #2
    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

    Comment


    • #3


      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!

      Comment

      Working...
      X