Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Disable copy function

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Would like to know if it's possible to write code in a workbook to disable the copy function while veiwing the workbook.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    i did this by putting in the auto_open macro a command to hide all toolbars, and then
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""

    so that CTRl C, and CTRL V are both disabled

    remember to reenable the CTRL keys with your auto close macro

    Application.OnKey "^c"
    Application.OnKey "^v"


    hope this helps a bit!

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, I'll give it a whirl. (have always wanted to visit Australia)

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Australia = GR8 place, GR8 people.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In addition to the onkey methods mentioned above you need to disable the copy command on the Edit menu and the cell right-click menu by doing the following:

    CommandBars(1).Controls("Edit").Controls("Copy").Enabled = False
    CommandBars(21).Controls("Copy").Enabled = False

    _________________
    It's never too late to learn something new.

    Ricky

    [ This Message was edited by: Ricky Morris on 2002-05-08 18:21 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ricky..
    does that hide the option or does it just disable it?

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It disables it. To delete it try:

    CommandBars(1).Controls("Edit").Controls("Copy").Delete
    CommandBars(21).Controls("Copy").Delete

    But you would need to reset the menubars when you're done. With the disable method you would need to enable them when done by changing true to false

    _________________
    It's never too late to learn something new.

    Ricky

    [ This Message was edited by: Ricky Morris on 2002-05-08 18:27 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you're right. Its never too late to learn something new
    Colin

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try covering your bases with this in your Workbook module:

    Private Sub Workbook_Activate()
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    End With
    End Sub
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox "Right click menu deactivated." & vbCrLf & _
    "Cannot copy or ''drag & drop''.", 16, "For this file:"
    End Sub
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.CutCopyMode = False
    End Sub
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    With Application
    .OnKey "^c", ""
    .CutCopyMode = False
    End With
    End Sub
    Private Sub Workbook_Deactivate()
    With Application
    .CellDragAndDrop = True
    .OnKey "^c"
    .CutCopyMode = False
    End With
    End Sub


  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom is right....you should cover all your bases as the user can still have the Cut
    or copy button in another commandbar.
    As well as toms you can try this,

    Don't forget that the user can still
    Cut to another Workbook.

    In the Thisworkbook object;


    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.CutCopyMode = False
    End Sub


    And Also


    Sub DisableCopy()
    EnableControl 19, False ' copy
    EnableControl 21, False ' cut
    Application.CommandBars("Toolbar List").Enabled = False
    Application.OnKey "^C", ""
    End Sub

    Sub EnableCopy()
    EnableControl 19, True ' copy
    EnableControl 21, True ' cut
    Application.CommandBars("Toolbar List").Enabled = True
    Application.OnKey "^C"
    End Sub

    Sub EnableControl(Id As Integer, Enabled As Boolean)
    Dim CB As CommandBar
    Dim Ctrl As CommandBarControl

    On Error Resume Next
    For Each CB In Application.CommandBars
    Set Ctrl = CB.FindControl(Id:=Id, recursive:=True)
    If Not Ctrl Is Nothing Then Ctrl.Enabled = Enabled
    Next
    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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