Excel VBA suddenly throws errors everywhere.

I fixed a workbook to do a little more and made some other tweaks. Worked great. We save it to Google Drive. I download from Google drive and run it...starts throwing errors willy-nilly. Like Range("G2").Calculate. Like Range("A:A").Select.

Now, even the old version of the workbook throws weird errors of the same type.

Anybody know what could cause this?

I thought perhaps I disabled events somehow, so I ran code to enable. Then I thought maybe I enabled and shouldn't have, so ran code to disable.

If nothing else, how can I get back to square one?
LVL 22
Anne TroyEast Coast ManagerAsked:
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
i am not sure if i understood the question.
NorieAnalyst Assistant Commented:
What sort of errors are you getting?

How are you running/triggering the code?
Anne TroyEast Coast ManagerAuthor Commented:
What sort of errors are you getting?

Hi, Norie!

Error:  Code execution has been interrupted.

(Most recently, the line "End Sub" was highlighted yellow.)

Yes. I am running procedures from buttons on my workbook. We've been using the workbook for years. So, while I changed one of the procedures, I didn't change most of the others.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Anne TroyEast Coast ManagerAuthor Commented:
Here's one of my codes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Not Intersect(Target, Range("E2:J10000, T2:T50")) Is Nothing Then
  If Target.Value <> "x" Then Target.Value = "x" Else Target.Value = ""
End If
End Sub

Open in new window


I got an error on the first cell I clicked, hit End, and didn't get an error on the 2nd cell.
Anne TroyEast Coast ManagerAuthor Commented:
When I step through the following code, it keeps running a function it doesn't need to run.

Sub AddMeatTemp()

Dim lastrow As Long
Dim rng As Range
Dim ws As Worksheet
Dim Arng As Range, Erng As Range
Set rng = Range("AddMeatTemp")
Set ws = Sheets("CreateChoiceSets")
Application.ScreenUpdating = False
rng.Copy Destination:=ws.Range("M" & Rows.Count).End(xlUp).Offset(1)
    
    lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
    rng.Columns(1).Copy Destination:=ws.Range("A" & lastrow)
    
    lastrow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).Row + 1
    rng.Columns(2).Copy Destination:=ws.Range("E" & lastrow)
    
    lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set Arng = ws.Range("A2:A" & lastrow)
    Arng.RemoveDuplicates Columns:=1, Header:=xlNo
    lastrow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).Row
        Set Erng = ws.Range("E2:E" & lastrow)
    Erng.RemoveDuplicates Columns:=1, Header:=xlNo
    Application.ScreenUpdating = True
    Range("A:A").Select
    Cells.Find(What:="Temp:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Range("A1").Select
End Sub

Open in new window


The following is the function it runs, and I don't even think I'm using this function anymore.

Function User()
Application.Volatile
User = Application.UserName
End Function

Open in new window

Jan Karel PieterseExcel and VBA ExpertCommented:
Very likely this function is called from a cell and the code you are stepping through causes a recalc of the workbook.
Anne TroyEast Coast ManagerAuthor Commented:
OK. So what do I need to fix? Even when I disable this function, I continue to get errors.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Not Intersect(Target, Range("E2:J10000, T2:T50")) Is Nothing Then
  If Target.Value <> "x" Then Target.Value = "x" Else Target.Value = ""
End If
End Sub

Open in new window


Just got code execution on "End If", above.
Jan Karel PieterseExcel and VBA ExpertCommented:
Sounds as if the VBAProject is corrupted.

Save-as the xlsm file to xlsx format, close, re-open both xlsx and xlsm (DISABLE macro's on the second). Then use my copyVBAProject tool to copy the VBA project from xlsm to xlsx. Finally, save-as xlsx to new xlsm file.

www.jkp-ads.com/download.asp#copyvbaproject

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
Anne TroyEast Coast ManagerAuthor Commented:
Doing it.
Anne TroyEast Coast ManagerAuthor Commented:
Arggghhh. Thanks for your help, JK. I can email the file to you, but I really suspect it's my machine.
Jan Karel PieterseExcel and VBA ExpertCommented:
Does it help to start Excel in safe mode by holding down the ctrl key? Also, disable all com add-ins.
FarWestCommented:
if you did not run windows /office update for a long time (before 9th March) then please run it and check if this error remains
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 Excel

From novice to tech pro — start learning today.