Userform won't open with worksheet. Causing error and force close.

[drew.j.harrison]

[Note: I made a mistake in the title of this thread it should say workbook not worksheet]
So I have a user form called "User_Controls" and another macro that opens and locates it. When I try to call this in the Sub Workbook_Open it force closes excel. The weird thing is it only does it if I have already enabled macros for the workbook. When I change the workbook name so it prompts me to enable macros again and click "enable content" it works just fine. Any idea what could be going on here and how I could fix it? BTW I have also tried using Sub Workbook_Activate as well to launch the user form and got the same result. My code is below. Thanks for the help.

Private Sub Workbook_Open()
    Call Open_And_Locate_Userform(User_Controls)
End Sub

Sub Open_And_Locate_Userform(UF)
    Application.ScreenUpdating = False
    With UF
        .Show
        .StartUpPosition = 0
        .Top = Application.Top + Application.Height - UF.Height - 33
        .Left = Application.Left + Application.Width - UF.Width - 17
    End With
    AppActivate "Microsoft Excel"
    Application.ScreenUpdating = True
End Sub

FYI I also posted this question on the excelforum forums here:
Userform won't open with worksheet. Causing error and force close.

 

[Norie]

Sorry, little confused here.

I suggested you try it with only the code to show the form, is that what you've now done?

Actually from the description of the error message and where/when it occurs it sounds like there is a problem with the userform.

Try going to Tools>Options... and on the General tab select eithe Break on All Errors or Break in Class Modules.

Then goto the ThisWorkbook module and run the Open event with F5.

What happens when you do that?

Also, have a look under Tools>References..., specifically for anything marked as MISSING.

 

[LEE355]

Hi,
I would try this in a new workbook and close all other workbooks while you're testing it:

Insert a new UserForm and add a CommandButton to the form.

Rename the form as User_Controls and leave the CommandButton name as is.

In the Workbook_Open() module:

Private Sub Workbook_Open()
  ShowUserForm 'procedure call
  Exit Sub
End Sub

In the module behind the userform:

Private Sub CommandButton1_Click()
  Hide
End Sub
Private Sub UserForm_Activate()
    Me.StartUpPosition = 3
''''Uncomment these lines if you want to set the form in the lower right corner.
'  Dim app As Application
'  Set app = Application
'  With app
'    .ScreenUpdating = False
'    Me.Top = .Top + .Height - Me.Height - 33
'    Me.Left = .Left + .Width - Me.Width - 17
'    .ScreenUpdating = True
'  End With
End Sub
Private Sub UserForm_Initialize()
  With CommandButton1
    .Caption = "Close"
  End With
End Sub

In a standard module:

Sub ShowUserForm()
  Application.ScreenUpdating = False
  User_Controls.Show
  Application.ScreenUpdating = True
End Sub

Push F5 while in the the Workbook_Open sub to run it.

Add a commandbutton to the worksheet from the Forms toolbox, rightclick the button, click Assign macro
and choose ShowUserForm to run the code.

Summary of what was done:
1) Deleted the AppActivate "Microsoft Excel" line. The AppActivate statement is supposed to be used to
open an application. In this case you're trying to open a new instance of Excel, which is why you're
getting the Enable Macros message. This is the root of your problem.
2) Added the line Exit Sub as the last line in the Workbook_Open method.
3) Deleted the line .StartUpPosition = 0
4) Put the StartUpPosition statements in the UserForm_Activate method in the code behind the userform.
5) If you want to center the form regardless of user equipment, use the StartUpPosition property set to
the constant 2. Don't use CenterOwner as there's a bug. Or you can leave you're formula in but on
my equipment the form shows up in the bottom right corner.

 

[drew.j.harrison]

Sorry, little confused here.

I suggested you try it with only the code to show the form, is that what you've now done?

Actually from the description of the error message and where/when it occurs it sounds like there is a problem with the userform.

Try going to Tools>Options... and on the General tab select eithe Break on All Errors or Break in Class Modules.

Then goto the ThisWorkbook module and run the Open event with F5.

What happens when you do that?

Also, have a look under Tools>References..., specifically for anything marked as MISSING.

Yes that is what I have done. Tried both the 'Break on All Errors" and "Break in Class Modules" still get the error when opening the workbook if macros have been enabled already. Code works just fine if I launch manually with F5 or if the workbook is opened without macros enabled then enabled manually. I went to Tools>References and didn't see anything marked as MISSING but I'm not sure exactly what I am looking for here.

LEE355 - working on trying what you have listed. Will have feedback soon.

 

[Jaafar Tribak]

Just a stab in the dark ... Try this :
Sub Open_And_Locate_Userform(ByVal UF)</pre>