Go back to starting point

[PeterW]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[PeterW]

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