ActiveCell change then vaule in another cell change

user_15342655 0
Viewed 45

Please help me with my macro below it doesn't work correctly when I changed value in activecell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
   
    
    Dim KeyCells As Range
    
        kolumna = ActiveCell.Column
        wiersz = ActiveCell.Row
        komorka = Cells(wiersz, kolumna).Address
        Set KeyCells = Range(komorka)
       
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

        Target.Offset(0, 1) = Target * 12
       
End If
Application.EnableEvents = True
End Sub

but when I such code as below it works but it works when I change value in cell: $D$3

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    
    Dim KeyCells As Range
    
        Set KeyCells = Range("$D$3")
       
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Target.Offset(0, 1) = Target * 12
       
End If
Application.EnableEvents = True

End Sub

so the problem with my code is here:

komorka = Cells(wiersz, kolumna).Address

Thanks a lot for your help

vba excel
1 Answers

Move After Return

Application.MoveAfterReturn

  • It will not work if MoveAfterReturn is set to True, because when you change a value the cursor moves to the next cell which becomes the ActiveCell so they never intersect (Correction: it will work anyway if you're in column A and use the Left arrow to confirm the entry or in A1 use the Up arrow, etc.).
  • It could work if you set MoveAfterReturn to False, so I wrote the procedure to toggle it. Add a Button or a CommandButton and use it to enable or disable it when necessary.
  • Note that you have to confirm the cell entry with Enter. The arrows will not do.

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.MoveAfterReturn Then
        If Not Application.Intersect(ActiveCell, Target) Is Nothing Then
            Dim cValue As Variant: cValue = Target.Value
            If IsNumeric(cValue) Then
                Target.Offset(0, 1).Value = CDbl(cValue) * 12
            End If
        End If
    End If
End Sub

Standard Module e.g. Module1

Option Explicit

Sub toggleMAR()
    With Application
        If .MoveAfterReturn Then
            .MoveAfterReturn = False
        Else
            .MoveAfterReturn = True
        End If
    End With
End Sub
user_98140690
Related