Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell copy action pauses for 15 second for the smallest action | Excel Discussion (Misc queries) | |||
VBA code to undo previous VBA action | Excel Discussion (Misc queries) | |||
Copy Sheet action very slow | Excel Discussion (Misc queries) | |||
copy worksheet multiple times base on user's input | Excel Worksheet Functions | |||
Multiple users without interferring? | New Users to Excel |