Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > help with input box
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Ken is offline
external usenet poster
  
Posts: 590
Default help with input box
I am using an Input box for my sheet to sort, I have 28 sorts and it is not
working. Can someone tell me if my code is incorrect. The sort works fine
when run from a Macro, but I am having problems with it using an input box.
Thank you
Ken

Sub All_Sorts1()
'
Dim MySort As Long
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlDescending,
Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 7,247
Default help with input box
See my reply in the misc group. Please don't post to multiple
newsgroups.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Fri, 5 Mar 2010 12:27:14 -0800, Ken
wrote:

I am using an Input box for my sheet to sort, I have 28 sorts and it is not
working. Can someone tell me if my code is incorrect. The sort works fine
when run from a Macro, but I am having problems with it using an input box.
Thank you
Ken

Sub All_Sorts1()
'
Dim MySort As Long
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlDescending,
Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select

End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to input pictures automatically based on cell input? bsharp Excel Worksheet Functions 9 May 30th 09 07:16 AM
input in number form is being multiplied by 1000 when i input. jweinograd Excel Discussion (Misc queries) 4 April 16th 07 11:18 PM
Have user input converted to uppercase in same cell as input? Shannonn New Users to Excel 1 June 20th 06 03:19 AM
How do I add input data in the input ranges in drop down boxes. oil_driller Excel Discussion (Misc queries) 1 November 9th 05 10:31 PM
=SUMIF(Input!H2:H718,AZ19,Input!E2:E685)AND(IF ALex Excel Worksheet Functions 2 March 14th 05 09:19 PM


All times are GMT +1. The time now is 05:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement