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()
Selection.Activate
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()
ActiveCell.Select
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.

Thanks!aa_Test.xlsm
LVL 2
codequestAsked:
Who is Participating?
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))
r1.Select
Selection.Activate
Application.SendKeys Keys:="{F2}"
ShowForm
End Sub
0
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.
0
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.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

0
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.
0
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.
0
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.
0
codequestAuthor Commented:
Sorry, missed that last suggestion, kind of rushed.  I'll try it a little later.  Thanks for the attention to this!
0
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:  
http://stackoverflow.com/questions/33204431/application-sendkeys-keys-f2-does-not-work-from-form-button-subroutine/33204666#33204666
0

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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.