VBA code activate and deactivate toolbar, ribbon but allow copy and paste?

[danbates]

Hi,

I have the following codes that hides the ribbon and that but it also stops me from copy and pasting from another sheet.

Private Sub Workbook_Activate()

    With Application
      .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .DisplayFormulaBar = False
        .DisplayStatusBar = Not Application.DisplayStatusBar
        .ScreenUpdating = True
    End With


End Sub


Private Sub Workbook_Deactivate()
    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ScreenUpdating = True
    End With


End Sub

Does anyone know if there is a way to amend these codes but allow copy and paste to work?

Thanks

Dan

 

[rlv01]

FWIW, I ran your code and I can still copy and paste from another sheet no problem.

 

[Yongle]

How are you trying to copy and paste?

{Ctrl} C \ {Ctrl} V ?
Right-Click + select Copy \ Right-Click + select Paste ?
Another way?- please detail

Which version of Excel are you using?

 

[danbates]

Hi,

Thank you for you replies.

I've made an error on my original post.

I put I can't copy and paste from another sheet, but I meant I can't copy and paste from another workbook.

I {Ctrl} C \ {Ctrl} V to copy and paste and I am using Excel 13

Thanks

Dan

 

[Yongle]

It appears that the Activate_Workbook macro is causing your problem
- issue avoided by preventing that macro from running when the workbook is reactivated

1. Create a new standard module and rename it Ribbon
(click on Module in Project Window \ {F4} to see Properties window \ amend name to Ribbon)
2. Replace existing VBA with VBA below

Everything kept simple - hopefully not too simple!
Let us know if anything is not behaving as required

In ThisWorkbook module

Option Explicit

[COLOR=#006400][I]Private Sub Workbook_Open()[/I][/COLOR]
    Application.Run "[B]Ribbon[/B].HideRibbon"
[COLOR=#006400][I]End Sub[/I][/COLOR]

[COLOR=#006400][I]Private Sub Workbook_Activate()[/I][/COLOR]
    If RibbonHide = False Then Exit Sub
     Application.Run "[B]Ribbon[/B].HideRibbon"
[COLOR=#006400][I]End Sub[/I][/COLOR]

[COLOR=#006400][I]Private Sub Workbook_Deactivate()[/I][/COLOR]
    RibbonHide = False
    Application.Run "[B]Ribbon[/B].ShowRibbon"
[COLOR=#006400][I]End Sub[/I][/COLOR]

In Standard module (named Ribbon)

Option Explicit
Public RibbonHide As Boolean
[I][COLOR=#006400]
Private Sub HideRibbon()[/COLOR][/I]
    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .DisplayFormulaBar = False
        .DisplayStatusBar = Not Application.DisplayStatusBar
        .ScreenUpdating = True
    End With
[I][COLOR=#006400]End Sub[/COLOR][/I]

[I][COLOR=#006400]Private Sub ShowRibbon()[/COLOR][/I]
    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ScreenUpdating = True
    End With
[I][COLOR=#006400]End Sub[/COLOR][/I]

 

[danbates]

Hi Yongle,

it's very nearly spot on.

The only issue I can see is it leaves the formula bar showing but only after it's been activated not when the file is first loaded.

I'd prefer it to be hidden but if it's needed to be this way for the copy and paste to work then I'm happy to leave it as it is.

Thanks again

Dan

 

[Yongle]

try this
goes in ThisWorbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If RibbonHide = False Then
        RibbonHide = True
        Application.Run "Ribbon.HideRibbon"
    End If
End Sub

Copy & paste is still disabled in the other direction - does that require fixing too?
The way to do that would be to write a procedure
- asking user to specify "where to paste" and "range to be copied"
- copy & paste without deactivating the workbook