Get a sort to work from a userform

[rpaulson]

ANy Guru know why does this not work?

Private Sub UserForm_initialize() 
...
ComboBox6.List = Array("xlAsending", "xlDescending") 
....

Private Sub CommandButton2_Click()
Range("B10:T100").Sort Key1:=Range("B10"), Order1:=ComboBox6.Value, Header:=xlYes

thanks,

Ross

 

[Rick Rothstein]

ANy Guru know why does this not work?

Private Sub UserForm_initialize() 
...
ComboBox6.List = Array("xlAsending", "xlDescending") 
....

Private Sub CommandButton2_Click()
Range("B10:T100").Sort Key1:=Range("B10"), Order1:=ComboBox6.Value, Header:=xlYes

First off, you misspelled xlAscending. Second, your values in the ComboBox are text string, not numerical values... xlAscending and xlDescending are pre-defined constants for the numbers 1 and 2 respectively... the Sort method expects the numbers, not the text. Assuming you correct the spelling as noted above, change your CommandButton Click event code to this and it should work...

[table="width: 500"]
[tr]
	[td]Private Sub CommandButton2_Click()
  Range("B10:T25").Sort Key1:=Range("B10"), Order1:=IIf(ComboBox1.Value = "xlAscending", 1, 2), Header:=xlYes
End Sub[/td]
[/tr]
[/table]

By the way, why not put this code in the ComboBox's Change event instead of the CommandButton's Click event... that way the sort would happen immediately upon the selection from the ComboBox?

 

[rpaulson]

Rick,

Thanks for the quick response

I looked at the spelling 30 times. My dislexia is acting up. I guess it would have hot helped until I use the IIf anyway.

I never knew there was a ComboBox's Change event, ill look into it.

Thanks,
Ross