[RESOLVED] VBA form losing focus(activewindow) when minimizing Excel

[langej]

I know my first problem is i'm using VBA. However, with limited resources, this is what I need to use.

What i have is a Excel spreadsheet being populated with a VBA form. When i load the form, i'm minimizing the spreadsheet. Due to minimizing the spreadsheet the form loses focus(opens up behind any other opened window) until i click on either the form or Excel's minimized application located in the windows taskbar.

Is there a way to regain focus on the form? In VBA you cannot "hide" the spreadsheet, i have to minimize it. Thanks in advance.

Joel
Systems Analyst

[RobDog888]

Welcome to the Forums.

Moved from Classic VB forum.

[RobDog888]

Use Me.Setfocus right after your code to minimize Excel. Are you minimizing just the workbook or are you minimizing the Excel application itself?

[langej]

Thanks for responding so quickly. I am minimizing the application so the user does not see or have access to the spreadsheet. I am minimizing the application through a module

Sub OpenDailyOpsForm()

Application.WindowState = xlMinimized

frmDailyOps.Show

End Sub

and I'm initializing the form through the form's code. It seems Windows handles the minimization of the application, making any other opened program the activewindow. Therefore, I have to either click on the minimized Excel in the taskbar or click on the form itself.

Any suggestions?

[RobDog888]

Try...
Sub OpenDailyOpsForm()

Application.WindowState = xlMinimized

frmDailyOps.Show
frmDailyOps.SetFocus

End Sub

[langej]

I've tried the me.setfocus in the module but that errors out since it's in a module and not in the form's code. I've also tried it in the form's initialize sub but it does not "activate the form". I've tried the frmDailyOps.setfocus in both the form's code and the module's code (after minimizing the application) and it does the same thing. I can email you the excel file, it's only 66kb in size, so you can see what I mean.

[Hack]

I can email you the excel file, it's only 66kb in size, so you can see what I mean.Or, you could just attach it to a thread post. and let anyone that wants to help out have a look. :)

[langej]

I now have a generic form of the program ready for posting. Just attached it.

[RobDog888]

Create a new post and before you submit it click on the Manage Attachments button new the bottom of the page.

[langej]

Now I attached it. The password for the spreadsheet commandbutton is left blank.

[RobDog888]

Here is the problem, your displaying the userform Modally. this is preventing any code after the .Show from running. If you show it modeless by setting the ShowModally property to false then it works ok.

[langej]

I just got it to work with:


Sub OpenDailyOpsForm()

'Application.WindowState = xlMinimized
Application.Workbooks.Parent.Visible = False
frmDailyOps.Show

End Sub



and to show the spreadsheet I used visible=true. Thanks guys for all your input. I just hope this solution doesn't cause problems down the road.

What I find interesting in this solution is when you type in this line, after Parent, the .visible = false doen't show in the quickpics option window.

[RobDog888]

Yes, the minimize was causing the issue to.
Glad its resolved now. :)

[langej]

Yah, I tried the modal property and it did work but it showed the spreadsheet too and that is what I didn't want. Thanks for looking at this issue.
Joel