Hide and unhide Ribbons VBA

[dannyok90]

Hi All,
I have some code that hides the majority of the excel interface on workbook_open to prevent users from making changes that they shouldn't be.
It works well but at the moment, when the worksheet opens and hides the ribbons etc it hides any other workbooks ribbons that are open, i put some code at
workbook_close to unhide the ribbons when they close the workbook but it doesnt always make them reapear on there other spreadsheets they have open.
does anybody have any ideas?

Thanks, Dan

In this workbook:

Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
ActiveWindow.DisplayWorkbookTabs = False
MsgBox "HOW TO USE:" & vbNewLine & "Cells to be filled in using concise wording in capital letters." & vbNewLine & "" & vbNewLine & "Progress to Plan & Resource -  Contract dates to be taken from Insight." & vbNewLine & "" & vbNewLine & "Deliverables - Only show outstanding." & vbNewLine & "" & vbNewLine & "Once you have completed the  PSR, Click 'Submit'"
End Sub

Module 1: (Submit button)

Public Sub SaveAsC3()
    Dim ThisFile As String, DoF As String
    ThisFile = Range("C3").Value
    DoF = Range("V2").Value
    fName = "XXXXXXXX" & ThisFile & " " & "XXX" & " " & DoF
    ActiveWorkbook.Save
    On Error GoTo err
    Application.DisplayAlerts = False
    If (MsgBox("Are you sure you want to submit this PSR?", vbYesNo, "Decision")) = vbYes Then
        With ActiveWorkbook
            .SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",TRUE)"
            ActiveWindow.DisplayWorkbookTabs = True
            Application.DisplayAlerts = False
            If Workbooks.Count < 2 Then
            Application.Quit
 Else
 ThisWorkbook.Close
 End If

Module 2: (save and close button)

Sub SaveandQuit()
Application.DisplayAlerts = False
ThisWorkbook.Save
 If Workbooks.Count < 2 Then
 Application.Quit
 Else
 Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",TRUE)"
 ActiveWindow.DisplayWorkbookTabs = True
 ThisWorkbook.Close
 End If
 End Sub

 

[Yongle]

(in the workbook where ribbon should be hidden)
There is also a workbook_Deactivate event
- use that to to reset ribbon to normal
Use the Workbook_Activate event to hide the ribbon again
Hopping between different open workbooks should trigger the correct ribbon setting

 

[dannyok90]

Worked a treat, thanks very much!

Dan