UserForm1.Show does not work when open excel (excel is saved in xlsm-macro enabled)

[allansanjan]

Hi guys, i cant get my userform auto-run/show when i open my excel file. Thanks in advance, i try to run it, all works fine.

My excel file is saved as .xlsm (enabled)

I have a Module1 (aznoltestmacro): i have done it and works perfectly,

UserForm1 codes are as below:

Private Sub CommandButton1_Click()

UserForm1.Show

Call aznoltestmacro

MsgBox "Report Done!", , "Report Progress"
End Sub

Private Sub CommandButton2_Click()
Unload Me

End Sub

 

[RoryA]

If you want it to happen when you open the workbook, you need to use the Workbook_Open event in the ThisWorkbook module, or use an Auto_Open routine in a normal module.

 

[Engberg]

To show the user form when you open the Excel-file, put UserForm1.Show in ThisWorkbook, Workbook_Open() sub. Paste this code in the ThisWorkbook object and it should work:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

 

[allansanjan]

To show the user form when you open the Excel-file, put UserForm1.Show in ThisWorkbook, Workbook_Open() sub. Paste this code in the ThisWorkbook object and it should work:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

View attachment 54868

Hi Engberg,
I tried using your method placing the UserForm1.Show under Workbook_Open(), it doesnt work.. showed error 424,
hereby attached errors, Workbook_Open() code & my UserForm1 code

 

[allansanjan]

If you want it to happen when you open the workbook, you need to use the Workbook_Open event in the ThisWorkbook module, or use an Auto_Open routine in a normal module.

Hi Rory,
Thanks, it works (userform pop out), but shows Run time error '1004' when i click on generate, can u teach me how and where did u input the code for the auto_open? i placed the auto_open under my module1,

Private Sub Auto_Open()
UserForm1.Show
End Sub

Sub aznoltestmacro()
'this is where my macro starts

photos are as below for reference, thank you

 

[RoryA]

You don't appear to have an activeworkbook so you can't select a sheet in it. Are you trying to refer to a sheet in the personal macro workbook?

 

[Engberg]

Hi Engberg,
I tried using your method placing the UserForm1.Show under Workbook_Open(), it doesnt work.. showed error 424,
hereby attached errors, Workbook_Open() code & my UserForm1 code
View attachment 54877View attachment 54881

You need a user form named UserForm1 in the same project as the Workbook_Open, right now it looks like you're using two different projects/files.

 

[allansanjan]

You need a user form named UserForm1 in the same project as the Workbook_Open, right now it looks like you're using two different projects/files.

[SOLVED]
Thanks Engberg! I recreated UserForm1 & Module1 under the same project files resulting the UserForm1 appears when i open the excel file. Thank you very much !