Change Event to change cell in same row doesn't work if user clicks other row

Peterson · #1 05-08-2021, 11:13 PM

I have a Change Event macro that, for a given row in a defined table, adds the word "Active" to a cell in Column V when a user changes text in Columns A, B, or C. If the user clicks out of Column A-C after entering data and they click on a different row elsewhere in the table, then "Active" is entered in that clicked row.

How can I make sure that if the user enters data in a row, "Active" is entered in the same row, regardless of where the user clicks? Here's the code, also attached:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ' 05/08/2021
 ' Run if the target is in Columns A-C and is in Table1 (use Cols A-C, as
' any of these might be the first cell filled out for a new hearing).
' Add "Active" to the Job Status column:
    
    Dim rngActiveCell As Range
    Dim lngLastRow As Long
    Dim lngTblColNum As Long
    
    If Target.Column < 4 Then
        Set rngActiveCell = ActiveCell
        lngLastRow = ActiveCell.Row
        'If the active cell is in Table1:
        If Not Intersect(ActiveCell, [Table1]) Is Nothing Then
            Range("V" & lngLastRow).Value = "Active"
        End If
    End If
End Sub

jeffreybrown · #2 05-09-2021, 05:59 AM

Give this a try. Instead of Worksheet Change, you need to use Worksheet SelectionChange. Also, added a line to remove all previous values and a line to find LastRow of table.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngActiveCell   As Range
    Dim lngLastRow      As Long
    Dim lngTblColNum    As Long
    Dim LastRowTable    As Long
    If Target.Column < 4 Then
        Set rngActiveCell = ActiveCell
        lngLastRow = ActiveCell.Row
        If Not Intersect(ActiveCell, [Table1]) Is Nothing Then
            LastRowTable = ActiveSheet.ListObjects("Table1").DataBodyRange.Rows.Count + 2
            Range("V3:V" & LastRowTable).Value = ""
            Range("V" & lngLastRow).Value = "Active"
        End If
    End If
End Sub

jeffreybrown · #3 05-09-2021, 06:13 AM

Here's another solution that might fit, but in this example, both are included. Row color change plus the "Active".

p45cal · #4 05-10-2021, 06:08 AM

Surely this will do:?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)    ' 05/08/2021
If Target.Column < 4 Then
  If Not Intersect(Target, [Table3]) Is Nothing Then Range("V" & Target.Row).Value = "Active"
End If
End Sub

Peterson · #5 05-10-2021, 04:59 PM

Thank you, Jeffrey, for responding to my post. I wasn't able to get the first code to work, and the second one, while very slick, isn't quite what I'm looking for; my apologies if I didn't clearly state the issue I was having.

p45cal, your code is working -- thank you for your response, as well.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Change active cell after running macro in active cell via selection change	Peterson	Excel Programming	3	03-07-2021 12:19 PM
Finally, a reliable and seemingly robust DropDownList CC Change Event	gmaxey	Word VBA	0	09-08-2019 08:10 AM
Calendar view showing body or event details - how do you change?	smf1234	Outlook	0	07-12-2019 01:46 PM
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow	FUGMAN	Excel Programming	7	02-05-2017 08:37 AM
Macro to change user name	grantworth	Word	3	01-13-2017 02:21 PM