Trigger cell value change when other cells no blank VBA

Vincent88

Board Regular
Joined
Mar 5, 2021
Messages
172
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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

Board Regular
Joined
Mar 5, 2021
Messages
172
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Try this:
VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
According to your explanation On Error is a non sense :​
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
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).
VBA Code:
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
 

Forum statistics

Threads
1,136,790
Messages
5,677,745
Members
419,718
Latest member
ALWP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top