Trigger cell value change when other cells no blank VBA

[Vincent88]

I use below code to show the cell value as sheetname in column C (example : C2) when the cells in both column A and B in the same row is nonblank but it does not trigger to change when both cells (for example : A2 and B2) are filled until I click Cell C and enter.
How to modify the code to have the cell C to show the sheetname automatically once both cells A and B are filled with data.

CODE
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 3 Or Application.CountA(Cells(Target.Row, 1).Resize(, 2)) < 2 Then Exit Sub
Application.EnableEvents = False
Target.Value = ActiveSheet.name
Target.NumberFormat = "d mmm yyyy"
Target.HorizontalAlignment = xlRight
Application.EnableEvents = True

End Sub

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column > 3 Or Target.CountLarge > 1 Then Exit Sub
    
    If Cells(Target.Row, "A") <> "" And Cells(Target.Row, "B") <> "" Then
        Application.EnableEvents = False
        Cells(Target.Row, "C") = ActiveSheet.Name
        Cells(Target.Row, "C").NumberFormat = "d mmm yyyy"
        Cells(Target.Row, "C").HorizontalAlignment = xlRight
        Application.EnableEvents = True
    End If

End Sub

 

[Vincent88]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column > 3 Or Target.CountLarge > 1 Then Exit Sub
   
    If Cells(Target.Row, "A") <> "" And Cells(Target.Row, "B") <> "" Then
        Application.EnableEvents = False
        Cells(Target.Row, "C") = ActiveSheet.Name
        Cells(Target.Row, "C").NumberFormat = "d mmm yyyy"
        Cells(Target.Row, "C").HorizontalAlignment = xlRight
        Application.EnableEvents = True
    End If

End Sub

Hi Joe4,
This works but what scripts should be added to make Cell C returns to blank when either cell A or B is no input. I tried to add on error resume next and on error goto 0 but in vain.

 

[Marc L]

According to your explanation On Error is a non sense :​

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column < 3 And .Row > 1 Then
Application.EnableEvents = False
Cells(.Row, 3).Value2 = IIf(Application.CountA([A:B].Rows(.Row)) = 2, Name, Empty)
Application.EnableEvents = True
End If
End With
End Sub

 

[Joe4]

Hi Joe4,
This works but what scripts should be added to make Cell C returns to blank when either cell A or B is no input. I tried to add on error resume next and on error goto 0 but in vain.

You can add a simple "Else" clause to my original code (and we probably want to move the "Application.Events..." statements).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column > 3 Or Target.CountLarge > 1 Then Exit Sub
    
    Application.EnableEvents = False
    If Cells(Target.Row, "A") <> "" And Cells(Target.Row, "B") <> "" Then
        Cells(Target.Row, "C") = ActiveSheet.Name
        Cells(Target.Row, "C").NumberFormat = "d mmm yyyy"
        Cells(Target.Row, "C").HorizontalAlignment = xlRight
    Else
        Cells(Target.Row, "C").ClearContents
    End If
    Application.EnableEvents = True

End Sub