excel 2010 vba send keys to simulate clicking the formula bar

Question

Google hasn't given me quite what I want, I thought maybe SO might have the answer.

Instead of using a input box or user prompt that asks the user for info that will be stored in cell, I'm looking for some code to simulate clicking in the formula bar. I am currently using sendkeys method with "F2" to allow the user to enter info into the selected cell. It would be much easier to look into the formula bar, instead of a single cell in a sea of data.

Sub CopyTemplate()

'ActiveWorkbook.Save
Worksheets("HR-Cal").Activate
Dim rng As Range
Dim trng As Range
Dim tco As String
'Use the InputBox select row to insert copied cells
Set rng = Application.InputBox("select row to paste into", "Insert template location", Default:=ActiveCell.Address, Type:=8)
If rep = vbCancel Then
End If

startrow = rng.Row
'  MsgBox "row =" & startrow
Range("AG2") = startrow

Application.ScreenUpdating = False

'copy template block
Range("C6").End(xlDown).Select
Range("AG1") = ActiveCell.Row

tco = "A5:AN" & Range("AG1")
Range(tco).Select
Selection.Copy

Range("A" & Range("AG2")).Activate
Selection.Insert Shift:=xlDown

Range("c100000").End(xlUp).Select
Selection.End(xlUp).Select
'select first value

Range("AG1:AG2").ClearContents

Application.ScreenUpdating = True

SendKeys "{F2}"
SendKeys "{BS}"

End Sub

when the codes runs this is what the user sees (col 2 col 2621)

Answer 1

I don't believe there is a single keypress to "activate" the formula bar. There is probably a way to do with multiple keypress events like <alt><tab><tab>...~nine years later and a couple of other keys~...<tab><tab>

The quicker and more direct way would be to turn off the "EditDirectlyInCell" setting:

Application.EditDirectlyInCell = False

This will bring the cursor to the formula bar when you sendkey F2

You could just turn this thing off on Workbook_Open() in the workbook's code:

Private Sub Workbook_Open()
    Application.EditDirectlyInCell = False
End Sub

Perhaps on Workbook_BeforeClose() you could toggle that setting back on so you don't change their defaults:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EditDirectlyInCell = True
End Sub

Answer 2

maybe this will help, i set the Application.EditDirectlyInCell to false, before entering cell editing, then call DoEvents before returning EditDirectlyInCell to true

Sub EditRange(ByVal Target As Range)          'so any change in target would not affect
                                              'the caller's variable

    Set Target = Target.Areas(1).Resize(1, 1) 'to make sure the target is single cell

    Target.Worksheet.Activate                 'to make sure the worksheet is active

    Target.Activate                           'activate the designated cell

    Application.EditDirectlyInCell = False    'turn cell editing to false, any attempt _
                                               to edit cell will be done in formula bar

    SendKeys "{F2}"                           'send F2 to start editing the cell

    DoEvents                                  'make sure every command is fully executed

    Application.EditDirectlyInCell = True     'return in cell editing to default value
End Sub