PDA

View Full Version : Solved: Setting Window height & width



jammer6_9
04-12-2007, 01:46 AM
During workbook activate, I have set window height & width as I wanted but on workbook deactivate (HOW??? :dunno ) I want to put it back to the application default settings so that when I open another workbook it will not be affected...


Option Explicit
Private Sub Workbook_Activate()
With ActiveWindow
.WindowState = xlNormal
.Width = Application.UsableWidth
.Left = 1
.Top = 18
.Height = 510
End With
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
With ActiveWindow
.WindowState = xlNormal

End With
End Sub

geekgirlau
04-12-2007, 02:20 AM
Private Sub Workbook_Deactivate()
On Error Resume Next
ActiveWindow.WindowState = xlMaximized
End Sub

jammer6_9
04-12-2007, 02:30 AM
geekgirlau I already tried that code... When I exit the workbook and open new one settings of the my previous workbook still affects all workbooks. Please take a look with the code that I have written...


Private Sub Workbook_Activate()
On Error Resume Next
With ActiveWindow
.WindowState = xlNormal
.Width = Application.UsableWidth
.Left = 1
.Top = 18
.Height = 510
End With
strMessage = Sheets("Disclaimer").Cells(1, 1).Text
frmDisclaimer.Show

Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Chart Menu Bar").Enabled = False
Application.ActiveWindow.EnableResize = False

Application.CommandBars("Worksheet Menu Bar").Controls("Format").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("Edit").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("View").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("Data").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("Window").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("Help").Delete
Application.DisplayFormulaBar = False
'Application.DisplayFullScreen = True
Application.DisplayStatusBar = False
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("borders").Enabled = False
Application.CommandBars("Chart").Enabled = False
Application.CommandBars("Control Toolbox").Enabled = False
Application.CommandBars("Drawing").Enabled = False
Application.CommandBars("External Data").Enabled = False
Application.CommandBars("Forms").Enabled = False
Application.CommandBars("Formula Auditing").Enabled = False
Application.CommandBars("List").Enabled = False
Application.CommandBars("Pictures").Enabled = False
Application.CommandBars("PivotTable").Enabled = False
Application.CommandBars("Protection").Enabled = False
Application.CommandBars("Reviewing").Enabled = False
Application.CommandBars("Text To Speech").Enabled = False
Application.CommandBars("Visual basic").Enabled = False
Application.CommandBars("Watch Window").Enabled = False
Application.CommandBars("WordArt").Enabled = False
Application.CommandBars("CheckBoxes").Enabled = False
Application.CommandBars("Flow Connectors").Enabled = False
Application.CommandBars("Flow Shapes").Enabled = False
Application.CommandBars("Excel Add-in for Analysis Service").Enabled = False
Application.CommandBars("Custom 1").Enabled = False
Application.CommandBars("Custom 2").Enabled = False
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
ActiveWindow.WindowState = xlMaximized

Unload frmDisclaimer
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Chart Menu Bar").Enabled = True
Application.DisplayFormulaBar = True
Application.DisplayFullScreen = False
Application.DisplayStatusBar = True
Application.ActiveWindow.EnableResize = True
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Standard").Enabled = True
Application.CommandBars("borders").Enabled = True
Application.CommandBars("Chart").Enabled = True
Application.CommandBars("Control Toolbox").Enabled = True
Application.CommandBars("Drawing").Enabled = True
Application.CommandBars("External Data").Enabled = True
Application.CommandBars("Forms").Enabled = True
Application.CommandBars("Formula Auditing").Enabled = True
Application.CommandBars("List").Enabled = True
Application.CommandBars("Pictures").Enabled = True
Application.CommandBars("PivotTable").Enabled = True
Application.CommandBars("Protection").Enabled = True
Application.CommandBars("Reviewing").Enabled = True
Application.CommandBars("Text To Speech").Enabled = True
Application.CommandBars("Visual basic").Enabled = True
Application.CommandBars("Watch Window").Enabled = True
Application.CommandBars("WordArt").Enabled = True
Application.CommandBars("CheckBoxes").Enabled = True
Application.CommandBars("Flow Connectors").Enabled = True
Application.CommandBars("Flow Shapes").Enabled = True
Application.CommandBars("Excel Add-in for Analysis Service").Enabled = True
Application.CommandBars("Custom 1").Enabled = True
Application.CommandBars("Custom 2").Enabled = True
End Sub

geekgirlau
04-12-2007, 02:34 AM
Just a thought, but you may need

Application.ActiveWindow.EnableResize = True

prior to maximising the window

jammer6_9
04-12-2007, 02:39 AM
it was an exact thought geekgirlau... thnxxxx :bow:


Just a thought, but you may need

Application.ActiveWindow.EnableResize = True

prior to maximising the window