Workbook_SheetSelectionChange event

[sspatriots]

Can anyone tell me how to make the subject type of event only apply to 3 columns on a worksheet? I just need to target columns P through R on one worksheet called "Jobs".

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Count = 1 Then
oldValue = Target.Value
End If

oldAddress = Target.Address
End Sub

Thanks, SS

 

[sspatriots]

I think I found the issue with my last problem.

I changed:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    oldValue = Target
End Sub

To:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    oldValue = Target.Value
End Sub

Thank you so much for all your help. I think it is working as it should now. Have a great weekend.

 

[rollis13]

Yes, switching to Target.Value is useful most of the time since it depends on the contents of the target cell.
Thanks for the positive feedback, glad having been of some help.

 

[sspatriots]

I guess I spoke to soon. I went into my "LogDetails" worksheet to follow up on what is changing in columns P through R and noticed that the original value was picking up the value in column B as well from the "Jobs" worksheet. Not sure what is happening, but I do think this code is definitely on the right track to what I'm after and maybe just needs to be tweaked. Sorry for jumping the gun.

Below is a screenshot of the "LogDetails" worksheet. The only entry on there that is correct is the top one where I changed a date. The entries would either be a date or a Payment Status like "Paid", "Partial PMT", "Invoiced", "HOLD", "NA, etc.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sSheetName As String
'Dim temparr(1 To 1, 1 To 3) As Variant
  
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("P:R")) Is Nothing Then Exit Sub
'    MsgBox "Passed!"                              '<- added for testing
'    If Target.Count = 1 Then oldValue = Target.Value
    oldAddress = Target.Address

sSheetName = "Jobs"

If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now

Sheets("LogDetails").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", _
SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress

Sheets("LogDetails").Columns("A:E").AutoFit

Application.EnableEvents = True

End If

End Sub

 

[sspatriots]

Just looked closer at this. Apparently there is other code that I use to update a UserForm for that same "Jobs" worksheet that is causing this behavior when that form is updated to pass the changes from that form back to the worksheet and it is picking up that information from the column B of the "Jobs" worksheet instead of P through R. Looking to see if I can pin it down now.

 

[rollis13]

Yes, and as you already noted looking closer, I can't be of much help unless I see the whole project. Till now I've been guessing ... a possible scenario.

 

[sspatriots]

Ok, I think I have corrected the problem for now, or at least it seems to have rectified the situation. I went into the code that was triggering the problem and added the following at the beginning of that code:

Application.EnableEvents = False

Then I added the following to the beginning of that code:

Application.EnableEvents = True

 

[rollis13]

As you say there is some other macro that triggers the WorkSheet_Change event without triggering the WorkSheet_SelectionChange event which updates the variabile oldValue. But this shouldn't be a problem since in the WorkSheet_Change macro there is a filter with If Intersect(Target, Range("P:R")) Is Nothing Then Exit Sub. Well if you do some debugging with key F8 you will see the cursor jump from one macro to the other even when not required and you do avoid this with Application.EnableEvents = False/True.

 

[sspatriots]

Well, when I turn off the lines of code that I added and F8 my way through the code that triggered the event, it jumps to the event code at this line:

                tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value = .cboJobStatus.Text

The value that was being placed into the "Original Value" column is coming from the line just before that one.

Sub Update_Job_Status_PM_Form()


   Dim wb As Workbook
   Dim ws As Worksheet   'Added SPS,06/16/22
   Dim tb As ListObject
   Dim frm As Object    'UserForm
   Dim job_name As String
   Dim i As Long
   
   Set wb = ThisWorkbook
   
   Set ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on
   
   Set tb = ws.ListObjects("G2JobList")
   
   Set frm = Job_Status_PM

