CODEWITHSUNDEEP
excelvba
Mady Taylor
Mady Taylor

Reputation: 1

Run macro automatically when the active cell is changed

How do I run my macro automatically when the active cell is changed?

The code works when I go to the developer tab and select the macro and run it.

Sub didcellschange()
    Dim KeyCells As String
    KeyCells = "e14"
    If Application.Intersect(activecell, Range("e14")) Is Nothing Then breakbarcode

End Sub


Sub breakbarcode()
    'this macro breaks the barcode from the labels. it also compares the expiration date and P/N
    Application.DisplayAlerts = False
    ActiveSheet.Unprotect
    activecell.Select
    Selection.TextToColumns destination:=activecell.Offset(0, 0), DataType:=xlDelimited, 
    consecutivedelimiter:=True, semicolon:=True
    If (UCase(activecell.Offset(0, 0)) <> UCase(activecell.Offset(0, -1))) Then MsgBox "Wrong Component"
    If (UCase(activecell.Offset(0, 2).Value - 31 < prepdate)) Then MsgBox "Attention: This material is 
    either expired or will expire soon. Please requalify or order new material."
    activecell.Offset(0, 4).Select
    ActiveSheet.Protect

End Sub

Upvotes: 0

Views: 188

Answers (1)

DerinNehir
DerinNehir

Reputation: 56

' In your Worksheet code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("E14")) Is Nothing Then 
        Application.EnableEvents = False
        breakbarcode
    Application.EnableEvents = True
    End If
End Sub

Upvotes: 1

Related Questions