Search code examples
excelvbasendkeysformula-editor

excel 2010 vba send keys to simulate clicking the formula bar

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)enter image description here

Solution

  • 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