troubleshooting Question

How to insert VBA code into Excel

Avatar of Steve_Brady
Steve_BradyFlag for United States of America asked on
Microsoft ExcelMicrosoft Office
3 Comments1 Solution588 ViewsLast Modified:

Quite some time ago, an EEEEEE* wrote the following VBA code for me which has been great and functioned just as I wanted it to.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Intersect(Target, Range("O:O")) Is Nothing Then
        rowsel = Target.Row
        Range("O1").Value = Range("K" & rowsel).Value
    End If
End Sub

Open in new window

Recently, I attempted to insert the code into another workbook where I desired the same functionality, but apparently I did something wrong in the process. Here is the before:

"before"and here is the after:

"after"Question: What are the correct steps for copy/pasting this VBA code from one Excel (2013) workbook into another?

After copying the code from the functioning workbook, I inserted it into the second workbook by right-clicking "ThisWorkbook" then selecting "Insert." However, at that point , I did not know which option to choose. In this case the options included:

    • UserForm
    • Module
    • Class Module

but obviously, as (under) stated by the knight of antiquity in Indiana Jones and the last Crusade, "I chose poorly."

"He chose poorly."Fortunately the flesh has not rotted off my face but I have been beset by a non-functioning code.

It is not uncommon that I Google some topic or post a question on EE and the best resulting solution ends up being something using VBA. However, almost never is the code followed by directions explaining exactly how it should be inserted. Consequently, I usually have to just guess as I did in this case.

Question: Are there any basic clues or guidelines that someone like myself — who has no programming background and does not know VBA — can use to at least correctly determine how/where to insert code when needed?


PS  I also cannot figure out how to remove the errantly inserted "Class Module" (if indeed it needs to be removed).

* EEEEEE:   "Extraordinarily Excellent Experts-Exchange Excel Expert" (to raise the bar a notch on ssaqibh — see here)
Peter Kwan
Analyst Programmer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros