Google Answers Logo
View Question
Q: Microsoft Excel - Visual Basics language behind the keys "F2" and "Enter" ( Answered 4 out of 5 stars,   0 Comments )
Subject: Microsoft Excel - Visual Basics language behind the keys "F2" and "Enter"
Category: Computers > Programming
Asked by: shlodge-ga
List Price: $20.00
Posted: 13 Jul 2006 18:39 PDT
Expires: 12 Aug 2006 18:39 PDT
Question ID: 746123
Visual Basics macro to automate Pressing of the F2 Key

  I am looking for the programming behind the key "F2" & "Enter" in Microsoft
Excel. I am not interested in how to use the "sendkeys" function. (it
doesn't work on my pc)
  Currently I need to go to excel, in each of the cells and press "F2"
and "Enter" for excel to recognise my data. I want to know what is the
coding behind the key "F2" as well as "Enter" and how I can use visual basics to
run(automate) this function. 
I hope this is clear. Please e-mail me for any clarification
Subject: Re: Microsoft Excel - Visual Basics language behind the keys "F2" and "Enter"
Answered By: maniac-ga on 13 Jul 2006 20:03 PDT
Rated:4 out of 5 stars
Hello Shlodge,

The "programming" behind the F2 and Enter keys is pretty straight forward.

F2 maps to

and the Enter key after such an action maps to

I determined this information by performing the following steps:

[1] Use the menu 
  Tools -> Macro -> Record New Macro
and then selected OK to proceed to recording. At this point on my
machine, a toolbar appears which includes the button needed to stop

[2] Press F2 and Enter in order. That records those two steps into the macro.

[3] Stop recording by selecting the square button icon on the toolbar
or using the menu
  Tools -> Macro -> Stop Recording

At this point, you can then use the menu
  Tools -> Macro -> Macros...
and select your macro (by default would be named something like
Macro1) and then Edit to bring up Visual Basic with the code displayed
in the window. On my system, the complete macro function was as

Sub Macro3()
' Macro3 Macro
' Macro recorded 7/13/2006 by Maniac

End Sub

As you can see above, there are only two actions recorded, that refer
to the two steps (F2 and Enter) respectively.

Now, this macro only does this step for a single cell. To do so for
all the cells of the worksheet, you need something like the following:

Sub Macro3()
' Macro3 Macro
' Macro recorded 7/13/2006 by Maniac

    lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    For myRow = 1 To lastRow
        For myColumn = 1 To lastColumn
            ActiveSheet.Cells(myRow, myColumn).Cut
            ActiveSheet.Cells(myRow, myColumn).Select
        Next myColumn
    Next myRow
End Sub

which performs the key steps (cut / paste) in sequence for each
"active" cell of the worksheet.

However, I have a follow up question that if you answer, may lead to a
"better" solution.
  What symptom do you see that requires the cut / paste actions to correct?
If you can answer this question, please make a request for
clarification with the answer and I would be glad to follow up with
another solution to your problems.

I am guessing you are having problems with data imported by Excel (I
had similar problems on my own worksheets) but if that is the cause, I
may be able to help you load the data in a slightly different way to
fix your original problem.

If you have any problems understanding the answer or if you consider
the answer incomplete, please make a clarification request and I would
be glad to expand on the answer as needed.

Good luck with your work.

Request for Answer Clarification by shlodge-ga on 13 Jul 2006 21:06 PDT
Hi Maniac,
Yes, you are correct I am having problem with data import.

The best way I can explain it is like this.
Select a cell (can be any). Use "Format" -  "Cells", and change it to "text"
Type in "31/10/06" or in your case it may be "10/31/06"

After you enter the data, go to "Format" - "Cells", and change it to
"Date" or "General". Maybe use the format as "DD-MMM-YY"

You will notice that the cell would not have changed, the cell will
still display "31/10/06", as a text.

However, if you go into the cell, and press "F2", and then "Enter",
you will have noticed that Excel now recgonises the date and changes
it to "31-Oct-06".

I have tried to do "Edit", "Paste Special, Values" and it didn't work.
It just stays as the text "31/10/06"

When I use the VB code, it is the same as the Paste Special method above
ActiveCell = ActiveCell.Value

When I use the following VB Code

Range("A1") = Range("A1").Value 

it actually interprets the other way round (For example, "7/10/06" is
interpreted as "Oct 7 2006", but when I use "F2" and "Enter" on the
same cell, it will return "7th July 2006").
Please note that the VB code and the "F2" and "Enter" are done at the
same time. I don't know why the VB region setting is different to my
excel region setting. If there is a way to change it, please let me

My problem is that this is how I get the data and I can't change it.

Please note that my region setting has been set to read "dd-mmm-yy". 

Thank you very much for your help.

Request for Answer Clarification by shlodge-ga on 14 Jul 2006 01:54 PDT
Not to worry, I finally figured it out after many hours of tearing my
hair out. I just use text to columns

    Application.CutCopyMode = False 
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _ 
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True

Thanks for your help.

Clarification of Answer by maniac-ga on 14 Jul 2006 13:33 PDT
Hello Shlodge,

I am glad you have solved your problem. Let me comment on some of the
symptoms you refer to and perhaps explain the causes of those

[1] Cell value as "text" is not automatically converted to a date

Microsoft Excel stores dates as an integer value. See
for a nice explanation of the date / time format (as well as a
compatibility "bug" with Lotus 1-2-3).

The value you entered is a string (not an integer). An alternative for
converting the value would be to use code like
  Selection.Value = CDate(Selection.Value)
after you convert the cell type to General or a date type. I did a
quick check without converting the cell type (was text) and the test
macro I used changed the format of the date, but left it as a text

[2] Behavior of Paste Special -> Value menu

Again, the value is a string and not a number, so the conversion won't
happen. You can also show this behavior by entering the following
value into a date formatted cell
(note a leading single quote and no trailing quote)
This kind of entry forces a string value into the cell and generally
ignores the formatting settings. If you compare two cells (one with a
date value, the other with a text value), one will be right aligned
and the other will be left aligned - providing another clue of the
type of data stored in the cell.

[3] The "wrong" interpretation of the value in .Value assignment

I reviewed the VB Help files and did a search for
  locale definition
for an explanation that on my system indicates in part that "locale
aware" functions should obey your system locale settings, but VB
itself is fixed as an US-English locale. You may be getting this
latter behavior in the specific assignment of
  Range("A1") = Range("A1").Value
you provided as an example. Odd - a further review of the VB help file
didn't indicate if CDate was locale aware or not.

Again, good luck with your work.
shlodge-ga rated this answer:4 out of 5 stars

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy