Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-08-2021, 11:13 PM
Peterson Peterson is offline Change Event to change cell in same row doesn't work if user clicks other row Windows 10 Change Event to change cell in same row doesn't work if user clicks other row Office 2019
Advanced Beginner
Change Event to change cell in same row doesn't work if user clicks other row
 
Join Date: Jan 2017
Posts: 59
Peterson is on a distinguished road
Default 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
Attached Files
File Type: xlsm Change Event Not Working.xlsm (30.0 KB, 1 views)
Reply With Quote
  #2  
Old 05-09-2021, 05:59 AM
jeffreybrown jeffreybrown is offline Change Event to change cell in same row doesn't work if user clicks other row Windows 10 Change Event to change cell in same row doesn't work if user clicks other row Office 2016
Expert
 
Join Date: Apr 2016
Posts: 639
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #3  
Old 05-09-2021, 06:13 AM
jeffreybrown jeffreybrown is offline Change Event to change cell in same row doesn't work if user clicks other row Windows 10 Change Event to change cell in same row doesn't work if user clicks other row Office 2016
Expert
 
Join Date: Apr 2016
Posts: 639
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Here's another solution that might fit, but in this example, both are included. Row color change plus the "Active".
Attached Files
File Type: xlsm Change Event Not Working.xlsm (21.3 KB, 3 views)
Reply With Quote
  #4  
Old 05-10-2021, 06:08 AM
p45cal p45cal is offline Change Event to change cell in same row doesn't work if user clicks other row Windows 10 Change Event to change cell in same row doesn't work if user clicks other row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 480
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 05-10-2021, 04:59 PM
Peterson Peterson is offline Change Event to change cell in same row doesn't work if user clicks other row Windows 10 Change Event to change cell in same row doesn't work if user clicks other row Office 2019
Advanced Beginner
Change Event to change cell in same row doesn't work if user clicks other row
 
Join Date: Jan 2017
Posts: 59
Peterson is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change Event to change cell in same row doesn't work if user clicks other row 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
Change Event to change cell in same row doesn't work if user clicks other row 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft