VBA code to run a VBA code when I press a cell.

[KlausW]

Hi I have a challenge, I have this VBA code to run a VBA code when I press a cell.
It doesn't really work as intended, it should be like that when I click on a cell in column J, the value from column A is displayed in cell J1. Example I press J6 and the date written in A6 should appear in J1.
Any help will be appreciated
Best Regards
Klaus W

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("j2:j32")) Is Nothing Then
Range("j1").Value = Range("a2:a32").Value
End If
Call Mail
End Sub

 

[Joe4]

Change this line:

Range("j1").Value = Range("a2:a32").Value

to this:

Range("J1").Value = Target.Offset(0, -9).Value

 

[KlausW]

Change this line:

Range("j1").Value = Range("a2:a32").Value

to this:

Range("J1").Value = Target.Offset(0, -9).Value

Hi Joye4 thanks allot it works have a nice day Best Regards from Denmark KW

 

[Joe4]

You are welcome!
Glad I was able to help!

In case you are not familiar with these type of Event Procedures here is a brief explanation.
The "Target" range (which is one of the parameters of the function) is the cell that was selected that caused this code to run.
So it is the cell you selected in column J.

Then in order to get the value from column A of that same row, we need to look 9 columns to the left (to move from column J to A).
That is where the .Offset(0 , -9) comes in.

 

[KlausW]

You are welcome!
Glad I was able to help!

In case you are not familiar with these type of Event Procedures here is a brief explanation.
The "Target" range (which is one of the parameters of the function) is the cell that was selected that caused this code to run.
So it is the cell you selected in column J.

Then in order to get the value from column A of that same row, we need to look 9 columns to the left (to move from column J to A).
That is where the .Offset(0 , -9) comes in.

Thanks again, Can I ask one thing? If I only want Private Sub Worksheet_SelectionChange to work in column J what do I do?
KW

 

[Joe4]

Thanks again, Can I ask one thing? If I only want Private Sub Worksheet_SelectionChange to work in column J what do I do?
KW

This line already does that:

If Not Intersect(Target, Range("j2:j32")) Is Nothing Then

Note that "Worksheet_SelectionChange" will ALWAYS fire (as long as events are enabled) when any cell is selected, regardless of where it is.
You cannot stop that from happening, unless you turn off events (in which case none of this code will work).
So what you do is check at the very beginning to see if the selection is in your desired range (in this case J2:J32).
If it is not, nothing contained in that IF...THEN block will run.

Note: Depending on your wants/needs, you may need to move the "Call Email" inside the IF...THEN block.
Since it is currently outside of it, that step will always run anytime any cell is selected.

 

[KlausW]

This line already does that:

If Not Intersect(Target, Range("j2:j32")) Is Nothing Then

Note that "Worksheet_SelectionChange" will ALWAYS fire (as long as events are enabled) when any cell is selected, regardless of where it is.
You cannot stop that from happening, unless you turn off events (in which case none of this code will work).
So what you do is check at the very beginning to see if the selection is in your desired range (in this case J2:J32).
If it is not, nothing contained in that IF...THEN block will run.

Note: Depending on your wants/needs, you may need to move the "Call Email" inside the IF...THEN block.
Since it is currently outside of it, that step will always run anytime any cell is selected.

Thanks again KW

 

[Joe4]

You are welcome.
I hope that all makes sense!

 

[KlausW]

God morning every body

Is it possible to make this run, only when I press a cell in column J.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Any help will be appreciated

Best regards and have a nice weekend

Klaus W

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Sheets("Hofkalender").Range("$j$2:$j$41")) Is Nothing Then

Range("$J$1").Value = Target.Offset(0, -9).Value

End If

MsgBox "Mail"

End Sub

 

[KlausW]

I found this solution on the net.
Good weekend
KW

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection.Count = 1 Then
    If Not Intersect(Target, Range("$j$2:$j$41")) Is Nothing Then
        
        Range("$J$1").Value = Target.Offset(0, -9).Value
        MsgBox "Mail"
            'Call
        End If
    End If

End Sub