|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change Event to change cell in same row doesn't work if user clicks other row
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Here's another solution that might fit, but in this example, both are included. Row color change plus the "Active".
|
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
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 |