VBA Code Interferring with User's Copy Action
 

Hi,

I've inherited an Excel template that applies application methods (suc
as hiding a custom menu) when the workbook is activated. I receive
quite a few user complaints on not able to copy into the template fro
another workbook. I stepped through the code and realized that tha
every time after the user switches back to the template, th
application methods are executed, and then the ability to paste usin
Edit/Paste or Paste Special is lost. It seems this is the cas
everytime a menu command is executed. I know the user can paste it fro
the clipboard, but it's an inconvenience that she has to pick out th
item to paste and the paste operation changes the format of the targe
cells. I wonder if anybody knows how to get around this problem. T
see this problem, open two workbooks. In the first work book, put i
something like this:

Private Sub Workbook_Activate()
application.displayformulabar = false
End Sub

Copy something using Ctrl-C in the second workbook, switch back to th
first and then try to paste. It doesn't work, although you can find th
contents still in the clipboard.

Thanks for any insight.

spacecitygu

--
Message posted from http://www.ExcelForum.com

VBA Code Interferring with User's Copy Action
 

Does this code need to be executed every time the workbook is activated? Or
could you get away with moving the code to Workbook_Open?

Another possibility to look into is checking the value of
Application.CutCopyMode in your Activate function. This will at least tell
you whether something has been copied to the clipboard from Excel. For
example:

Private Sub Workbook_Activate()
If Application.CutCopyMode < False Then
application.displayformulabar = false
End If
End Sub

"spacecityguy " wrote in
message ...
Hi,

I've inherited an Excel template that applies application methods (such
as hiding a custom menu) when the workbook is activated. I received
quite a few user complaints on not able to copy into the template from
another workbook. I stepped through the code and realized that that
every time after the user switches back to the template, the
application methods are executed, and then the ability to paste using
Edit/Paste or Paste Special is lost. It seems this is the case
everytime a menu command is executed. I know the user can paste it from
the clipboard, but it's an inconvenience that she has to pick out the
item to paste and the paste operation changes the format of the target
cells. I wonder if anybody knows how to get around this problem. To
see this problem, open two workbooks. In the first work book, put in
something like this:

Private Sub Workbook_Activate()
application.displayformulabar = false
End Sub

Copy something using Ctrl-C in the second workbook, switch back to the
first and then try to paste. It doesn't work, although you can find the
contents still in the clipboard.

Thanks for any insight.

spacecityguy


---
Message posted from http://www.ExcelForum.com/

VBA Code Interferring with User's Copy Action
 

Thanks for your advice. Unfortunately, I've to run the code everytim
the workbook is activated. We use Hyperion Essbase (an OLAP database
here. The template is used to send data to the database through th
Essbase Add-in. We don't want to let the users to trigger the Essbas
commands within the template so we hide the menu within the template
The problem is that the users typically need to open a second workboo
and connect to the database (hence need the Essbase Add-in) to get som
data to help them fill the template. That's why my code has to turn o
the Add-in when the workbook is deactivated and turn it off and it'
activated again.

Thanks again for your help

--
Message posted from http://www.ExcelForum.com

VBA Code Interferring with User's Copy Action
 

OK. Here's something for you to file under "Kludge"

Don't laugh yet. Here's what you can do: On activate, check CutCopyMode. If
not False, add a new worksheet and paste. Turn off your menu, then copy the
current selection (what just got pasted) back to the clipboard, hide the new
sheet, and switch back to the sheet that was originally active. User can now
paste freely!

To avoid adding a new worksheet each time the user copies something in, use
a global variable called HiddenSheet, and only add the new sheet if
HiddenSheet is Nothing.

To avoid keeping the hidden sheet around forever, delete it on the
BeforeSave event.

Here's the code:

Dim HiddenSheet As Worksheet

Private Sub Workbook_Activate()

Dim CurrentSheet As Worksheet

If Application.CutCopyMode < False Then
Set CurrentSheet = ActiveSheet
If HiddenSheet Is Nothing Then
Worksheets.Add
Set HiddenSheet = ActiveSheet
Else
HiddenSheet.Visible = xlSheetVisible
HiddenSheet.Activate
End If

HiddenSheet.Paste
Application.DisplayFormulaBar = False
Selection.Copy
HiddenSheet.Visible = xlSheetHidden
CurrentSheet.Activate

Else
Application.DisplayFormulaBar = False
End If

End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Not HiddenSheet Is Nothing Then
Application.DisplayAlerts = False
HiddenSheet.Delete
Set HiddenSheet = Nothing
End If
End Sub

VBA Code Interferring with User's Copy Action
 

Thanks for the advice. I had actually thought about the same and wa
working on it. Knowing somebody else thought that would work was
comfort! I've to do that in several places (when the workbook i
activated and also when a worksheet is activated). I tested it fo
activating the workbook, and I think the same would work for th
worksheet just fine.

Thanks again

--
Message posted from http://www.ExcelForum.com