?
Solved

Copy and Paste Disabled on Workbook/Worksheet Activate/Deactivate

Posted on 2014-10-10
12
Medium Priority
?
700 Views
Last Modified: 2014-10-27
Hi Experts,
I'm using the below code to set the environment for an excel workbook, problem is that when the code executes either on workbook deactivate/activate or worksheet activate anything that I may potentially have "queued" to paste is deselected, making me unable to c + p between workbooks/worksheets.

Is there anything I can do to mitigate?
Private Sub Workbook_Activate()
Application.ScreenUpdating = False
        Call HidetheRibbon
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayWorkbookTabs = False
        Application.WindowState = xlMaximized
        ActiveWindow.WindowState = xlMaximized
        Call UsedRangeZoom
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
        Call HidetheRibbon
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
        ActiveWindow.DisplayGridlines = True
        ActiveWindow.DisplayWorkbookTabs = True
        Application.WindowState = xlMaximized
        ActiveWindow.WindowState = xlMaximized
        Call UsedRangeZoom
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
        Call ShowtheRibbon
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
        ActiveWindow.DisplayGridlines = True
        ActiveWindow.DisplayWorkbookTabs = True
Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:-Polak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
12 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374169
where is the rest of code.  i mean you are calling another procedure from this i mean Call HidetheRibbon and ShowtheRibbon and Call UsedRangeZoom
0
 
LVL 1

Author Comment

by:-Polak
ID: 40374175
Sure here is the rest I didn't think that had anything to do with it:

Sub HidetheRibbon()
  If Application.CommandBars("Ribbon").Height >= 100 Then
    Application.SendKeys "^{F1}"
  End If
End Sub
Sub ShowtheRibbon()
  If Application.CommandBars("Ribbon").Height < 100 Then
    Application.SendKeys "^{F1}"
  End If
End Sub
Sub UsedRangeZoom()
    'set zoom for active sheet to columns with data
    ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Columns.Count).Select
    ActiveWindow.Zoom = True: ActiveWindow.VisibleRange(1, 1).Select
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374189
this is the part of the code that creates problem.  in the event of sheet activate

the following code to be removed.

'        Application.DisplayFormulaBar = True
'        ActiveWindow.DisplayHeadings = True
'        ActiveWindow.DisplayGridlines = True
'        ActiveWindow.DisplayWorkbookTabs = True
'        Application.WindowState = xlMaximized
'        ActiveWindow.WindowState = xlMaximized

Open in new window


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374193
i am not sure which you use all of those extra not useful codes.

i the below code without those deactivation works much better than your original code.

Private Sub Workbook_Activate()
Application.ScreenUpdating = False
        Call HidetheRibbon
        Call UsedRangeZoom
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
        Call HidetheRibbon
        Call UsedRangeZoom
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
        Call ShowtheRibbon
Application.ScreenUpdating = True
End Sub



'   your modules unchanged

Sub HidetheRibbon()
  If Application.CommandBars("Ribbon").Height >= 100 Then
    Application.SendKeys "^{F1}"
  End If
End Sub
Sub ShowtheRibbon()
  If Application.CommandBars("Ribbon").Height < 100 Then
    Application.SendKeys "^{F1}"
  End If
End Sub
Sub UsedRangeZoom()
    'set zoom for active sheet to columns with data
    ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Columns.Count).Select
    ActiveWindow.Zoom = True: ActiveWindow.VisibleRange(1, 1).Select
End Sub
0
 
LVL 1

Author Comment

by:-Polak
ID: 40374212
I'm okay with removing the worksheet activate events (except for "Call UsedRangeZoom") they are only necessary for me as I build the workbook (so that I can have headings, the ribbon, etc..).

However, the events under workbook activate/deactivate are necessary to set the environment for the end-user, I do no want them to see the ribbon, gridlines, headings, etc... when they are in the workbook. So I cannot simply remove that code, and even with worksheet activate disabled you cannot copy and paste between workbooks running the workbook activate/deactivate code.....
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374338
i could not find another way but to go for another method.

