Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting code
Hello,
I am new to VBA and have what is probably quite a simple problem. I have a two-column list that does not have column headers. The values in column 2 come are generated by links to other areas of sheet1 and change quite often. I have to keep resorting the list when a change occurs. I would like to find the code that will automatically sort on column 2 of my range as values change. -- Thanks in advance, MarkN |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting code
Try some VBA event code
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Columns("A:B").Sort key1:=Range("B1"), header:=xlNo End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Hello, I am new to VBA and have what is probably quite a simple problem. I have a two-column list that does not have column headers. The values in column 2 come are generated by links to other areas of sheet1 and change quite often. I have to keep resorting the list when a change occurs. I would like to find the code that will automatically sort on column 2 of my range as values change. -- Thanks in advance, MarkN |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting code
If the change is due to a recalculation as you imply, then you might want to
use the calculate event instead: Private Sub Worksheet_Calculate On Error GoTo ws_exit: Application.EnableEvents = False Columns("A:B").Sort key1:=Range("B1"), header:=xlNo ws_exit: Application.EnableEvents = True End Sub Place it in the same module as described by Bob. Additional information on events http://www.cpearson.com/excel/events.htm Chip Pearson's page on Events. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Try some VBA event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Columns("A:B").Sort key1:=Range("B1"), header:=xlNo End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Hello, I am new to VBA and have what is probably quite a simple problem. I have a two-column list that does not have column headers. The values in column 2 come are generated by links to other areas of sheet1 and change quite often. I have to keep resorting the list when a change occurs. I would like to find the code that will automatically sort on column 2 of my range as values change. -- Thanks in advance, MarkN |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting code
Thanks very much Gents,
I notice that everyone wants to get hold of a good VBA book, but it seems to be hard to find one. I have used your code and was able to modify it and include several more ranges to sort and a few other things but I am finding that I cannot 'create' some of the basic stuff that I need to do (loops, withs, etc). Would you have any recommendations for a person with limited time who wants to improve. -- Thanks again, MarkN "Bob Phillips" wrote: Try some VBA event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Columns("A:B").Sort key1:=Range("B1"), header:=xlNo End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Hello, I am new to VBA and have what is probably quite a simple problem. I have a two-column list that does not have column headers. The values in column 2 come are generated by links to other areas of sheet1 and change quite often. I have to keep resorting the list when a change occurs. I would like to find the code that will automatically sort on column 2 of my range as values change. -- Thanks in advance, MarkN |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting code
Try this,
Put below code to your worksheet's code module. Private Sub Worksheet_Calculate() Range("b1").CurrentRegion.Sort Key1:=Range("b1"), Order1:=xlAscending, Header:=xlNo _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Regards, Shah Shailesh http://in.geocities.com/shahshaileshs/ (Excel Add-ins Page) *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting code
Ignore this post.
Regards, Shah Shailesh http://in.geocities.com/shahshaileshs/ (Excel Add-ins Page) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code for sorting | Excel Discussion (Misc queries) | |||
Sorting by code | Excel Discussion (Misc queries) | |||
sorting code | Excel Discussion (Misc queries) | |||
Sorting with code | Excel Programming | |||
Sorting Using Code | Excel Programming |