CODEWITHSUNDEEP
excelvba
ADRSAL
ADRSAL

Reputation: 33

Calling a macro from another

I running a macro that pops up a message if the user inputs a value in column E having column D empty. therefore the user has to input value in D and then in E. once the user inputs a value in D, by Vlookup formula the sheet will display a number in column F. The second macro should then check if value of column F is not equal to value input in column E, if not equal popup a message. First part is working but not the second. any idea please. thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Cells(Target.Row, 5).Address And Target.Value <> "" And Cells(Target.Row, 4).Value = "" Then

       MsgBox "Input value in column D"
       Cells(Target.Row, 4).Select
       Target.Clear        
    End If        
Call Macro2

End Sub

Sub Macro2()

   If Target.Address = Sheets(1).Cells(Target.Row, 5).Address And Target.Value <> "" And Target.Value <> Sheets(1).Cells(Target.Row, 6).Value Then
    MsgBox "E and F don't match"

   End If

End Sub

Upvotes: 1

Views: 53

Answers (1)

Vityata
Vityata

Reputation: 43565

If the second one is the problem, then pass the Target to it:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False                    '<--- Consider removing this line
    If Target.Address = Cells(Target.Row, 5).Address _
       And Target.Value <> "" _
       And Cells(Target.Row, 4).Value = "" Then

        MsgBox "Input value in column D"
        Cells(Target.Row, 4).Select
        Target.Clear    
    End If    

    Macro2 Target        
    Application.EnableEvents = True                      '<--- Consider removing this line
End Sub

Sub Macro2(Target As Range)

    If IsError(Target) Then
        MsgBox Target.Address & "is an error!"
    ElseIf IsError(Sheets(1).Cells(Target.Row, 6)) Then
        MsgBox Sheets(1).Cells(Target.Row, 6).Address & " is an error!"
    ElseIf Target.Address = Sheets(1).Cells(Target.Row, 5).Address _
           And Target.Value <> "" _
           And Target.Value <> Sheets(1).Cells(Target.Row, 6).Value Then
        MsgBox "E and F don't match"
    End If

End Sub

However, it could be that Target.Clear is making a loop within the Worksheet_Change, because it changes the worksheet once again. Depending on whether this is ok or not ok, you may consider writing Application.EnableEvents = False and Application.EnableEvents = True at the start or at the end of the Sub.

Upvotes: 1

Related Questions