check attached file.  whenever you are opening the file, press shift F11  OR ALT SHIFT F1 to insert a new sheet,( you can always delete that sheet at the end of your work.

this new sheet method will help trigger activating all those formula bar and gridlines etc , that frees the worksheet activation event not to be spoiled with these unessesary event which erases the copy clipboard.
sssss.xlsm
0
 
LVL 1

Author Comment

by:-Polak
ID: 40378028
See the below code I have found an initial workaround to my question by using If Application.CutCopyMode = True/False Then...
and essentially disabling the workbook activate, deactivate, and worksheet activate code if there is something copied to the clipboard. As such, when switching back and forth between workbooks the application/window environment settings do not set the "UI" if the user has something copied at the time of workbook activate, deactivate or worksheet activate.

Presumably, if the user has something copied when he activates my workbook he/she might need some of the options available to them in the Ribbon anyway. Therefore.... I am "OKAY" with my work-around..... HOWEVER, I was wondering if there was a better way.....?

Private Sub Workbook_Activate()
    If Application.CutCopyMode = True Then
        Application.ScreenUpdating = False
        Call HidetheRibbon
        Call UsedRangeZoom
        Application.ScreenUpdating = True
    End If
    If Application.CutCopyMode = False Then
        Application.ScreenUpdating = False
        Call HidetheRibbon
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayWorkbookTabs = False
        Application.WindowState = xlMaximized
        ActiveWindow.WindowState = xlMaximized
        Call UsedRangeZoom
        Application.ScreenUpdating = True
     End If
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Application.CutCopyMode = True Then
        Application.ScreenUpdating = False
        Call ShowtheRibbon
        Call UsedRangeZoom
        Application.ScreenUpdating = True
    End If
    If Application.CutCopyMode = False Then
        Application.ScreenUpdating = False
        Call ShowtheRibbon
        Call UsedRangeZoom        
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayWorkbookTabs = False
        Application.ScreenUpdating = True
     End If
End Sub
Private Sub Workbook_Deactivate()
    If Application.CutCopyMode = True Then
        Application.ScreenUpdating = False
        Call ShowtheRibbon
        Call UsedRangeZoom
        Application.ScreenUpdating = True
    End If
    If Application.CutCopyMode = False Then
        Application.ScreenUpdating = False
        Call ShowtheRibbon
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
        ActiveWindow.DisplayGridlines = True
        ActiveWindow.DisplayWorkbookTabs = True
        Application.ScreenUpdating = True
     End If
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:JimFive
ID: 40395736
I have two possibilities
1) In Sub UsedRangeZoom() you are changing the selected range.  This is probably what is killing your copy/paste.

2) In your modified code, if cutcopymode = true save a range variable to equal the selected range then at the end of your process reselect that range and copy it. (This might be an issue if the user is cutting, you'll need to test that)
0
 
LVL 1

Author Comment

by:-Polak
ID: 40395759
Hi Jim, when I was testing earlier it appeared that almost any Application.XXXXXXXXXXXXXX or ActiveWindow.XXXXXXXXX expression pretty much killed the copied range. Even something as simple as CalculationAutomatic/Manual will kill it. I think the only one that didn't effect it was xlMaximize.

With respect to #2 would that work if the range were in another workbook? or in this workbook going into another upon deactivation? (and/or a second instance of excel)
0
 
LVL 15

Accepted Solution

by:
JimFive earned 1500 total points
ID: 40397758
#2 should work as the range object would be associated with a specific range of cells on a specific worksheet.  So something like
Dim r as Range
Set r = Application.Selection
' Do other stuff here
r.select

Open in new window

0
 
LVL 1

Author Closing Comment

by:-Polak
ID: 40406419
Thanks Jim I should be able to take it from there
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question