Excel VBA: Get range of previous cell after calling LostFocus()

Question

How can I get the range of a previous cell in Excel? I have a ComboBox and usually I can fill its value into the active cell (under the ComboBox) with ActiveCell.Value = box.Value. When I have selected a value of my ComboBox and click in any other cell, I want the value of the ComboBox to be written into the previous cell, but this code writes it to cell I clicked on:

Private Sub box_LostFocus()
  ActiveCell.Value = box.Value
End Sub

Any ideas?

What do you mean by previous cell? – rex May 27 '14 at 09:18 — rex, May 27 '14 at 09:18

score 3 · Accepted Answer · answered May 27 '14 at 09:27

If you include this in your worksheet code, PreviousCell will always be the previous range you had selected.

Dim PreviousCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' Your code that uses PreviousCell should go in the if statement that makes sure PreviousCell has a value
    If Not PreviousCell Is Nothing Then
        Debug.Print PreviousCell.Address
    End If

    Set PreviousCell = Target ' This needs to be the last line of code.
End Sub

Excel VBA: Get range of previous cell after calling LostFocus()

1 Answers1

Linked