Move VBA code to a new Excel workbook and modify cell references

Hello,

Quite some time ago, I obtained the following two-part VBA code for Excel which I now want to include in a new workbook:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
	
End Sub

Open in new window

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
	
    If Not Intersect(Target, Range("AA:AE")) Is Nothing Then
        rowsel = Target.Row
        Range("AC1").Value = Range("P" & rowsel).Value
    End If
End Sub

Open in new window


The function of the code is as follows:

When a cell in a particular column is selected, the cell at the top of that column displays the content from a different column which resides in the same row as the selected cell.

For example, in the following screenshot, cell D7 is selected so cell D1 displays the content from cell B7 (ie from the same row as the active cell):

2017-11-15e.png
And then when a different cell in column D is selected — whether by use of the Up/Down arrows or by a mouse-click — the heading in cell D1 automatically changes to display the content in column B of the new active row. In other words, the spreadsheet does not have to recalculate for the heading to change. It simply occurs by changing which cell is active (eg D11 in this screenshot):

2017-11-15f.png
Here is a screenshot of how the code appears in the older workbook:

2017-11-15g_vba-for-Scripture-headin.png
I know I need to save the workbook as a macro-enabled (.xlsm) file but which Insert option should be used and where should each part of the code be pasted?

2017-11-16e.png
Also, which parts of the code needs to be modified if I want to change the selected cell, heading, and/or content column(s)?

Thanks
WeThotUWasAToadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
When you place the selection change event in ThisWorkbook module, it will be triggered automatically for all the sheets in the workbook when you select a cell in the target range.

If you want this functionality to work for a certain sheet, right click the Sheet Tab --> View Code --> Paste the following code into the opened code window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim rowsel As Long
If Not Intersect(Target, Range("D:D")) Is Nothing Then
    rowsel = Target.Row
    Cells(1, "D") = Cells(rowsel, "B")
End If
End Sub

Open in new window


The line If Not Intersect(Target, Range("D:D")) Is Nothing Then makes sure that the code gets executed only if you select a cell in column D and place the corresponding cell's value from column B into D1.

The line If Target.CountLarge > 1 Then Exit Sub, makes sure that when more than one cells are selected, the code will exit.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeThotUWasAToadAuthor Commented:
Thanks for the response.

I noticed two lines in your code which I don't see in the original:

If Target.CountLarge > 1 Then Exit Sub
Dim rowsel As Long

Open in new window


Can you explain their purpose? Oh, I just saw your comment. What does, "the code will exit" mean? If I select multiple cells, I'd like to still have the row one display show the top of the range I have selected.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The code will Exit means, it won't perform any action defined in the code because not one cell is selected only but more than one cells so in that case you won't like the code to perform any predefined actions after making the selection on the sheet.

You used the variable rowsel to hold the selected cell's row but didn't declare that variable. It's always a good practice to declare all the variables along with their data types used in the code. So it's always better to have "Option Explicit" on top of the Module so that the compiler will let you know if you are using a variable in the code and didn't declare it or misspelled it.
0
WeThotUWasAToadAuthor Commented:
Your code seems to work great Subodh* and thanks for the explanation.
The code will Exit means, it won't perform any action defined in the code because not one cell is selected only but more than one cells so in that case you won't like the code to perform any predefined actions after making the selection on the sheet.
Could I rewrite your first paragraph as follows?

        "The code will exit" means it won't perform any of its defined actions if you happen to select more than a single cell.

That's how I understand it but please let me know if I'm wrong.

Incidentally, out of curiosity and with your earlier explanation and the screenshot included in my OP, I was able to obtain the desired functionality from the previous form of the code by:

Right-click > ThisWorkbook > View Code > paste:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Not Intersect(Target, Range("C:E")) Is Nothing Then
        rowsel = Target.Row
        Range("D1").Value = Range("B" & rowsel).Value
    End If
End Sub

Open in new window

...and:

Right-click > Sheet1 > View Code > paste:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
End Sub

Open in new window

After doing that, the only difference I noticed is that your code becomes functional as soon as it is inserted (ie even before saving the workbook, let alone saving it as an .xlsm file).

Also, I did not notice a problem with either code when extending the selection beyond a single cell.

Thanks again,

Steve

*Sorry I don't know this but should I refer to you as Subodh or Neeraj?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.