PDA

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,

X10A
01-19-2009, 11:02 PM
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.

GTO
01-19-2009, 11:23 PM
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,

GTO
01-19-2009, 11:53 PM
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