Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Copy and Paste Disabled on Workbook/Worksheet Activate/Deactivate

Posted on 2014-10-10
12
Medium Priority
?
897 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
11 Comments
 
LVL 27

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 27

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

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 27

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 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