Excel VBA: How to duplicate double-click/cell edit action in vba code

Question

I have 3 worksheets in an Excel workbook. Sheet1, Sheet2, and Sheet3 are identical in layout. I'm using Sheet3 to evaluate if the values in Sheet1 and Sheet2 are identical.

I'm having problems when attempting to compare two identical columns; Column A in Sheet1 and Column A in Sheet2. Column A in Sheet2 is a column with a series of text values. The cells in this column have the green arrow displayed in the upper left corner of the cell.

I have a procedure that loops through the cells in Sheet1, Column A changing the cell values to text via Selection.NumberFormat = "@".

In Sheet3, I have a formula comparing the cell values in Sheet1-Column A and the cell values in Sheet2-Column A, for this comparison the formula initially evaluates to "NO". However, after double-clicking a cell in Sheet1-Column A after the procedure runs, the formula updates to "" (meaning the values are equal)

Sheet1-Column A  cell a1 value = 1234
Sheet2-Column A  cell a1 value = 1234 (Green arrow in upper left corner)
Sheet3-Column A, cell a1 value = "NO"

After double-clicking cell a1 in Sheet1-Column A, the formula in Sheet3-Column A, cell a1 evaluates to "", meaning the values are equal. The green arrow is now displayed in the upper left corner of cell a1 in Sheet1-Column A)

What I would like to do via vba code is to duplicate the double-click mouse behavior to update the display in each cell so the green arrow will be displayed in the upper left corner of each cell in Sheet1-Column A

this is wrong on so many levels. in vba use something like cStr(cell.value) or in excel formula use VALUE() — Steven Martin
Steve, thanks for the suggestion. Would you mind providing some detail on what is wrong? — Aaron

Gary's Student Gary's Student · Accepted Answer · 2015-03-15T11:53:18

You can re-assert cell data with VBA

Manually this is done with double-click followed by touching the Enter key or by using F2. If we begin by running:

Public Sub setttup()
    With Range("A1:A3")
        .Clear
        .NumberFormat = "@"
        Range("A1").Value = "=6+7"
        Range("A2").Value = "=7+11"
        Range("A3").Value = 12345
        .NumberFormat = "General"
    End With

    Range("B1").Formula = "=A1+1"
End Sub

We have this as a result:

enter image description here

We still need to "fix" cells A1 and A2

Rather than manually re-asserting the data, we can use:

Public Sub FixUp()
    With Range("A1:A3")
        .Value = .Value
    End With
End Sub

Which results in:

enter image description here

An alternative approach is to use a SendKeys loop. But this is much slower for large amounts of data.

Excel VBA: How to duplicate double-click/cell edit action in vba code

1 Answers