Hiding ribbon on a particular worksheet

[Sarguroh]

Dears,
Can anybody help me to hide ribbon, headings, gridlines, ruler only for one specific sheet.

I have found the below code, but this is applying for all the sheet on workbook.

((( Use code-tags )))

Private Sub Workbook_Open()
Set myRange = ActiveSheet
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ThisWorkbook
For Each wsSheet In wbBook.Worksheets
If Not wsSheet.Name = "Blank" Then wsSheet.Activate
With ActiveWindow
.DisplayHeadings = False
.DisplayGridlines = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
'ActiveSheet.EnableSelection = xlUnlockedCells
End With
Next wsSheet
myRange.Select
End Sub

 

[Stevie]

Hi Sarguroh,
if you want to remove headings and gridlines for a specific sheet, then simply remove the for loop in your above code and select only the sheet you want to remove it for.
My understanding is that the ribbon is visible or not, so you wouldn't be able to make that show for all sheets but one without including code which hides the ribbon when the sheet is selected and then makes it visible when each of the others is selected.

 

[Logit]

.
More specifically :

Paste this into the worksheet to remove grid lines, etc.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    With ActiveWindow
    .DisplayHeadings = False
    .DisplayGridlines = False
    End With
Application.ScreenUpdating = True
End Sub
Sub yesem()
With Sheets("Sheet1")
    Application.ScreenUpdating = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
 
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayGridlines = True
    End With
End With
End Sub

Private Sub Worksheet_Deactivate()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub

If the sheet in question is Sheet1, paste this code into the ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
    Sheets("Sheet2").Select
    Sheets("Sheet1").Select
End Sub

This is one way to accomplish your goal.

 

[Sarguroh]

Thanks for your reply. Really appreciate your help.

However, when I close and open the sheet again. It doesn't implement. I have to run code every time when I open the workbook. It should be fixed, when I open the workbook it should be already implemented.

Thanks in advance for your support

 

[Logit]

Did you download the sample workbook ? Did you look where the code is pasted ?

It sounds like you have one or more macros pasted in the wrong location. The sample workbook functions as desired.

 

[Sarguroh]

Yes, there's one more code on workbook.

Private Sub Workbook_Open()

Worksheets("Splash").Activate

End Sub

 

[Logit]

If the sheet SPLASH is the target of the above macro (removing gridlines, menu bar, etc), then use this macro in ThisWorkbook to replace anything previously indicated:

Option Explicit

Private Sub Workbook_Open()
    Worksheets("Splash").Activate
End Sub

Then, make certain the following code is pasted into the Worksheet module for the SPLASH sheet :

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    With ActiveWindow
    .DisplayHeadings = False
    .DisplayGridlines = False
    End With
Application.ScreenUpdating = True
End Sub
Sub yesem()
With Sheets("Sheet1")
    Application.ScreenUpdating = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayGridlines = True
    End With
End With
End Sub

Private Sub Worksheet_Deactivate()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub