Onclick 2 events

[Steve Jackson]

I want to record a macro and then assign that macro to the onclick event of
a button. In the macro I have tried to open a different worksheet and sort
data in that worksheet. When I copy the text from the macro to the onclick
event of the button and click on the button I get the following error:

Runtime error 1004
Select Method of range class failed

It seems to failed after selecting the worksheet. Any help will be
appreciated.

Steve

What I have for the whole event is the following:

Private Sub CommandButton1_Click()
Sheets("Tables").Select
Range("B2:J6").Select
Selection.Sort Key1:=Range("J3"), Order1:=xlDescending,
Key2:=Range("I3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("B8:J12").Select
Selection.Sort Key1:=Range("J9"), Order1:=xlDescending,
Key2:=Range("I9") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("B14:J18").Select
Selection.Sort Key1:=Range("J15"), Order1:=xlDescending, Key2:=Range( _
"I15"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("B20:J24").Select
Selection.Sort Key1:=Range("J21"), Order1:=xlDescending, Key2:=Range( _
"I21"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("L2:T6").Select
Selection.Sort Key1:=Range("T3"), Order1:=xlDescending,
Key2:=Range("S3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("L8:T12").Select
Selection.Sort Key1:=Range("T9"), Order1:=xlDescending,
Key2:=Range("S9") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("L14:T18").Select
Selection.Sort Key1:=Range("T15"), Order1:=xlDescending, Key2:=Range( _
"S15"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("L20:T24").Select
Selection.Sort Key1:=Range("T21"), Order1:=xlDescending, Key2:=Range( _
"S21"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

 

[Ron de Bruin]

Enter the name of the macro in the Click event instead of the code

Like this

Call yourmacroname

 

[Dick Kusleika]

Private Sub CommandButton1_Click()
Sheets("Tables").Select
Range("B2:J6").Select
Selection.Sort Key1:=Range("J3"), Order1:=xlDescending,
Key2:=Range("I3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

Steve:

There are two problems. First, you are trying to select a range, B2:J6,
that's not on the active sheet. The difference between this code and the
code you copied from is location. When you're in a class module, like the
module where you code events for sheets, the default object is the sheet
whose class module you're in, not the active sheet. If you're in a standard
module, the active sheet is the default object. You need to prefix your
Ranges with the sheet reference. See also the "Default Object" section on
this page

http://www.dailydoseofexcel.com/archives/2004/04/28/beginning-vba-navigating-the-object-model/

Second, you don't really need to select the range before you sort it. You
could, for instance, say

With Sheets("Table").Range("B2:J6")
.Sort Key1:= etc...
End With

For more information on selecting and activating, see

http://www.dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/