Press F2 with vba

M

Minitman

Greetings,

Is there anyway to press F2 inside of a macro with vba???

I can get my code to work if I manually press F2 after I hit enter.
But when I try to record it to see how vba interprets my keystrokes,
all it shows is this:

Range("AN5:AY5").Select
ActiveCell.FormulaR1C1 = "9/19/1981"
Range("BL5:BW5").Select
ActiveCell.FormulaR1C1 = ""

I hit Enter then F2. I did this twice in the sample above and this is
what gets recorded. In the first cell was a date and the second cell
was empty. I don't need to be copying or pasting over what is already
in the cell, I just need the ability to do so if needed.

Anyone have any ideas as to how to do this with vba?

Any help is always appreciated.

-Minitman
 
R

Rick Rothstein \(MVP - VB\)

Why don't you tell us the ultimate functionality you are trying to have
happen. VBA does not need to have any keys pressed, or to select anything,
or to change the active cell's location, etc. in order to do things. So,
tell us what you have, what you need to specify and what you want to end up
with afterwards and someone here will see if they can give you code to do
that.

Rick
 
J

Joel

SendKeys Statement Example
This example uses the Shell function to run the Calculator application
included with Microsoft Windows. It uses the SendKeys statement to send
keystrokes to add some numbers, and then quit the Calculator. (To see the
example, paste it into a procedure, then run the procedure. Because
AppActivate changes the focus to the Calculator application, you can't single
step through the code.). On the Macintosh, use a Macintosh application that
accepts keyboard input instead of the Windows Calculator.

Dim ReturnValue, I
ReturnValue = Shell("CALC.EXE", 1) ' Run Calculator.
AppActivate ReturnValue ' Activate the Calculator.
For I = 1 To 100 ' Set up counting loop.
SendKeys I & "{+}", True ' Send keystrokes to Calculator
Next I ' to add each value of I.
SendKeys "=", True ' Get grand total.
SendKeys "%{F4}", True ' Send ALT+F4 to close Calculator.
 
M

Minitman

Hey Rick,

Thanks for the reply.

Ultimately, I am trying to tab though a form on a sheet with
non-contiguous entry cells.

I am close using Worksheet_Change event with this code (this is only
the first two entry cells):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.MergeArea.Activate
If Target.MergeArea.Address = _
Range("pfCell_6").MergeArea.Address Then _
Range("pfCell_25").MergeArea.Select
If Target.MergeArea.Address = _
Range("pfCell_25").MergeArea.Address Then _
Range("pfCell_24").MergeArea.Select
....
End Sub

I can get this to work if I manually hit F2 after I hit ENTER. This
puts the cursor at the next entry cell, but not in it. To enter the
cell, I hit F2 and immediately hit ENTER. This will trigger the
Worksheet_Change event and send the cursor to the next cell in the
list, which is what I am looking for.

I thought maybe SendKeys might do the job. I looked it up in MS Help
and got the syntax, but no real direction as to where to place it!!!

Any one have an idea how to do this?

Any help is appreciated.

-Minitman
 
M

Minitman

Hey Joel,

Thanks for the reply.

I just tried SendKeys and could not get it to work consistently. Plus
the code was pasting contents of last cell - not what I need.

Here is what I tried (the first two cells only for this example):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.MergeArea.Activate
SendKeys "{F2}", True
If Target.MergeArea.Address = _
Range("pfCell_6").MergeArea.Address Then _
Range("pfCell_25").MergeArea.Select
If Target.MergeArea.Address = _
Range("pfCell_25").MergeArea.Address Then _
Range("pfCell_24").MergeArea.Select
....
End Sub

Any help to get this to work would be appreciated.

-Minitman
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top