VBA problem

[Guest]

Can anybody tell me why variable g is causing an error when it gets to
order1:=g? Why wouldn't d be causing the same error? Thanks!

d = InputBox("What cell do you want to sort by on this page?", Xpos:=9000,
Ypos:=7000)
g = InputBox("How do you want to sort? Please answer either" & Chr(13) & _
"with xlAscending or xlDescending", Xpos:=9000, Ypos:=7000)

If d = "" Then
GoTo Option1
End If

On Error GoTo Option2

ActiveSheet.Range(a, c).EntireRow.Select
Selection.Sort Key1:=Range(d), order1:=g

 

[Bob Phillips]

Because the sort order expects a long value of 1 or 2. xlAscending and
xlDescending are Excel constants that evaluate top 1 or 2. You cannot use a
string with xlAscending or xlDescending in the Sort function, you need to
decode to 1 or 2.

Maybe

Selection.Sort Key1:=Range(d), order1:=IIf(LCase(g) = "xlascending",
1, 2)

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

Mark1,

You aren't really using xlAscending and xlDescending, which are defined
constants. You are using strings instead, which aren't really the same
thing: "xlAscending" does not equal xlAscending

Instead, use soemthing like:

Dim g As Variant
g = MsgBox("Click Yes for Ascending and No for Descending", vbYesNo)

Then in the sort, use:

Order1:=IIf(g = vbYes, xlAscending, xlDescending)

HTH,
Bernie
MS Excel MVP