Auto_open won't run on multiple files
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? |
Auto_open won't run on multiple files
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? |
Auto_open won't run on multiple files
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. |
All times are GMT +1. The time now is 01:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com