CODEWITHSUNDEEP
excelvbaworksheet
dud
dud

Reputation: 21

Excel VBA: Workbook_Open

I'm using Workbook_Open to call a userform when the application is opened, this is working fine. However I would like it to only run the first time it is opened. I tried this and it work if I run the sub from the editor but not when I open the file.

Sub Workbook_Open()
If Worksheets("DataSheet").Range("A1").Value = "" Then
     QuickStartForum.Show
End If
End Sub

Note: A1 contains a value that will be populated after the user form has run

It appears that the problem is that it opens the user form before the data is loaded into the worksheet.

Is this there a way around this or do I need to take a different approach ?

Upvotes: 2

Views: 18312

Answers (1)

Chris
Chris

Reputation: 5615

I think it is because you have this code in a Module. You need to put the code within 'ThisWorkBook'.

I tried this following code and had no issues when it was in the 'ThisWorkBook' it failed to run within 'Module1'

Private Sub Workbook_Open()
    If Worksheets("DataSheet").Range("A1").Value = "" Then
         QuickStartForum.Show
         Worksheets("DataSheet").Range("A1").Value = "filled" ' <-- this fills the cell with data for testing, so that when you reopen the file it should not re-open the userform
    Else
        MsgBox ("not shown because the A1 cell has data")
    End If
End Sub

Upvotes: 8

Related Questions