Return focus to worksheet from userform Excel 2007 vs 2000

[jaslake]

I have a workbook developed in Excel 2007 (compatibility mode) that contains Userform4. This form can be displayed (or not) depending in the wishes of the user. What I am trying to do is return focus to the worksheet after the UserForm is displayed.

When the worksheet is activated, I have the following code:

Please Login or Register  to view this content.

This code is also present in another general module:

Please Login or Register  to view this content.

As written above, the UserForm displays without errors with Excel 2007. However, running the same workbook in Excel 2000, it generates an error "Runtime error 438 Object doesn't support this property or method".

Now, if I change the code to this:

Please Login or Register  to view this content.

it runs fine in Excel 2000 but not in Excel 2007; In Excel 2007, I get the error message "Runtime error 5 Invalid procedure call or argument".

I also have this code to be used in the event the user has closed the form and wishes to reopen it:

Please Login or Register  to view this content.

The above code works in Excel 2007, not in Excel 2000.
Changing the code to:

Please Login or Register  to view this content.

works in Excel 2000 but not Excel 2007.

Can you tell me if this is simply a compatibility issue or am I missing something in the code that will work with both 2000 and 2007?

Thanks J

[shg]

You could test Application.Version and execute one or the other.

[jaslake]

Thanks shg, I'll try that.

J

[jaslake]

I've changed my code as follows:

Please Login or Register  to view this content.

and the workbook seems to run well under both Excel 2007 and Excel 2000 (those being the only two versions on which I have to test).

Do you (or anybody else on the forum) know if this is an anomaly with Excel 2007? For example, if this macro were to be run in Excel 2003, would it fail?

Thanks for your input shg. It fixed my immediate problem.

J

[shg]

AppActivate works in Excel 2003.

[jaslake]

I was using Excel 2003 as an example. Since my last post, I've done some research on AppActivate and there is some indication that it's "not reliable". I have no clue what that means as no examples were given.

This code works (for the moment) in Excel 2007 and Excel 2000:

Please Login or Register  to view this content.

My question (if you know) is will it work in Excel 2003, Excel 2002, Excel 97 and Excel 95. Or, is the only way of knowing is to test it on each of the versions.

Thanks....J

[Leith Ross]

Hello jaslake,

You need to use the Windows API to switch the focus. This macro will work with Excel 2000 through 2003. Earlier versions require more API code and checks. It may prove to be more of a bother to make this compatible with versions of Excel earlier than 2000. Copy this code into a Standard VBA module.

API Code to Switch Focus

Please Login or Register  to view this content.

UserForm Code
The UserForm must be displayed as modeless for the focus to switch back to Excel.

Please Login or Register  to view this content.

Worksheet Event Code

Please Login or Register  to view this content.

[jaslake]

Leith...

I've revised my code as follows:

API Code to Switch Focus...this code sits in a standard module by itself

Code:

Please Login or Register  to view this content.

UserForm4 Code

Please Login or Register  to view this content.

Worksheet Event Code

Please Login or Register  to view this content.

Standard Module to display UserForm4 Code

Please Login or Register  to view this content.

You indicated

This macro will work with Excel 2000 through 2003.

.

What I'm experiencing is that the code works fine in Excel 2007; when I run it in Excel 2000, I get the following error

Run Time error 438 Object doesn't support this property or method

at line

Please Login or Register  to view this content.

So, for some reason, Excel 2000 doesn't like it. Any thoughts as to where I'm going wrong?

Thanks J

[Leith Ross]

Hello jaslake,

You didn't cause the problem. I should have checked first that Excel 2000 supported the property. I have Excel 2000 and I assumed the property was available. Guess what? It is isn't! So, that pretty much solves the predicament about the needing the other API code for the earlier versions.

The code I need to write to get the window handle for Excel 2000 will also work with all other previous versions of Excel. However, If you are planning to use this with versions earlier than 2000, let me know. There is a special version of the function AddressOf that is needed. This function was added in Excel 2000 and later. Let me know.

[jaslake]

I appreciate your help with this issue. No, I don't plan to support versions earlier than 2000. I would, however, like the code to work on versions 2000 through 2007, if thats feasible.

Thanks J

PS: How do you KNOW it's not available in Excel 2000 (other rhan the fact it dosen't work)? Is there a reference guide?

[Leith Ross]

Hello jaslake,

I know because I checked the Object Browser in Excel 2000. Anyway, here is function that will return the window handle of Excel that the macro is running in. Copy this to a standard VBA module.

Please Login or Register  to view this content.

Example

Please Login or Register  to view this content.

[romperstomper]

Just to confirm - the hWnd property of the Application was added in XL2002. See for example, this MSKB article.

[jaslake]

Hi Leith

I've inserted your code as written into a general module and added your "Example Code" (underlined) as follows:

Please Login or Register  to view this content.

As implemented, focus is not released from the userform. Since I don't understand what the code is doing (I'm quite the amateur at VBA), it's difficult for me to discern whether I have the code in the correct place or if I added your example code in the correct place.

Any directon is appreciated.

Thanks J

[jaslake]

Bump no response

[Leith Ross]

Hello jaslake,

Sorry about the oversight. You need to also change the [b]UserForm_Activate[b] event code as shown...

Please Login or Register  to view this content.

[jaslake]

In an attempt to get the code to work, I had prviously added this code

Please Login or Register  to view this content.

to my Private Sub UserForm Activate sub as follows:

Please Login or Register  to view this content.

This code has not returned focus to the worksheet in Excel 2000. At your suggestion, I've added

Please Login or Register  to view this content.

to the code as follows:

Please Login or Register  to view this content.

and get a Syntax Error. Any ideas?

Have I placed your Example Code (from your first response) in the proper places?

Thanks J

[Leith Ross]

Hello jaslake,

This does get confusing switching back and forth. The UserForm_Activate event should be this.

Please Login or Register  to view this content.

[jaslake]

Yes, I can see I left a space between Set and Focus. To avoid any typos, I copied and pasted your code over mine. It now looks like this:

Please Login or Register  to view this content.

I now get a complie error "Sub or Function not defined". The word SetFocus is highlighted in the code.

Thanks for your help.

J

[Leith Ross]

Hello jaslake,

I better have a look at your workbook. Can you post a copy? I am using Excel 2003.

[jaslake]

I'll be happy to post a copy if you promise to not "snicker" at the code. By the way, I get the same error message running Excel 2000 and 2007.

I'll be looking forward to hearing from you.

Thanks J

[Leith Ross]

Hello jaslake,

This should do it. There were a few small mistakes. Check this out and let me know if it is right.

Edit: Deleted attachment per poster's request

[jaslake]

I will play with this more but a quick test indicates that your change works in both Excel 2000 and 2007. I did a quick compare of the code and it appears the only change you made was

Please Login or Register  to view this content.

which fixed the "Sub of Function not defined" error.

Is this the case or did I miss something?

I appreciate all your help.

Question...is it possible to delete the file attachements? If so, I'd appreciate if you would do so or explain to me how I can do so. If not then such is life.

Thanks Leith. J

[jaslake]

Leith...I've deleted the attachment to my post. If you can or will do the same for your post, it will be greatly appreciated.

J

[Leith Ross]

Hello jaslake,

I have deleted the file attachment. The other change was to the UserForm property ShowModal. This was set to False. The default is True.

[jaslake]

Thanks again Leith. Your help is greatly appreciated.

J