Solved

Copy and Paste Disabled on Workbook/Worksheet Activate/Deactivate

Posted on 2014-10-10
12
596 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
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 500 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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