ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Activate Sheet (https://www.excelbanter.com/excel-programming/421852-activate-sheet.html)

Martin
Activate Sheet
 
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
Activate Sheet
 
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

Martin
Activate Sheet
 
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

Howard31
Activate Sheet
 
Hi Joel,

Try The following statement at the beginning of the precedu
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

Martin
Activate Sheet
 
Thank you, that has rectified the problem.

Many thanks,

Martin

"Howard31" wrote:

Hi Joel,

Try The following statement at the beginning of the precedu
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

JLGWhiz
Activate Sheet
 
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


All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com