How to retrieve filename on startup

user_8722088 0
Viewed 102

I'm attempting to conditionally launch a user form when excel opens based on the file's name. However, I believe that the code is running before the file fully launches, giving me an error.

Private Sub Workbook_Open()
    Dim name As String
    name = ActiveWorkbook.FullName
    If InStr(name, "Export Checksheet") > 0 Then
        UserForm1.Show
    End If
End Sub

Testing this on a file that's already open runs fine, but trying to let it run naturally as the file opens causes a Run-time error '91' and points me at the line defining the name variable.

vba excel
2 Answers

Looks like ThisWorkbook is loading before whatever other workbook you're opening, so ActiveWorkbook is Nothing and any member call against it will raise error 91.

The Workbook_Open handler will only ever run for ThisWorkbook (the document that's hosting the VBA project) - what you need is to wire up another handler, at application level, so that you can run code whenever any workbook is opened.

Change your Workbook_Open handler to capture a reference to the Application instance into a module-level WithEvents object variable:

Option Explicit
Private WithEvents app As Excel.Application

Private Sub Workbook_Open()
    Set app = Me.Application
End Sub

Now select app from the left-hand dropdown, and create a handler for the WorkbookOpen event - that is where you'll want to verify whether you need to pop up that dialog:

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If InStr(Wb.FullName, "Export Checksheet") > 0 Then
        With New UserForm1
            .Show
        End With
    End If
End Sub

Note that the application-wide event provides you with the Workook object, so you don't need to care whether it's the ActiveWorkbook.

user_11885130

This will work:

Option Explicit
Private Sub Workbook_Open()

    Dim wb As Workbook

    For Each wb In Workbooks
        If wb.name Like "*Export Checksheet*" Then
            wb.Activate
            Userform1.Show
        End If
    Next wb

End Sub

Though I don't get why do you do this, why don't you write this code and the userform only on the workbook Export checksheet?

user_75586820
Related