Shift focus from a userform to excel

[bydesign]

Hello

How can I shift focus from a visible userform to the excel sheet?

my userform is modeless and
Currently, the last statement in my procedure is...
Sheets("MySheet").Range("B93").Activate

The cell selector is put in the correct place, however the userform still has focus... therefore you must actually click the excel sheet before you can type in the selected cell.

Any ideas on how to programatically shift focus to the excel sheet?

 

[NdNoviceHlp]

try

Sheets("MySheet").Range("B93").select

.
However, the focus stays with the userform I believe until you use "hide" , "unload", or close. I've been wrong before. Dave

 

[bydesign]

Dave

I also went down that road while experimenting...

.activate or .select does not affect the focus.

I have procedures in the userform module, standard module, sheet and workbook modules. When the events in the sheet or workbook are triggered then excel maintains focus...

Thanks for the input, I appreciate your time!

 

[NdNoviceHlp]

Scott...I'm not sure why you're trying to type into a worksheet cell while you have a userform displayed? Why not just have a textbox on the userform with the control source set as the cell you want to type into while the userform is displayed? Dave

 

[bydesign]

Dave

My userform is realatively complex... I am deveoping an app that works with alot of technical data that is either developed from the input on the userform or modified on the sheet based on userform input. These features are all tied into reporting routines for electrial inspectors and material orders and material forcasting.

Basically a complex toolbar... I actually wanted to make 1 or more tool bars for this but I couldn't come up with a user friendly setup...

[/img]

 

[Tom Urtis]

Have you set the ShowModal property of the userform to false? Or is there something else maybe going on that I missed with the question.

 

[bydesign]

Tom

Yes, the "ShowModal" property is set to "false"

This works exactly as desired... i.e. I can work with the user form or the excel sheet at will...

After I run a procedures after userinput on the userform, I set the active cell the user should be working with...

This works to visually show the user where to go... but if the user wishes to type in this cell, they actually have to click on it (or the excel window itself) in order to change focus to excel...

Basically I'm looking to prgrammatically shift focus to excel.

for a userform or control it's easy... i.e. userform.setforcus , however there isn't a setfocus method for the application..

Microsoft knowledgebase says this is designed this way becasue excel is the parent object...

I'm hopeing to find a workaround...

Thanks for the input.

 

[Tom Urtis]

What version of Excel are you using? Excel97 by any chance? I cannot duplicate your problem as I understand it. I am using Excel 2002 on XP.

To use a worksheet when a userform is active, you need Excel 2000 or above, and you need to set the form's ShowModal property to False. The form will show unless you include the statement somewhere
UserForm1.Hide
and then Unhide it again when you are ready. You probably already knew that. If you are using a version after 97 then either I still don't understand the issue (always a possibility), or there is something else happening in your application.

 

[bydesign]

Tom

I am using WinXP pro and Excel XP 2003

I made a sample sheet to demonstrate... just download it
http://home.wi.rr.com/bydesign/focusdemo.xls

 

[Ivan F Moala]

Just use the command

AppActivate ("Microsoft Excel")