Excel VBA - Application.SendKeys Keys:="{F2}" does not work from Form button subroutine

This code works fine when called from a macro shortcut key.  

Sub EditActiveCell()
Application.SendKeys Keys:="{F2}"
End Sub

Open in new window

However, it won't work when called from a subroutine that originates with a UserForm control click.  
Also, if I make the routine that is called from the control click as simple as this:

Sub UserFormControlClickAction()
End Sub

then the control click subroutine leaves the Selection (a single cell) "inert";  I have to click on it in order to enter into it, format it etc.

In the attached test file, it correctly sets the F2 on the first click of the control button, but not on any others.  If I click the "Show Form" button again, it will then correctly set the F2 again.  However, I haven't reproduced these effects in my real spreadsheet.

Any help with getting the F2 setting to work consistently from the UserForm control click would be appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Sub EditActiveCell()
Dim ws As Worksheet
Dim r1 As Range
Set ws = ThisWorkbook.Worksheets(1)
Set r1 = ws.Range(ws.Cells(5, 1), ws.Cells(5, 1))
Application.SendKeys Keys:="{F2}"
End Sub
codequestAuthor Commented:
@Martin:  thanks for the input.  I tried your suggestion to add the "ShowForm" instruction.  It actually stopped the F2 action even on the initial use of the Do Routine button (which was allowed without that instruction in place).   So no joy.
Martin LissOlder than dirtCommented:
OK, sorry, you're right. I saw that it would repeatedly select the cell so I thought that was sufficient. Please describe a scenario where you would want to click the button more than once. In other words (without my additional ShowForm) what might be something you'd do after the cell got the focus and before you clicked the button again.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

codequestAuthor Commented:
I'm working on an spreadsheet in which the first column is an outline.  The UserForm, which is very compact, contains several controls that provide shortcuts for row insert (1 or 3), font size, indent, cell fill color and so on.   With the UserForm controls I can bang away on the outline with many fewer keystrokes or mouse clicks.

The intended use of the F2 function is to augment the insert, so that the first column of the newly inserted row is set to edit mode, and I can start typing immediately after pressing the insert button.  It's a little thing, but the current way it works is frustrating because the new row cell is selected, but it's inert.  

So the form stays there permanently, and I might do several inserts, formats etc without reloading the form.  

As I mentioned, in my "real" spreadsheet, I can't get the F2 to work even on the first click of the form control button.  I reduced the situation to the test case to make it as simple as possible for exploration.

I added a "wait":  didn't help.

Application.SendKeys Keys:="{F2}", Wait:=True

Open in new window

Martin LissOlder than dirtCommented:
I'm not sure I understand. When I remove my additional ShowForm line, the cell isn't "inert"; I can do anything I want to it. I'm using Excel 2010. You?

I also found that if I move the ShowForm line to the Worksheet_SelectionChange event or Worksheet_Change event that to my eye there's no problem.
codequestAuthor Commented:
I'm using Excel 2013.    "Inert" is not helpful, sorry.  Yes the cell can be right-clicked on and formatted, and then clicked on and edited.  I notice though that I can't "F2" manually into it, without first re-selecting it.  And, again, if I run the subroutine  from a macro shortcut key (which regretably I did not include with the test, but which can be easily added, say CTRL-SHFT-F, the SendKeys F2 function of the subroutine works as intended.  So it is as if the UserForm is not giving control or activation back to the worksheet, or something like that.
Martin LissOlder than dirtCommented:
Did you try my suggestion in my previous post? If that doesn't help then I don't think I can help.

I have found in the past that unless you update the sheet from the userform (and stay in the userform until you're done) that it doesn't work.
codequestAuthor Commented:
Sorry, missed that last suggestion, kind of rushed.  I'll try it a little later.  Thanks for the attention to this!
codequestAuthor Commented:
Tried that last suggestion, it didn't change the behavior

Adding this to the start of the UserForm control click function did the trick:

Appactivate Thisworkbook.Name

Credit to:  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
codequestAuthor Commented:
This was the answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.