Range class failed

[Richard]

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

 

[Dave Peterson]

What happens wrong?

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

 

[Bob Phillips]

Richard,

Exactly what problem are you having, as it seems to work fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Richard]

I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

 

[Richard]

I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

 

[Dave Peterson]

I'd start by adding:

debug.print rng.address

to see what that shows. Drop it in a couple of spots. And see where it errors
out.

 

[Richard]

I've now figured out that it's comming from the CommandButton1 when I click
it from the "HList" Sheet, Code sorts ok when I run code from vba, any
suggestions?

 

[Dave Peterson]

I initially tried it with a button on one worksheet that sorted a different
worksheet and your code worked fine in my tests in xl2003.

Are you running xl97?

If that's the case, change the .takefocusonclick property to false for that
commandbutton.

Show the control toolbox toolbar.
click on the design mode icon
rightclick on the commandbutton
choose properties
change .takefocusonclick to false
click on the design mode icon (to get out of that mode)

Alternatively, you could add this to the top of your procedure:

Activecell.activate

(This bug was fixed in xl2k.)

I've now figured out that it's comming from the CommandButton1 when I click
it from the "HList" Sheet, Code sorts ok when I run code from vba, any
suggestions?

 

[Richard]

Thanks that was it, yes I'm using 97, Thanks so much