Set Focus To Application After Hiding UserForm

atay · Sep 21st 2007

I created UserForm with one TextBox and "Close" Button. And it's works like a real-time search with my criteria. He is using AutoFilter to show what he found. And when I'm pressing Close Button the Focus isn't on Application, but on Hidden window (know because when I'm clicking alt+space form's menu shows).

To show window I'm using (running from hot-key):

Code

DoCmd.OpenForm "UserForm1"

to hide

Code

Private Sub CloseButton_Click()
UserForm1.Hide
End Sub

How to resolve focus on actual Worksheet ?

Dave Hawley · Sep 21st 2007

Re: Setting Focus On Application After Userform.hide

Code

ActiveCell.Activate

Or for a paticular cell

Code

Application.GoTo Sheet1.Range("A1")

Or, to toggle focus between Excel GUI and your UserForm, set ShowModal Property of the UserForm to False

atay · Sep 21st 2007

Re: Setting Focus On Application After Userform.hide

doesn't work.
The secound formula is only moving active cell, but it can't resolve focus to the application. Whole apllication lose focus and it looks like Excel is in background (and the other window before is active), but I hide this window (userform.hide) and it still have focus.

Dave Hawley · Sep 21st 2007

Re: Setting Focus On Application After Userform.hide

Works fine and the second code (not formula) doesn't move the active cell.

atay · Sep 21st 2007

Re: Setting Focus On Application After Userform.hide

realy don't work

how do I have ?
I have hotkey button (CTRL+G) that running

Code

Public Sub PokazOkno()
UserForm1.Show
UserForm1.TextBox1.SetFocus
End Sub

even in form when i'm clicking X (windows close button) focus is stealed. I don't have any function assigned to "close window". I think .hide function don't kill window, just hiding it, but not with success (this window still exists and have focus), how to kill this window ?

p45cal · Sep 21st 2007

Re: Set Focus To Application After Hiding UserForm

I tried to replicate the problem but came a cropper when trying to use

Code

DoCmd.OpenForm "UserForm1"

A bit of investigation seems to indicate this is an Access vba command. When I replaced

Code

DoCmd.OpenForm "UserForm1"

with

Code

UserForm1.Show

there was no problem with getting focus on the spreadsheet.

Presumably then, you have an Access object which you're activating from Excel so you may need to reactivate the Excel application from your code..
p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]re:"how to kill this window ?"

perhaps

Code

Unload UserForm1

p45cal

Dave Hawley · Sep 21st 2007

Re: Setting Focus On Application After Userform.hide

Really does work

Hiding a UserForm will NOT Unload it from Memory, you need Unload Me to truly unload it.

atay · Sep 21st 2007

Re: Set Focus To Application After Hiding UserForm

Code

DoCmd.OpenForm "UserForm1"

return Runtime 424, object Required

and

Code

Unload UserForm1

works like "hide", when I'm press alt+space i have menu "move" and "close" from nowhere. When I click "close" focus is backing to application.

**Andy Pope** · Sep 21st 2007

Re: Set Focus To Application After Hiding UserForm

Can you clarify which application you are using, Excel or Access?

atay · Sep 21st 2007

Re: Set Focus To Application After Hiding UserForm

Excel 2002

**Andy Pope** · Sep 21st 2007

Re: Set Focus To Application After Hiding UserForm

How do you get DoCmd to work then? That's an Access instruction.

What happens if you activate a visible sheet?

Dave's example will work, unless Sheet1 is the hidden sheet.

[vba]Application.GoTo Sheet1.Range("A1")
[/vba]

Insomniac · Sep 21st 2007

Re: Set Focus To Application After Hiding UserForm

Its unclear here if Excel is being automated from Access or what, but in any case I believe the solution will be AppActivate.
Application.Goto will select and display a sheet and range, but the userfom will still have focus without AppActivate "Microsoft Excel".
Should work for versions 2002 up, below needs API.

Code

Sub TestSetFocus()
    
    UserForm1.Show vbModeless
    
    'Excel will select and display, but userform keeps focus
    Application.GoTo Sheet1.Range("A1")
    
    'Application gets focus with following
    AppActivate "Microsoft Excel"
    
End Sub

Display More

atay · Oct 8th 2007

Re: Set Focus To Application After Hiding UserForm

nothing of this is not working.

I don't know what I can to do

there's my file in attachment

CTRL+E (Macro) is set at this new window. Code is simple, try to enter "210" and press Enter. There is no focus on Excel 2003 (I install new version to try).

Can You help me ?

Dave Hawley · Oct 8th 2007

Re: Set Focus To Application After Hiding UserForm

You haven't set ShowModal Property of the UserForm to False, that's why.

atay · Oct 8th 2007

Re: Set Focus To Application After Hiding UserForm

sorry, I lost this fragment

I was trying / AppActivate "Microsoft Excel" /

Now it's working great, thanks

Set Focus To Application After Hiding UserForm

Participate now!

Share

Users Viewing This Thread