View Full Version : Solved: Workbook Event CODE
xluser2007
01-19-2009, 10:36 PM
Hi All,
For a particular workbook, I would like to know how to write code whereby upon opening the workbook it would:
Select A1 for All Worksheets except
in worksheet "Introductions" select range A3.
in worksheet "Annual certification" select range A10.
in worksheet "Housekeeping Fund Data DV" select range A6.
Zoom all worksheets at 100% but zoom "Introductions" at 115% and "Annual certification" at 140%.
Ensure that all worksheets are viewed in "Normal View" and that there are no Pagebreaks i.e. pagebreaks option is unchecked
Activate the worksheet "Introductions"If anyone could please help with the above code I would really appreciate it.
regards,
Have you tried using the macro recorder? I think that should do the trick.
nst1107
01-19-2009, 11:02 PM
The macro recorder would be excellent here. Just do everything you want while the macro recorder is running and you will have your solution. It would probably take twice as long to write the code from scratch.
Greetings xluser,
Not well tested, so try in a copy first, but this should work.
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Select
wks.DisplayPageBreaks = False
ActiveWindow.View = xlNormalView
Select Case wks.Name
Case "Introductions"
ActiveWindow.Zoom = 115
Range("A3").Select
Case "Annual certification"
ActiveWindow.Zoom = 140
Range("A10").Select
Case "Housekeeping Fund Data DV"
Range("A6").Select
Case Else
Range("A1").Select
ActiveWindow.Zoom = 100
End Select
Next
End Sub
Hope this helps,
Mark
xluser2007
01-19-2009, 11:38 PM
Hi All,
Firstly, Nate and X10A, I fully appreciate your suggestions for using the macro recorder - here is the cleanest output I could produce:
Sub Macro3()
Range("A3").Select
Sheets("Annual certification").Select
Range("A10").Select
Sheets("Housekeeping Fund Data DV").Select
Range("A6").Select
Sheets("Introduction").Select
ActiveWindow.Zoom = 115
Sheets("Annual certification").Select
ActiveWindow.Zoom = 140
Sheets(Array("VisiPlan Report 1", "Fund Data DV", "Housekeeping Fund Data DV", _
"General DV Lists")).Select
Sheets("VisiPlan Report 1").Activate
ActiveWindow.Zoom = 100
Sheets("VisiPlan Report 1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("Introduction", "Annual certification", "VisiPlan Report 1", _
"Fund Data DV", "Housekeeping Fund Data DV", "General DV Lists")).Select
Sheets("Introduction").Activate
Sheets("Introduction").Select
End Sub
I know this can be cut down, but I was really hoping to learn how to do a simple Select Case statement for the relevant worksheets.
As such, this brings me to GTO's (Mark's) fantastic code, which is ideally what I was looking for.
Mark it works fantastically and just the sort of technique I wanted to learn. Thanks for your great help.
regards,
Hi xluser,
Shucks, you'll want to turn 'grammar check' back on if you didn't get one of them dang little wavy lines when you put "Mark" and "fanstastic code" in the same sentence. I usually test my code like this> :hide:
Have a great evening and happy to help :beerchug:
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.