Inconsistent run-time error

[William Bartusek]

Why does the following block of code work when using a
macro button on a custom toolbar but not work when using a
control button on a worksheet??? I get a run-time error
code 1004, Method 'range of object_Worksheet' failed
pointing to the Sort block of code.

Private Sub NewScenario_Click()

'sort the "Worksheet_Name" range by Ascending order of
Scenario
Application.Worksheets("Lookup_Ranges").Activate
Application.Worksheets("Lookup_Ranges").Range
("Worksheet_Name").Activate

'sort Worksheet names by column 2 of "Worksheet_Name" range

Application.Worksheets("Lookup_Ranges").Range
("Worksheet_Name").Sort _
Key1:=Range("Worksheet_Name").Columns(2), _
Order1:=xlAscending, Header:=xlNo

'open "SetBaseline" userform
SetBaseline.Show
SetBaseline.Baseline.SetFocus

End Sub

 

[Dave Peterson]

Are you using xl97?

There's a bug in xl97 (fixed in xl2k) that deals with code called from controls
from the control toolbox toolbar that are used on a worksheet.

One fix is to change the .takefocusonclick property to false.

Another fix is to add:
activecell.activate
at the top of your code.

===
If you're not using xl97, maybe you could eliminate some of the
selecting/activating:

With Worksheets("lookup_ranges").Range("worksheet_name")
.Cells.Sort Key1:=.Columns(2), _
Order1:=xlAscending, Header:=xlNo
End With

Untested, but compiled.