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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Does the userform open without problem if you do it manually?
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Yes. I also call the userform macro when I activate the worksheet it is used on. It works fine there as well.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
What if you remove/comment all the code apart from .Show?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
I've no idea what the problem is.

Is there other code?

What's the purpose of this?
Code:
AppActivate "Microsoft Excel"
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
I thought you tried that already and it didn't make a difference?
 

drew.j.harrison

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

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Just tried adding Application.Wait now+timevalue("00:00:02") so the code waits 2 secods before executing. Still no good.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,761
Members
417,109
Latest member
996

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
Top