Go back to starting point

  • Thread starter Thread starter PeterW
  • Start date Start date
P

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
 
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
 
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
 
Back
Top