CODEWITHSUNDEEP
excelvba
koci
koci

Reputation: 23

Workbooks.Add doesn't activate the new workbook when running from Workbook_Open()

I'm trying to run a macro when my workbook is opened. The macro is supposed to create a new workbook using Workbooks.Add and end with the new workbook active. However, I cannot seem to make this happen no matter what I try.

The simplest attempts I tried were the following (you can try it yourself):

Private Sub Workbook_Open()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    wb.Activate
End Sub

Or this one from within the module itself:

Sub Auto_Open()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    wb.Activate
End Sub

I tried various things such as adding the following lines of code:

DoEvents or

Application.Wait (Now + TimeValue("00:00:02"))

I also tried using If Then statements to check the name of the active workbook, but that didn't work either.

In the end, the original file that I clicked on is still the active workbook. How can I get the newly created workbook to be the active window when the macro is finished running? Thanks!

Edit: By the way, note that if you try running the exact same code in a regular sub, it behaves the way that I want. It just doesn't seem to work when running in the 'run on workbook open' subs.

Upvotes: 1

Views: 2238

Answers (2)

Mikku
Mikku

Reputation: 6654

I have been running all the different ways I can. But a workaround is to add a MsgBox

Private Sub Workbook_Open()

        Dim wb As Workbook
        Set wb = Workbooks.Add
        wb.Activate

        MsgBox "New Book Added"

End Sub

When you will click okay on the MsgBox, it will stay on the Newly created Workbook.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

Application.Wait just idles the Excel Application, so that won't work as you've observed. If all else fails (I'm not in a position to test at the moment) you could use Application.OnTime to schedule a procedure that creates a new workbook and activates it, 1 second after the Workbook_Open event:

Option Explicit

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:01"), "addNewWb"
End Sub

Private Sub addNewWb()
Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate
End Sub

Upvotes: 1

Related Questions