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.

 

[Andrew Poulsom]

Customising Toolbars clears the clipboard (at least when it's done manually).

 

[geff]

Customising Toolbars clears the clipboard (at least when it's done manually).

I thought it was the remove toolbars code. Here's the weird thing. If when I deactivate the workbook, go to another workbook to copy data from. I can view the office clipboard taskbar and it stays active when I switch back to the offending workbook. The clipboard isn't being cleared. I can see the data inside the office clipboard but nothing will paste. I'm guessing it's just excels internal clipboard that is being cleared?

I can actually paste using the office clipboard taskbar but my paste data button just does nothing. The Paste Data buttons code for info is simply this:

ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Is there anyway around this problem? Thanks so far.

 

[Andrew Poulsom]

The Office Clipboard and the Excel Clipboard are not the same animal.

 

[brianburen]

i have the same problem:

I have a macro that selects a range say a1:l1 and copies it

then it closes that workbook and i get message do i want to save the data on the clipboard or not ( i want this to be yes by default)

i try to paste it into the new workbook, and it says the pastespecial method does not work

 

[Andrew Poulsom]

Why don't you paste to the new workbook before closing the source workbook?

 

[geff]

Why don't you paste to the new workbook before closing the source workbook?

I have tried this it still doesn't work. I really need to get this sorted, is there anyway to extract the data from the office clipboard then...given the fact it seems it's only the excel clipboard that is being cleared?

Or even just some way to call the office clipboard taskbar into view using VBA so the end users can paste using that?

 

[Andrew Poulsom]

What is causing the clipboard to be cleared? To avoid it make sure that you paste immediately after copying.

 

[brianburen]

I use a template every morning that when done it saves with a file name based on the date.
My workbook that the data will be saved to is the file that has a different file name everyday.
How would i make the VBA switch back to this workbook before closing the other? I want the VBA to be in the template, but be able to work even when the file name changes.

If I put one file name in windows(Production Report 8-24-11 B.xls),activate, the next day it wont work because the filename will have the date in it.

 

[geff]

Why don't you paste to the new workbook before closing the source workbook?

As you have mentioned above it is the Workbook Activate code on the destination workbook that is clearing the clipboard.

I cannot change the Workbook Activate code as it is a fundamental part of the workbook. The workbook is used by other people (not me) they complete it as a claim form which I then process.

They need to be able to paste data from their own spreadsheets into the claim form. Therefore they have to switch to another spreadsheet in order to copy their data. That data is then being wiped from the clipboard when they switch back to the claim form to paste into it.

@brianburen - Not meaning to be rude, but would you be able to start your own thread.