codequest
asked on
Excel VBA - Application.SendKeys Keys:="{F2}" does not work from Form button subroutine
This code works fine when called from a macro shortcut key.
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
Sub EditActiveCell()
Selection.Activate
Application.SendKeys Keys:="{F2}"
End SubHowever, 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
ASKER
@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.
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.
ASKER
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.
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
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.
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.
ASKER
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.
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.
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.
ASKER
Sorry, missed that last suggestion, kind of rushed. I'll try it a little later. Thanks for the attention to this!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was the answer
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