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

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
[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.

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

Code:
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.
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Does the userform open without problem if you do it manually?
 
Upvote 0
What if you remove/comment all the code apart from .Show?
 
Upvote 0
I've no idea what the problem is.

Is there other code?

What's the purpose of this?
Code:
AppActivate "Microsoft Excel"
 
Upvote 0
I use AppActivate "Microsoft Excel" to switch the focus back to the worksheet after the user form opens. There is other code in the Open Workbook but I don't believe that is affecting it since I deleted everything but the Call Open_And_Locate_Userform(User_Controls) and still had the problem. I'll try again with no code except User_Controls.Show and see if that works.
 
Upvote 0
I thought you tried that already and it didn't make a difference?
 
Upvote 0
I thought you tried that already and it didn't make a difference?

I did but I still had the other code in. It isn't force closing but it is giving me a "Path/File access error" then a Run-time error '75': Could not find the specified object.

Still only happens when macros have already been enabled for that file and not when I have to manually enable them.

When I click debug it highlights the User_Controls.Show The VBA window doesn't look right either nothing shows up in the VBA Project Side Bar. Almost like the project didn't have time to load.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,217,758
Messages
6,138,438
Members
450,137
Latest member
HANHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top