Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    I have a need to clear the clip board when a user leaves an Excel application window and goes to another application window. I have code in a VB app that performs this function but the same code does not work in the Excel VBA code, it actually kills the whole Excel application. I have attached a WinZip fle containing the the VB app that works and an Excel spreadsheet where I tried to implement the same code. Can someone look at these and tell me what is wrong?
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    Several Comments:

    1- There is no Clipboard object in Excel, therefore ClipBoard.Clear is not going to clear the clipboard, it is going to produce an error. If the data on the clipboard was a result of copying Excel cells (there is a marque around some cells) then what you want is:

    <pre> Application.CutCopyMode = False
    </pre>


    2- Why use the Windows hook API when there are Application Activate/Deactivate event routines which should do what it looks liike you are trying to do?

    3- The way you have implemented your code, you are going to unhook the first time the window is deactivated and never rehook.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    Thanks. I have tried the ThisWorkbook object's window activate,deactivate, workbook activate, deactivate but these event are not fired when one leaves an Excel window. When you refer to Application object's events, are you saying I need to create a class using "WithEvents" and making it an Application object and it will handle the events of leaving and coming back into an Excel application.

    The Application.CutCopyMode = False on my Excel 2000 does not work this way. Instead it shows either Application.CutCopyMode = xlCut or xlCopy

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    What is your definition of "when one leaves an Excel window?"

    Application.CutCopyMode = False is not documented in the help files, but it does work. Have you tried it?
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    Setting to False is documented in the 2000 VBA Help

    CutCopyMode Property
    Returns or sets the status of Cut or Copy mode. Can be True, False, or an XLCutCopyMode constant, as shown in the following tables. Read/write Long.

    Return value Description
    False Not in Cut or Copy mode
    xlCopy In Copy mode
    xlCut In Cut mode

    Set value Description
    False Cancels Cut or Copy mode and removes the moving border
    True Cancels Cut or Copy mode and removes the moving border
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    The windowActivate event only fires when one changes to another workbook within Excel, not when one leaves XL.
    In fact, there seems to be no Application event that fires when XL becomes inactive.

    All you can do is have a timed macro check whether XL is the active window:

    Option Explicit

    '*** Declare some Windows API calls

    Declare Function FindWindow32 Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Declare Function GetForegroundWindow Lib "user32.dll" () As Long
    Declare Function SetActiveWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    Dim dNextTime as Double
    Function IsExcelActive() As Boolean
    Dim hWndP2 As Long
    Dim hWndXL As Long
    Dim hWndVBE As Long
    hWndXL = FindWindow32("XLMAIN", Application.Caption)
    hWndVBE = Application.VBE.MainWindow.hwnd
    'Find the active window
    hWndP2 = GetForegroundWindow
    IsExcelActive = (hWndXL = hWndP2) Or (hWndP2 = hWndVBE)
    End Function

    Sub IsActive()
    If IsExcelActive Then
    Else
    Msgbox "Excel Not Active"
    End If
    dNextTime=Now+Timevalue("00:00"01")
    Application.Ontime dNextTime, "IsActive"
    End Sub

    Sub StopIt
    Application.Ontime EarliestTime:=dNextTime, Procedure:="IsActive", Schedule:=False
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    What I am trying to do is not allow someone to cut and paste information that is in the Excel sheet which means somehow I have to clear the clip board whenever a user leaves the active Excel and goes to another application such as another Excel work book, notepad, word, etc. I am able to do this with a VB program which I am attaching. Inside the attached Winzip is also a XLS where I try to use the same code but it fails.
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    How are you starting the timed macro you are referring to?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing Windows Clipboard upon leaving Excel (2000/SR2)

    Just by running IsActive. It reschedules itself.

    But reading your other message about the actual goal of this assignment, I would say this is not too good a method, since when the user disables macros, he/she can do whatever he/she likes.

    To really prevent copying you'll need to consider what it is you don't want copied: formulas, numbers, ...

    You *could* disable all copying options for the workbook in question, but it is a rather tedious job that -again- will not work when the file is opened with macros disabled.

    My aproach would be to hide everything sensitive from view, protect all sheets except one, which shows a huge message to tell the user to open the file with macros enabled. Then have the macros unhide the relevant sheets and maybe disable copying or use the IsActive scheme.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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