VBA command for edit cell mode
 

I am using Windows XP with Office 2003.

1. I need a VBA method to enter edit mode within a cell. As though the user
double clicked in a cell or pressed F2. Or must I use send keys?

2. Is there a way to capture the user's previous selection? For example,
user makes a selection one, then makes selection two. I need the range
address for selection one.

Could someone please post example code for these questions?

Thanks much in advance.

VBA command for edit cell mode
 

quartz,

You don't really need to enter edit mode. You can use the inputbox method
to get the user's input. For example, to get the user to change the value
in cell A1:

Range("A1").Value = Application.InputBox("Edit A1", , Range("A1").Value)

To capture the user's last selection, you need to use 2 global range
variables, and the worksheet selection change event. In a standard module,
declare these at the top:

Public OldRange As Range
Public CurRange As Range

Then in the worksheet change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set OldRange = CurRange
Set CurRange = Target
If OldRange Is Nothing Then Exit Sub
MsgBox "The last selection was " & OldRange.Address
End Sub

Of course, OldRange will be available to you anywhere in your code.

HTH,
Bernie
MS Excel MVP


"quartz" wrote in message
...
I am using Windows XP with Office 2003.

1. I need a VBA method to enter edit mode within a cell. As though the
user
double clicked in a cell or pressed F2. Or must I use send keys?

2. Is there a way to capture the user's previous selection? For example,
user makes a selection one, then makes selection two. I need the range
address for selection one.

Could someone please post example code for these questions?

Thanks much in advance.

VBA command for edit cell mode
 

Bernie,

Thanks for your help. Just two things:

Actually, I do need an Edit method because I have code attached to a
double-click event in the sheet and I want to give the user the choice to
edit the cell as they normally would...I guess I can use:
Application.SendKeys "{F2}"

Thanks much for the method to capture the user's previous selection. Do you
find that using "Worksheet_SelectionChange" reduces response time in large
spreadsheets? I have never used this event as it seems to me that it could
affect performance?...

Again thanks.

"Bernie Deitrick" wrote:

quartz,

You don't really need to enter edit mode. You can use the inputbox method
to get the user's input. For example, to get the user to change the value
in cell A1:

Range("A1").Value = Application.InputBox("Edit A1", , Range("A1").Value)

To capture the user's last selection, you need to use 2 global range
variables, and the worksheet selection change event. In a standard module,
declare these at the top:

Public OldRange As Range
Public CurRange As Range

Then in the worksheet change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set OldRange = CurRange
Set CurRange = Target
If OldRange Is Nothing Then Exit Sub
MsgBox "The last selection was " & OldRange.Address
End Sub

Of course, OldRange will be available to you anywhere in your code.

HTH,
Bernie
MS Excel MVP


"quartz" wrote in message
...
I am using Windows XP with Office 2003.

1. I need a VBA method to enter edit mode within a cell. As though the
user
double clicked in a cell or pressed F2. Or must I use send keys?

2. Is there a way to capture the user's previous selection? For example,
user makes a selection one, then makes selection two. I need the range
address for selection one.

Could someone please post example code for these questions?

Thanks much in advance.