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

 

[rollis13]

This could be what you are after for restrictions on sheet Jobs and as before with no restriction for all other sheets:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Sh.Name = "Jobs" And Intersect(Target, Range("P:R")) Is Nothing Then Exit Sub
    If Target.Count = 1 Then oldValue = Target.Value
    oldAddress = Target.Address
End Sub

 

[sspatriots]

This could be what you are after for restrictions on sheet Jobs and as before with no restriction for all other sheets:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Sh.Name = "Jobs" And Intersect(Target, Range("P:R")) Is Nothing Then Exit Sub
    If Target.Count = 1 Then oldValue = Target.Value
    oldAddress = Target.Address
End Sub

Thank you. I will try this when I get back to the office in the morning. Quick question. If I'm only doing selections to the one worksheet, would I change "workbook" to "worksheet" so the other worksheets would not be targeted?

 

[rollis13]

No, for a single sheet use event Worksheet_SelectionChange instead and move the macro to the sheet's module.

 

[sspatriots]

Good morning,

I tried the following code you suggested in a worksheet and used the Worksheet_SelectionChange, however, it still recognizes changes to other cells outside of those three columns. The code I'm trying to make work in my workbook is like the code in this link: Track Changes Automatically in Worksheet with VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("P:R")) Is Nothing Then Exit Sub
    If Target.Count = 1 Then oldValue = Target.Value
    oldAddress = Target.Address
End Sub

 

[rollis13]

With this macro in the sheet's module what you are say is can't be true. This line:

If Intersect(Target, Range("P:R")) Is Nothing Then Exit Sub

If the Target cell doesn't intersect the column range P:R then exit the sub.
Add this line for testing purposes and try again. Don't leave the macro you showed in post #1 active.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    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
End Sub

You will see the message box only when you select a cell in the target range.
But, if you are to recognizes changes you need to use event Worksheet_Change instead.

 

[sspatriots]

Thanks. I tested what you have above and got the "passed" message box to pop up. So then, I tried to take that code for tracking that I linked in my post #5 above into the same worksheet change event code. I'll show that combined code at the end of this post. I can get it to work, except it is doing two things that for some reason I can't figure out why.

1. It's not putting the new "Target.Value" in the worksheet "LogDetails" in the third column. It is basically placing the original value in both the second and third column. I'm guessing this is something simple that I'm just missing.
2. When I select a cell on the "LogDetails" worksheet (where the legacy information is being captured), my cursor randomly selects a different cell or for some cells it lets me select the actual cell. It's like randomly offsetting any cell I select and has a mind of its own.

Here is the code that I combined with what you just provided:

Dim oldValue As String
Dim oldAddress As String

Option Explicit
Private Sub Worksheet_SelectionChange(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:D").AutoFit

Application.EnableEvents = True

End If

End Sub

Thanks again for all your help on this.

 

[rollis13]

This is now a completely different problem as per the topic of this thread.
Anyway, since I have not idea on how your data is organized, till now I've only tried to guess what you are up to.
1. With Worksheet_SelectionChange you are recording in sheet LogDetails all the selections/movements you have done in sheet Jobs columns P:R. As I said at the end of my post #6, if you need to record all the changes you made, you should use event Worksheet_Change instead.
2. In my testing this issue hasn't happened yet. In your real project it probably dipends on the use of Worksheet_SelectionChange instead of Worksheet_Change.

 

[rollis13]

Follow up, the fog is clearing.
In your macro after renaming it Worksheet_Change get rid of this line:

If Target.Count = 1 Then oldValue = Target.Value

then add this other macro in the same sheet's module:

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

 

[sspatriots]

Below is what I have if I did it correctly to your instructions. The only thing now that it is doing is that it has locked in the memory of the Original Value and it puts that under the Original Value column on the "LogDetails" sheet for every cell I select regardless of what the Original Value was. It's like locked in the memory for that value after the first time it is run. All of the code below is in my "Jobs" worksheet.

Dim oldValue As String
Dim oldAddress As String

Option Explicit
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:D").AutoFit

Application.EnableEvents = True

End If

End Sub

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