How to Sort Data Using VB in Excel Part 2

TGoldenEye

This creates a feeling of inadequacy, although it may hurt your ego, but do visit a doctor and ward off price viagra your doubts. In time of erectile stage, the blood circulating veins and arteries are getting lots of blood circulation. on line cialis The mere fact that it involves the brain makes it really effective in case of nervous system dysfunctions that cause cheap levitra generic neuropathic pain. Although ED is a more common condition than most couples will levitra online india acknowledge.

How to Sort Data Using VB in Excel Part 2

In this part, I will show you how to add more buttons to sort data in each corresponding field. We have already added the button for the CustomerName field. Now we need to add sorting buttons for the Address, City, State, and Zip fields.

 Add More Buttons

You can add a button from the Developer Menu as demonstrated in Part 1. We can also just copy the button from the previous How To and then rename it. This is my favorite method. Follow the step below:

  • After adding the 2nd button, we need to rename both the name and text (caption) to Address.

  • Drag the Address button to cover the Address cell and resize it to fit the cell box

We have already completed the Customer Name sorting button with VB code in Part 1 as shown below:

Full Code:
Sub SortCustomerName()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row  'get last row index =18
With ActiveSheet.Buttons("CustomerName")
    If .Caption = "Customer Name" Then
        .Caption = "Customer Name "
         Range("A3:F" & lastRow).Sort key1:=Range("B3:B" & lastRow), order1:=xlAscending, Header:=xlNo
    Else
         Range("A3:F" & lastRow).Sort key1:=Range("B3:B" & lastRow), order1:=xlDescending, Header:=xlNo
        .Caption = "Customer Name"
      End If
End With
End Sub

Now we can update the VB code on the Customer Name button. I have created a Macro/Sub SortAddress() procedure for sorting the address and assigned a Macro to the Address button as shown below. Per code below, I created two functions: SorfieldsASC() and SortFieldsDESC(),then called for each function on the Sub SortAddress() procedure. When the users click the Address button the first time, it will sort the address field by ascending on the cell C3:C18. The 2nd click will sort the data by descending.  We can use these two functions on many buttons.

Now add all the buttons through the Zip Code field as shown in the picture below.

After we add all the buttons, we need to create a Macro name and assign it to those buttons. We can use the two functions of sorting from the Address button. I went the easy way, and just copied the code from the Sub SortAddress() above and then changed the button name, button caption, and cell range for sorting.

Example for Sub SortZipcode():

  • button name = Zip
  • button caption = Zip Code
  • field for sorting = F3:F

In the picture below, you will see how the two functions were used in each Sub/Macro. You can only change the corresponding button’s name, button caption, and cell range for sorting.

Enhance Function

According to the picture above, you can see that all the Sub/Macro names have the same structure. In each Sub/Macro, you can see the repeating usage of With…End With Statement, If…Else…Statement, SortFieldsASC function, and SortFieldsDESC function. The functions and Sub/Macro makes the VB code for the buttons seem very long. We can shorten all the VB codes by creating a new function. We can put all the With…End With Statement, If…Else…Statement, SortFieldsASC function, and SortFieldsDESC function into one new function. Then we can pass the values of the button names, button captions, and cell ranges to the parameters as shown in the picture below.

Full Code:

Public Function SortFields(BtnName As String, BtnCaption As String, CellRange As String)
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row  'get last row index =18
With ActiveSheet.Buttons(BtnName)
    If .Caption = BtnCaption Then
        .Caption = BtnCaption & " "
         Range("A3:F" & lastRow).Sort key1:=Range(CellRange & lastRow), order1:=xlAscending, Header:=xlNo
    Else
        .Caption = BtnCaption
         Range("A3:F" & lastRow).Sort key1:=Range(CellRange & lastRow), order1:=xlDescending, Header:=xlNo
      End If
End With
End Function
……….........
Sub SortZipCode()
       Call SortFields(“Zip”, “Zip Code”, “F3:F”)
End Sub

Explanation:

  • Function name = SortFields
  • The button name (“Zip”) will be passed through the BtnName parameter in the function
  • The button caption (“Zip Code”) will be passed through the BtnCaption parameter in the function
  • The cell range (“F3:F”) will be passed through the CellRange parameter in the function
  • lastRow = 18 (per my Excel data)

We can pass the value to the parameters: the button name with “Zip”, button caption  with “Zip Code”, and cell range with “F3:F” in the function as shown below.

Public Function SortFields(“Zip”, “Zip Code”, “F3:F”)
Dim lastRow As Long
lastRow = Cells(Rows.Count, “A”).End(xlUp).Row  ‘get last row number =18
With ActiveSheet.Buttons(“Zip”,)
   If .Caption = “Zip Code” Then
       .Caption = “Zip Code” & ” “
        Range(“A3:F” & lastRow).Sort key1:=Range(“F3:F” & lastRow), order1:=xlAscending, Header:=xlNo
   Else
       .Caption = “Zip Code”
        Range(“A3:F” & lastRow).Sort key1:=Range(“F3:F”& lastRow), order1:=xlDescending, Header:=xlNo
     End If
End With
End Function

  • .Caption = “Zip Code” & ” ” = . Caption = “Zip Code “
  • Range(“A3:F” & lastRow) .Sort key1:=Range(“F3:F” & lastRow) = Range(“A3:F18”) .Sort key1:=Range(“F3:F18”)

Related posts