asked on

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?

Professor J

i am not sure if i understood the question.

Norie

What sort of errors are you getting?

How are you running/triggering the code?

Anne Troy

ASKER

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.

Anne Troy

ASKER

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 Troy

ASKER

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 Pieterse

Very likely this function is called from a cell and the code you are stepping through causes a recalc of the workbook.

Anne Troy

ASKER

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.

ASKER CERTIFIED SOLUTION

Jan Karel Pieterse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anne Troy

ASKER

Doing it.

Anne Troy