Selected Answer
Hello Kyle,
The following code is put to work in the attached workbook. Note that it is in XLSM format. When you transfer the code to your own workbook make sure that you install it in the code sheet of the worksheet on which you want the action. It won't work if installed elsewhere. In the attached workbook you will find it in the codesheet of Sheet1.
Option Explicit
Private Enum Nws ' Worksheet navigation
' 04 Sep 2018
NwsSourceColumn = 5 ' 5 = column E (change as required)
NwsDisplayColumn = 10 ' 10 = column J (column O would be 15)
End Enum
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 04 Sep 2018
Const TableName As String = "MyTable"
Dim Rng As Range
Set Rng = ActiveSheet.ListObjects(TableName).DataBodyRange
With Target
If Not Application.Intersect(.Cells(1), Rng) Is Nothing Then
' ===============================
' the following 3 lines delete previous displays
Set Rng = Range(Cells(Rng.Row, NwsDisplayColumn), _
Cells(Rng.Row + Rng.Rows.Count - 1, NwsDisplayColumn))
Rng.ClearContents
' ===============================
Cells(.Row, NwsDisplayColumn).Value = Cells(.Row, NwsSourceColumn).Value
End If
End With
End Sub
Before it can work in your own workbook some adjustments will have to be made.
- Set the appropriate value for the enumeration NwsSourceColumn.
In my example it is column E which Excel identifies by its number 5.
- Do the same for enumeration NwsDisplayColumn at the top of the code. In my example it is column J (=10). Change the value to 15 to specify column O.
- Look for the Const TableName and assign the actual name of the table in your worksheet to this string.
- Finally, I decided that you only want to display one value at a time, the one in the row just clicked. Therefore my code deletes all others. If you don't want that action remove the 3 lines of code I marked, or you might just place an apostrophe at the beginning of each of these 3 lines which would mark it as comment and prevent it from executing.
I have awarded points for your question because it is clear and precise. But it is also a prime candidate for follow-ups from which I ask you to refrain. For onlookers, this is a clean question with a clear answer. Let's not spoil the image. If you (or they) have a related question, post the code in a new thread and ask for the modification you want. Here are some candidates.
First, there is only limited sense in using code to display a hidden value if that value is calculated or retrieved from available data. The task should be carried out by the code and the column not required.
Second, I was tempted to format the display cell with a frame around it and a yellow background and give that same background highlight to the selected row to establish a visual connection. I abstained because if this were done the display cell need not be in the same row. It could be in a permanent location that doesn't need to be deleted when the display changes but might be moved up and down to be on the same page as the click..
The point is that the code provides an infrastructure which identfies the clicked cell (which gives easy access to all the data in that row) and it identifies a range to display a result in. For both ranges, already identified and waiting, it is simple to apply whatever formatting you might wish for without significant extra effort.
