Solved

Copy and Paste Disabled on Workbook/Worksheet Activate/Deactivate

Posted on 2014-10-10
12
472 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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
#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
Comment Utility
Thanks Jim I should be able to take it from there
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now