SendKeys not working from userform

Hi all,

I'm using SendKeys("{DOWN}") to make Excel select the next visible cell on a spreadsheet. When I run the code from a custom menu button, it works fine. However, ff I run the code by pressing a command button on a userform, the active cell does not move down one cell. I have tried specifying the object in a number of ways, including WINDOWS("Filename.xls").application.sendkeys etc, but the command never reaches the cell unless the code is run from a menu button.

Can anyone tell me why this is and how I tell Excel to send the DOWN keystroke to the userform?

Alternatively, can anyone tell me an easy way to get the active cell to jump down to the next visible row? The sub first sets an autofilter, then selects the header cell, then tries to select the next visible cell down. Header row will always be, say, row 1, but the next visible row may be 2, or 10, or 17 etc, depending on the autofilter setting.

Any help appreciated!

I'm not an expert but i've found that when running code form a userform you have to be really specific about what you want it to do.

In the userform code are you telling excel which sheet you are working in?
Most commands have to start with Sheets("Whatever") and then the command.

I.E cells(1,1).select won't work but sheets(1).cells(1,1).select will

Hope that helps
Schwizer

Thanks for the response. I agree about having to be explicit when working with forms and I am sure that this is a similar case. I just can't figure out how to get Excel to send the keystroke to the correct object.

I've tried Sheets("sheet1").Application.SendKeys ("{DOWN}"), True and that doesn't work. Sheets().sendkeys also won't work as sendkeys is not a method in the sheets object.

Any other ideas out there?

Hello Alfaholic,

One problem is the UserForm is by default modal. This locks Excel out to responding to inputs. In the UserForm properties page, set ShowModal to False. Then try the code...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks for the response Leith. The code sets the UserForm to false when it calls it, but I changed the default ShowModal setting to false as well, and then changed the sendkeys command to start with "application". I still get no joy, so any other suggestions are welcome.

Does anyone have a similar arrangement working?

Hi all,

Just for reference, and also in case I forget how I did this in the future and search this forum for an answer again, this problem can be solved by hiding the userform before the SendKeys functions, and then showing the form again afterwards if need be. The keys then get sent to the active spreadsheet window. Also, specifying "application.sendkeys" instead of just "sendkeys" seems to throw it off.

Cheers
me