We help IT Professionals succeed at work.
Get Started

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

178 Views
Last Modified: 2017-11-17
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
Comment
Watch Question
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE