Unable to change ActiveSheet within Workbook_Open

  • Thread starter Thread starter Denis
  • Start date Start date
D

Denis

I'm trying to modify a macro that I inherited. This .xls (created as
a 2003 .xls but run under 2007) has a Workbook_Open macro that
automatically runs when the .xls is opened. The macro opens a .csv
file and then tries to make this .csv file the active sheet but it
seems that the active sheet can't be changed to this .csv file and the
active sheet remains as the original active sheet when the .xls was
initially opened. Here's the code fragment:

Sub Workbook_Open()
Workbooks.Open dailystart ' open the .csv file
Set wbdata = ActiveWorkbook
MsgBox ActiveWorkbook.Name
MsgBox ActiveSheet.Name
Range(Cells(2, 1), Cells(1, 1).End(xlDown)).NumberFormat = "mm/dd/
yy;@"

' explicitly activate the .csv workbook and sheet
wbdata.Activate
wbdata.Sheets(1).Activate
wbdata.Sheets(1).Select
wbdata.Sheets(1).Name = "Summary"
MsgBox ActiveWorkbook.Name
MsgBox ActiveSheet.Name

I would expect that the active workbook and sheet would change to
the .csv file (with it's single sheet) when it is opened. The above
MsgBox's show the active workbook is the .csv file but the active
sheet remains as the original active sheet. However, the NumberFormat
statement applies to the .csv file which suggests that the .csv file
is the active sheet.

Then the macro tries explicitly activate the just opened .csv file.
It successfully changes the name of the .csv file single sheet and the
active workbook is correct but the active sheet remains the original
sheet of the .xls of the Workbook_Open.

Can someone explain what is going on? Is there something special
about trying to change the active sheet from within a Workbook_Open
macro?

Denis
 
Can someone explain what is going on? Is there something special
about trying to change the active sheet from within a Workbook_Open
macro?
Click to expand...

Quite a few things in a workbook's open event are best deferred until the
workbook is fully open. Call your CSV stuff in an ontime macro, eg

' in the open event
Application.OnTime Now, "OpenCSV"

where Sub OpenCSV() is in a normal module with code to op[en the CSV etc

Regards,
Peter T
 
Can someone explain what is going on?  Is there something special
about trying to change the active sheet from within a Workbook_Open
macro?
Click to expand...

Quite a few things in a workbook's open event are best deferred until the
workbook is fully open. Call your CSV stuff in an ontime macro, eg

' in the open event
Application.OnTime Now, "OpenCSV"

where Sub OpenCSV() is in a normal module with code to op[en the CSV etc

Regards,
Peter T
Click to expand...

Yes, Application.OnTime Now works much better. Thanks.

Denis
 
Back
Top