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

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" wrote:

> 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,

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

Any ideas on this?

Martin

"Joel" wrote:

> 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" wrote:
>
> > 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

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

--
A. Ch. Eirinberg


"Joel" wrote:

> 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" wrote:
>
> > 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

Thank you, that has rectified the problem.

Many thanks,

Martin

"Howard31" wrote:

> 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
>
> --
> A. Ch. Eirinberg
>
>
> "Joel" wrote:
>
> > 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" wrote:
> >
> > > 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

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.
"Martin" wrote:

> Joel,
>
> Sorry, I have tried this and the error message says script out of range.
>
> Any ideas on this?
>
> Martin
>
> "Joel" wrote:
>
> > 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" wrote:
> >
> > > 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