I'm having a problem using offset with Worksheet_Change(). What I want
to accomplish is when the person finishes entering a value in the
current cell to move down one and to the left one.

Right now I have my code in the Worksheet_Change event but this makes
the active cell the next cell and then runs my code which offsets the
cell to the wrong cell.

All I want to do is make it easy for the user to enter data so that it
basically acts as a carriage return.

If you need more information let me know and I'll try to clarify
better.

Thanks,
Chris


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Chris,

If I understand your question correctly, all you need to do go to the Tools
menu, choose Options then the Edit tab, and check the "Move selection after
Enter" setting, and set the direction setting to down. You can set this
with VBA with code like

Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"cford31" wrote in message
...

I'm having a problem using offset with Worksheet_Change(). What I want
to accomplish is when the person finishes entering a value in the
current cell to move down one and to the left one.

Right now I have my code in the Worksheet_Change event but this makes
the active cell the next cell and then runs my code which offsets the
cell to the wrong cell.

All I want to do is make it easy for the user to enter data so that it
basically acts as a carriage return.

If you need more information let me know and I'll try to clarify
better.

Thanks,
Chris


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Thanks for the reply Chip,

That would solve my problem if I had only one column but I have two
columns and after they finish entering the value in the second column I
want it to move to the first cell on the next row (Down one, Left one).


Here is my code:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim pvtTable As PivotTable
Dim intColumn As String

intColumn = Target.Column
If intColumn = 2 Then
ActiveCell.Offset(1, -1).Select
End If

Set pvtTable = Worksheets("Sheet1").Range("D2").PivotTable
pvtTable.RefreshTable

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I think I solved my problem by changing ActiveCell.Offset to
Target.Offset

Thanks for the help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

select the area where you want to enter data and then use Tab instead of
enter.

--
Regards,
Tom Ogilvy

"cford31" wrote in message
...

Thanks for the reply Chip,

That would solve my problem if I had only one column but I have two
columns and after they finish entering the value in the second column I
want it to move to the first cell on the next row (Down one, Left one).


Here is my code:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim pvtTable As PivotTable
Dim intColumn As String

intColumn = Target.Column
If intColumn = 2 Then
ActiveCell.Offset(1, -1).Select
End If

Set pvtTable = Worksheets("Sheet1").Range("D2").PivotTable
pvtTable.RefreshTable

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/