Go back to starting point
 

Please help ... this is driving me nuts as I'm sure it is very simple

After running the following code, I would like the activecell to be the
cell where it first started before the code fired. At the moment, after
running the code I end up in the last cell in range
"modCashOutInputs".

Thanks in advance
Peter


Public Sub Worksheet_Change(ByVal Target As Range)

Dim CellCheckValidation As Range
Dim CurrentRow As Integer
Dim CurrentColumn As Integer

CurrentRow = Target.Row
CurrentColumn = Target.Column

'check to see if any of the Cash Outflow inputs has changed and if so
update the calculations

If Not Intersect(Target, Range("modCashOutInputs")) Is Nothing Then

Set CellCheckValidation = Cells(CurrentRow, 4)

If Not Intersect(CellCheckValidation, Cells.SpecialCells
xlCellTypeAllValidation)) Is Nothing Then

Range("modCurrentRowNumber").Value = CurrentRow
procUpdateCashFlowFormulaActiveRow
End If

End If

Application.ScreenUpdating = True

End Sub


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=571632

Go back to starting point
 

Public Sub Worksheet_Change(ByVal Target As Range)

Dim CellCheckValidation As Range
Dim CurrentRow As Integer
Dim CurrentColumn As Integer

On Error goto ErrHandler
Application.EnableEvents = False
CurrentRow = Target.Row
CurrentColumn = Target.Column

'check to see if any of the Cash Outflow inputs has changed and if so
update the calculations

If Not Intersect(Target, Range("modCashOutInputs")) Is Nothing Then

Set CellCheckValidation = Cells(CurrentRow, 4)

If Not Intersect(CellCheckValidation, Cells.SpecialCells
xlCellTypeAllValidation)) Is Nothing Then

Range("modCurrentRowNumber").Value = CurrentRow
procUpdateCashFlowFormulaActiveRow
End If

End If

ErrHandler:
Target.Select
Application.ScreenUpdating = True
Application.EnableEvent = True
End Sub

--
Regards,
Tom Ogilvy

"PeterW" wrote in
message ...

Please help ... this is driving me nuts as I'm sure it is very simple

After running the following code, I would like the activecell to be the
cell where it first started before the code fired. At the moment, after
running the code I end up in the last cell in range
"modCashOutInputs".

Thanks in advance
Peter


Public Sub Worksheet_Change(ByVal Target As Range)

Dim CellCheckValidation As Range
Dim CurrentRow As Integer
Dim CurrentColumn As Integer

CurrentRow = Target.Row
CurrentColumn = Target.Column

'check to see if any of the Cash Outflow inputs has changed and if so
update the calculations

If Not Intersect(Target, Range("modCashOutInputs")) Is Nothing Then

Set CellCheckValidation = Cells(CurrentRow, 4)

If Not Intersect(CellCheckValidation, Cells.SpecialCells
xlCellTypeAllValidation)) Is Nothing Then

Range("modCurrentRowNumber").Value = CurrentRow
procUpdateCashFlowFormulaActiveRow
End If

End If

Application.ScreenUpdating = True

End Sub


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=571632

Go back to starting point
 

Thanks for your help Tom. That works well, however if anyone else i
going to use the code, please note a small typo in Tom's suggeste
code. Under "ErrHandler" it should read "Application.EnableEvents
True" not "Application.EnableEvent = True

--
Peter
-----------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...nfo&userid=649
View this thread: http://www.excelforum.com/showthread.php?threadid=57163