Activate Sheet

[Martin]

Hello,

I have some code that formats my workbook which is below:

Sub Format()
Application.DisplayFullScreen = True
Application.CommandBars("Worksheet Menu Bar").Enabled = False

Sheets("Main Menu").Select
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

Sheets("H1").Activate
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

Sheets("H2").Activate
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

Sheets("Main Menu").Select

End Sub

I run this when the workbook is opened by the user and only takes a second
to run however the user can see the sheets being selected and then revert
back to the main menu.

I was wondering if there is a way to get the code to point to the sheets H1
and H2 but without actually selecting it or the user seeing them being
selected.

I appreciate this is purely cosmetic but it would make the front end look a
little better.

Thanks in advance for your help.

Martin

 

[Joel]

Use the title of the window for this code to work like book1.xls. These
properties don't apply to individual worksheets.

Sub Format()
Application.DisplayFullScreen = True
Application.CommandBars("Worksheet Menu Bar").Enabled = False

With Windows("Main Menu")
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

With Windows("H1")
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

With Windows("H2")
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

End Sub

 

[Martin]

Joel,

Sorry, I have tried this and the error message says script out of range.

Any ideas on this?

Martin

 

[Howard31]

Hi Joel,

Try The following statement at the beginning of the precedure:
Application.ScreenUpdating = False

Than at the end of the procedure set it back to True:

Application.ScreenUpdating = True

Hope that will help

 

[Martin]

Thank you, that has rectified the problem.

Many thanks,

Martin

 

[JLGWhiz]

Hi Martin, You don't need the sheet references to set window attributes.
Those are part of Windows and therefore subordinate to the Application
object. Just remove all of your sheet references and it should solve your
problem.