Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Cambridge, UK
    Thanked 3 Times in 3 Posts

    Macro Prevents Paste (2000 onwards)

    I have a Macro that runs as part of the Activate Event on a specific sheet.


    Plus the reverse process on De-Activate.

    This is because on some users pc the spreadsheet is so complex that it is very slow with auto recalculate.

    However because of this macro, when a user tries to copy from another sheet or book and paste into this one
    paste is disabled when the activate macro runs, in the same way that if you copy something and then do anything else
    before you paste such as as data entry, the clipboard is cleared.

    (I also have the same issue with a sheet on which I need to prevent drag and drop, but enable it on other sheets within the same workbook)
    The facility works fine from Activate & DeActivate, but disables Paste.

    Is there anyway to prevent this behavior, or have I missed something simple.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Macro Prevents Paste (2000 onwards)

    It's the same as working interactively: many actions in Excel will clear the clipboard. Changing calculation mode is one of them. You'd probably be better off turning off automatic calculation entirely, and only turn it on when you need it.

    If it's just data you want to copy/paste, you could write code to enable this; formulas will be lost however, and there are other limitations.

    For the following code you need a reference to the Microsoft Forms 2.0 Object Library. An easy way to set this reference is to insert a userform; you can delete it immediately, the reference will remain set.
    Private Sub Worksheet_Activate()
    Dim f As Boolean
    Dim objData As New DataObject
    ' Did the user copy anything to the clipboard?
    If Application.CutCopyMode Then
    ' Raise flag
    f = True
    ' Store in Data Object
    End If
    Application.Calculation = xlCalculationManual
    If f Then
    ' Place data in Windows clipboard again
    End If
    Set objData = Nothing
    End Sub
    Similar for the Deactivate event.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: Macro Prevents Paste (2000 onwards)

    Excel tends to clear the clipboard when a macro is run. AFAIK, there is no way around this.
    Legare Coleman

Posting Permissions

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