VBA: Run-time 424 when calling macro at form close

[mrMozambique]

Greetings, all. When I run the following macro from anywhere in my workbook, it works fine. However, when I call it on the Terminate Even of a userform, it gives me an "object-required" 424 error. I have a suspicion it's because it doesn't know how to grab the "sites" worksheet while it's closing the userform, but I'm not sure. Can anyone give me some guidance?

Sub sortsites2()
'
' sortsites2 Macro
'

'
    Sheets("sites").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Worksheets("sites").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("sites").Sort.SortFields.Add Key:=Range("E2:E2627") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("sites").Sort.SortFields.Add Key:=Range("F2:F2627") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("sites").Sort.SortFields.Add Key:=Range("H2:H2627") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sites").Sort
        .SetRange Range("E1:R2627")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Facilities").Select
End Sub

 

[Andrew Poulsom]

Which line is causing the error? Try removing the lines that contain Select.

 

[Norie]

Why not use the ActiveWorkbook reference for that last line as you have for all the other worksheets?

Mind you this might work too:

Application.Goto ThisWorkbook.Worksheets("Sites").Range("A1"), True

 

[mrMozambique]

Which line is causing the error? Try removing the lines that contain Select.

Unfortunately, it doesn't tell me which line is causing the error because it only shows the RunMacro line from the form's Terminate Event. When I run the macro normally (i.e. not from the Terminate Event), it works fine. There's something about closing the form and running the macro that's causing the problem. Thanks for your help.

 

[Ruddles]

If you're still struggling: if you place a breakpoint at the top of your code, you can step through it and see which line triggers the error.

 

[Norie]

Try going to Tools>Options and select Break on All Errors on the General tab.

If there's a problem with the some other code in the form.

By the way all that use of Select/Selection might be something to do with the problem.

 

[mrMozambique]

I seem to have gotten it to work by simply placing the code from the macro directly into the Terminate event rather than calling the macro. Strange...

Thanks for the help, guys.