I don't want the clipboard clearing...help

[geff]

Hi All,

I have a complicated workbook that I've put together using VBA code I've found rather than written myself. It's probably not streamline but it has been working for what is needed for a while now.

There is quite a bit of code within "This Workbook" module which forces users to enable macros (the well known hidden sheets trick). It also disables toolbars and some control within the workbook and then obviously restores it all again on exit. There is also code within "This Workbook" module which handles those procedures for when the workbook is Deactive and Active again. The navigation through the workbook (by end users) is using Macro buttons as they have no access to the excel toolbars. For example "Next Page" which has code to validate the current page before moving onto the next etc.

Here's where I have come a bit stuck. I have been asked to add a "Paste Data" button. Not really a problem I have the code to paste data as values into the table concerned.

Where it goes wrong is the fact it would appear the clipboard is being cleared by either the "Workbook_Activate" code or the "Workbook_Deactivate" code. Please see the code below:

<u>Workbook Activate Code</u>

[code]
Private Sub Workbook_Activate()

' This ensures that if the user returns to the eClaim after viewing an alternative
' excel file then the excel controls are disabled

  Run "RemoveToolbars"
  Run "Disable_Control"
  
' Hides the ADMIN FINISH BUTTON

  Sheets("Main").Shapes("Button 20").Visible = False
End Sub

Sub RemoveToolbars()

' RemoveToolbars Macro
' Disables functionality of excel


    On Error Resume Next
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = False
        Application.DisplayFormulaBar = False
        Application.DisplayStatusBar = False
        ActiveWindow.DisplayWorkbookTabs = False
    Next
    
End Sub

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
      (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, _
      ByVal bRevert As Integer) As Integer

Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Integer, _
      ByVal nPosition As Integer, ByVal wFlags As Integer) As Integer

Sub Disable_Control()
   Dim x As Integer, hwnd As Long
   hwnd = FindWindow("XLMain", Application.Caption)
     Call DeleteMenu(GetSystemMenu(FindWindow("xlMain", _
         Application.Caption), False), 4, 1024)
   Call DeleteMenu(GetSystemMenu(FindWindow("xlMain", _
         Application.Caption), False), 3, 1024)
   Call DeleteMenu(GetSystemMenu(FindWindow("xlMain", _
         Application.Caption), False), 0, 1024)
   Call DeleteMenu(GetSystemMenu(FindWindow("xlMain", _
         Application.Caption), False), 6, 1024)
End Sub

[/code]

<u>Workbook Deactivate Code</u>

[code]
Private Sub Workbook_Deactivate()

' Toolbars are restored so that excel can function if a different workbook is
' in focus

Run "RestoreToolbars"

End Sub

Sub RestoreToolbars()

' RestoreToolbars Macro
' Restores functionality of excel


    On Error Resume Next
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = True
        Application.DisplayFormulaBar = True
        Application.DisplayStatusBar = True
        ActiveWindow.DisplayWorkbookTabs = True
        
    Next
    
End Sub

[/code]

Can anyone tell me where the clipboard is being emptied and how I can stop it from occuring so that users can paste data into this workbook?

Thanks in advance.

 

[brianburen]

OK no problem....... although I just got it to work.

 

[chuckchuckit]

Geff,

Using the clipboard to copy, opens itself to being cleared in many ways at random times. If there is code anywhere in an other instance of Excel running that pushes the escape key for instance, this clears the clipboard for all Excel instances as they share same clipboard.

If you have a maco that is transfering data (copying), try code that directly transfers data without using clipboard.

But of course if you have user interface that requires Ctrl-C to copy, you will be stuck using the clipboard.

Chuck