'Application.EnableEvents = False
  
   With frm
   
      job_name = Trim(.txtJobName.Text)
      
            For i = 1 To tb.DataBodyRange.Rows.Count
      
                If tb.ListColumns("Job Name").DataBodyRange.Cells(i).Value = job_name Then
               
                tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value = .cboJobStatus.Text
                tb.ListColumns("G1" & Chr(10) & "PM").DataBodyRange.Cells(i).Value = .cboG1PM.Text
                tb.ListColumns("G2" & Chr(10) & "PM").DataBodyRange.Cells(i).Value = .cboG2PM.Text
                tb.ListColumns("Customer").DataBodyRange.Cells(i).Value = .txtCustomer.Text
                tb.ListColumns("Group 1 Status").DataBodyRange.Cells(i).Value = .txtGroup1Status.Text
                tb.ListColumns("Group 1 EST Completion Date").DataBodyRange.Cells(i).Value = .txtGR1ESTCOMPLDate.Text
                tb.ListColumns("Group 1 Ship Date to Jobsite").DataBodyRange.Cells(i).Value = .txtGroup1ShipDate.Text

                tb.ListColumns("Cab" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboCabVendor.Text
                tb.ListColumns("Cab" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtCabPONo.Text
                tb.ListColumns("Cab" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtCabPODate.Text
                tb.ListColumns("Cab" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtCabREQDDate.Text
                'tb.ListColumns("Cab" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtCabRCVDDate.Text
                tb.ListColumns("Cab" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboCabSHPGARR_ITEMLCTN.Text

                tb.ListColumns("ENT" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboENTVendor.Text
                tb.ListColumns("ENT" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtENTPONo.Text
                tb.ListColumns("ENT" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtENTPODate.Text
                tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtENTREQDDate.Text
                'tb.ListColumns("ENT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtENTRCVDDate.Text
                tb.ListColumns("ENT" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboENTSHPGARR_ITEMLCTN.Text

                tb.ListColumns("FXTR" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboFXTRVendor.Text
                tb.ListColumns("FXTR" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtFXTRPONo.Text
                tb.ListColumns("FXTR" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtFXTRPODate.Text
                tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtFXTRREQDDate.Text
                'tb.ListColumns("FXTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtFXTRRCVDDate.Text
                tb.ListColumns("FXTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboFXTRSHPGARR_ITEMLCTN.Text

                tb.ListColumns("CONTR" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboCONTRVendor.Text
                tb.ListColumns("CONTR" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtCONTRPONo.Text
                tb.ListColumns("CONTR" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtCONTRPODate.Text
                tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtCONTRREQDDate.Text
                'tb.ListColumns("CONTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtCONTRRCVDDate.Text
                tb.ListColumns("CONTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboCONTRSHPGARR_ITEMLCTN.Text

                tb.ListColumns("Door EQPT" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboDoorEQPTVendor.Text
                tb.ListColumns("Door EQPT" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtDoorEQPTPONo.Text
                tb.ListColumns("Door EQPT" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtDoorEQPTPODate.Text
                tb.ListColumns("Door EQPT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtDoorEQPTREQDDate.Text
                'tb.ListColumns("Door EQPT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtDoorEQPTRCVDDate.Text
                tb.ListColumns("Door EQPT" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboDoorEQPTSHPGARR_ITEMLCTN.Text

                tb.ListColumns("Wiring" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboWiringVendor.Text
                tb.ListColumns("Wiring" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtWiringPONo.Text
                tb.ListColumns("Wiring" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtWiringPODate.Text
                tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtWiringREQDDate.Text
                'tb.ListColumns("Wiring" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtWiringRCVDDate.Text
                tb.ListColumns("Wiring" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboWiringSHPGARR_ITEMLCTN.Text

                tb.ListColumns("Jack" & Chr(10) & "Vendor").DataBodyRange.Cells(i).Value = .cboJackVendor.Text
                tb.ListColumns("Jack" & Chr(10) & "PO").DataBodyRange.Cells(i).Value = .txtJackPONo.Text
                tb.ListColumns("Jack" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtJackPODate.Text
                tb.ListColumns("Jack" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = .txtJackREQDDate.Text
                'tb.ListColumns("Jack" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value = .txtJackRCVDDate.Text
                tb.ListColumns("Jack" & Chr(10) & "SHPG ARR/" & Chr(10) & "ITEM LCTN").DataBodyRange.Cells(i).Value = .cboJackSHPGARR_ITEMLCTN.Text

                tb.ListColumns("Weekly Status Summary").DataBodyRange.Cells(i).Value = .txtWeeklyStatusSummary.Text
    
                tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value = .txtLastUpdateDate.Text
        
            
                '            Last Update Date for Job Status
                tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value = .txtLastUpdateDate.Text
                
                
                '            Customer Details, Last Updated Date
                 Range("G2JobList[[#Headers],[Payment" & Chr(10) & "Before" & Chr(10) & "Shipping]]").Offset(-1, 0) = .txtPMTLastUpdateDate
                
                
                '            Customer Details
                tb.ListColumns("Job Type").DataBodyRange.Cells(i).Value = .cboJobType.Text
                tb.ListColumns("G1" & Chr(10) & "PM").DataBodyRange.Cells(i).Value = .txtG1PM.Text
                tb.ListColumns("Customer PO").DataBodyRange.Cells(i).Value = .txtCUSTPONo.Text
                tb.ListColumns("Customer" & Chr(10) & "PO Date").DataBodyRange.Cells(i).Value = .txtCUSTPODate.Text
                tb.ListColumns("Customer Contact").DataBodyRange.Cells(i).Value = .txtCustomerContact.Text
                tb.ListColumns("Customer Phone").DataBodyRange.Cells(i).Value = .txtCustomerPhoneNo.Text
                tb.ListColumns("Customer E-mail").DataBodyRange.Cells(i).Value = .txtCustomerEmail.Text
                
                
                '            Customer Details, Payment History
                tb.ListColumns("Down" & Chr(10) & "Payment").DataBodyRange.Cells(i).Value = .cboDNPMT.Text
                tb.ListColumns("Payment" & Chr(10) & "with" & Chr(10) & "Approval").DataBodyRange.Cells(i).Value = .cboPMTWithAPPVL.Text
                tb.ListColumns("Payment" & Chr(10) & "Before" & Chr(10) & "Shipping").DataBodyRange.Cells(i).Value = .cboPMTB4SHPG.Text
                
                
                '            Customer Details, Shipping Addresses
                tb.ListColumns("SITE Address").DataBodyRange.Cells(i).Value = .txtJobSiteSHPGADDR.Text
                tb.ListColumns("CUST Address").DataBodyRange.Cells(i).Value = .txtCUSTSHPGADDR.Text
                tb.ListColumns("STOF Address").DataBodyRange.Cells(i).Value = .txtSTOFSHPGADDR.Text
                
                
                '            Last Customer Contact Date and Customer Dialog
                tb.ListColumns("Last CUST" & Chr(10) & "Contact Date").DataBodyRange.Cells(i).Value = .txtLastContactDate.Text
                tb.ListColumns("Customer Dialog").DataBodyRange.Cells(i).Value = .txtCUSTDialog.Text
            
        
                Exit For
         
            End If
      
      Next
   
   CFJobStatusPMUF
   
   End With

'Application.EnableEvents = True

End Sub

 

[rollis13]

Well, since it's the only line that has reference on sheet "Jobs" I would wrap only that one with Application..EnableEvents = False/True instead of the whole macro. Maybe you have other events that need triggering in the other sheets. Anyway, if you do some other debugging and you don't notice issues you can leave it as it is in your post #18.

Ps. Sorry, I got it wrong, all references are to Table Names and not to other Sheets so it's right to wrap the whole macro.