Auto_open won't run on multiple files

[augrection]

I have 3 workbooks for weekly payroll: Management, Admins, and Agents.
Each file has 7 worksheets, named "Sunday", through "Saturday".
Different people pull up each file and some forget to select the
correct day before editing. So as my first try at VBA, created the
code below to select the correct worksheet.

My auto_open routines work fine if opening each file seperately. But
if selecting all three files and choosing open, only one auto_open
runs. Using xl 2003 on a network.



Sub auto_open()
Dim mydate As Date
mydate = Weekday(Now)
If mydate = 1 Then
ThisWorkbook.Sheets(1).Select
ElseIf mydate = 2 Then
ThisWorkbook.Sheets(2).Select
ElseIf mydate = 3 Then
ThisWorkbook.Sheets(3).Select
ElseIf mydate = 4 Then
ThisWorkbook.Sheets(4).Select
ElseIf mydate = 5 Then
ThisWorkbook.Sheets(5).Select
ElseIf mydate = 6 Then
ThisWorkbook.Sheets(6).Select
ElseIf mydate = 7 Then
ThisWorkbook.Sheets(7).Select
End If
End Sub


The filenames will change periodically and we only open all three
files at the end of the day. I tried using workbook_open with renaming
all three subroutines to unique names with the same results.

I'm also curious, what's the shortest code to accomplish the above?

 

[Jim Cone]

The auto_open sub goes in a standard module...
'--
Sub Auto_Open()
Dim mydate As Long
mydate = Weekday(Now)
ThisWorkbook.Sheets(mydate).Select
End Sub
'--
I've never seen your auto_open problem before, but that is probably
because I've never tried opening 3 workbooks at the same time with
event code in them.
No guarantee, but try this in each workbook open event code sub...

ThisWorkbook.RunAutoMacros xlAutoOpen
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"augrection"
wrote in message
I have 3 workbooks for weekly payroll: Management, Admins, and Agents.
Each file has 7 worksheets, named "Sunday", through "Saturday".
Different people pull up each file and some forget to select the
correct day before editing. So as my first try at VBA, created the
code below to select the correct worksheet.

My auto_open routines work fine if opening each file seperately. But
if selecting all three files and choosing open, only one auto_open
runs. Using xl 2003 on a network.



Sub auto_open()
Dim mydate As Date
mydate = Weekday(Now)
If mydate = 1 Then
ThisWorkbook.Sheets(1).Select
ElseIf mydate = 2 Then
ThisWorkbook.Sheets(2).Select
ElseIf mydate = 3 Then
ThisWorkbook.Sheets(3).Select
ElseIf mydate = 4 Then
ThisWorkbook.Sheets(4).Select
ElseIf mydate = 5 Then
ThisWorkbook.Sheets(5).Select
ElseIf mydate = 6 Then
ThisWorkbook.Sheets(6).Select
ElseIf mydate = 7 Then
ThisWorkbook.Sheets(7).Select
End If
End Sub


The filenames will change periodically and we only open all three
files at the end of the day. I tried using workbook_open with renaming
all three subroutines to unique names with the same results.

I'm also curious, what's the shortest code to accomplish the above?

 

[augrection]

I did have the auto_open subs in a standard module but started
experimenting when they wouldn't run.
I also tried opening multiple worksheets with just a simple "hello
world" message boxes and they all ran.
Thanks for the quick reply. I'll give it a try tomorrow.