PDA

View Full Version : Excel Page Format-help pls.



Angela
09-19-2012, 08:05 AM
Hello everyone,
I have a workbook called "render" with sheet1 named "return" and when I open "return" sheet I would like the following format to be applied to the whole page.

Private Sub Workbook_Activate()

On Error Resume Next
With Application
.DisplayFullScreen = True
.CommandBars("Worksheet Menu Bar").Enabled = False
Application.ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayWorkbookTabs = False
Range("A1").Select

End With

End Sub

The good thing is that it does that, it removes the grid, bars and so on, but unfortunately this format applies to every single excel page in my computer. How do I limit my code to only current workbook called "render" and applied only to the sheet1 called "return".
Thank you for your help, angela.

pls don't laugh, i'm just a hopeless starter.

mancubus
09-19-2012, 10:33 AM
hi. wellcome to the forum.

goes to ThisWorkbook code module of render.xlsm or render.xls:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If UCase(Sh.Name) <> UCase("return") Then Exit Sub

On Error Resume Next

Application.DisplayFullScreen = True
Application.CommandBars("Worksheet Menu Bar").Enabled = False
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayWorkbookTabs = False
Range("A1").Select

End Sub



Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

If UCase(Sh.Name) <> UCase("return") Then Exit Sub

On Error Resume Next

Application.DisplayFullScreen = False
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True
Range("A1").Select

End Sub

Angela
09-19-2012, 11:05 AM
Thank you so much. :hi:

mancubus
09-19-2012, 01:03 PM
you're most wellcome.

Angela
09-19-2012, 01:18 PM
Please check this out. Inside the workbook all the sheets opens without being adjusted to the code, but if I close the workbook from the sheet "return" and I try to open any other excel workbook, it will take the new format, in other words they are being ajusted to the code again, but not all of it. Hmm..why is this happening?:(. To be more specific in here, when I open another workbook the Worksheet Menu Bar does not get Enabled, all the rest gets enabled.

mancubus
09-19-2012, 02:12 PM
you may select another sheet by hitting ctrl+pgup or pgdn, then close the workbook.

or add below to ThisWorkbook:


Private Sub Workbook_Deactivate()

On Error Resume Next

Application.DisplayFullScreen = False
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True

End Sub

Angela
09-19-2012, 03:47 PM
Thank you, now it's working independently.