PDA

View Full Version : Opening a new file of a different format



mshbhwn98
02-28-2013, 10:05 AM
Hi,
I have 2 sub routines that remove the gridlines, headings and make excel full screen. Is there a way to allow me to open another excel spreadsheet ie file new without it looping through the code below?

So what I am seeing is when I click file new it flicks through the worksheets of my open workbook and won't let me see the other workbook that I just tried to create. It is there but I can't get to it.

The second sub routine undoes all the changes I made to set excel back to its previous settings.

Private Sub Workbook_Activate()
Dim ws As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
Set wbBook = ThisWorkbook
For Each wsSheet In wbBook.Worksheets
If Not wsSheet.Name = "Blank" Then
wsSheet.Activate
With ActiveWindow
.DisplayHeadings = False
.DisplayGridlines = False
'.DisplayWorkbookTabs = True
'.DisplayHorizontalScrollBar = False
End With
End If
Next wsSheet
Sheets("Summary").Activate
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Deactivate()
Dim ws As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayFullScreen = False
Set wbBook = ThisWorkbook
For Each wsSheet In wbBook.Worksheets
If Not wsSheet.Name = "Blank" Then
wsSheet.Activate
With ActiveWindow
.DisplayHeadings = True
.DisplayGridlines = True
'.DisplayWorkbookTabs = True
'.DisplayHorizontalScrollBar = False
End With
End If
Next wsSheet
Sheets("Summary").Activate
Application.ScreenUpdating = True
End Sub

dazwm
03-05-2013, 12:38 PM
To help read the code easier always wrap it like below


Private Sub Workbook_Activate()
Dim ws As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
Set wbBook = ThisWorkbook
For Each wsSheet In wbBook.Worksheets
If Not wsSheet.Name = "Blank" Then
wsSheet.Activate
With ActiveWindow
.DisplayHeadings = False
.DisplayGridlines = False
'.DisplayWorkbookTabs = True
'.DisplayHorizontalScrollBar = False
End With
End If
Next wsSheet
Sheets("Summary").Activate
Application.ScreenUpdating = True
End Sub

mshbhwn98
03-05-2013, 01:57 PM
Apologies dazwm. I just didn't realise there was a button to highlight the code.
Now I know how to do it.

I thought my explanation was clear.

I didn't upload a workbook because I didn't think it was necessary with such a small piece of code that doesn't do a great deal.

Thanks for your advice