Automatically leave dates in order

Question

How do I leave my table that has dates organized in order, type, has the cells: date of issue; name; expiration date; situation, I wanted my table organized by date of expiration automatically when I put a new value, either by pressing a button or when I open it. I believe I should use vba, only I do not understand anything msm.

DSD thanks anyone who can help.

Answer 1

Automatically sort using VBA

To implement the solution, it is necessary that the guide Desenvolvedor is enabled. If you haven’t done this yet, see how to Show the Developer tab.

Taking as an example the following table:

Perform the following steps to automate sorting by the date column:

1) Create table sort macro

1.1) Click on the tab Desenvolvedor

1.2) Click the Visual Basic button (or press Alt+F11) to open the VBA window

1.3) In the menu Inserir, click on the option Módulo to add the Modulo1 to Vbaproject

1.4) In the code window of Modulo1, add the following procedure:

Public Sub OrdernarPlanilha1()

    Const INI_COL_ORDENAR = "Plan1!B1" 

    Range(INI_COL_ORDENAR).Sort Key1:=Range(INI_COL_ORDENAR), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom
End Sub

• This procedure will sort the entire table around the cell specified in the constant INI_COL_ORDENAR.

• The constant INI_COL_ORDENAR should contain the address of the cell where the column containing the sorting values starts (in our example, it is the cell Plan1!B1)

2) Calling sort macro when opening the spreadsheet file

2.1) In the module code window EstaPasta_de_trabalho, add the following procedure:

Private Sub Workbook_Open()
    OrdernarPlanilha1
End Sub

• This procedure automatically executes the sort when the file is opened

3) Add a button to call the sort macro

3.1) Go back to the Excel window and click on the tab Desenvolvedor

3.2) Click on the button Inserir Controles and select the control Botão

3.3) Draw the button on the worksheet, select the macro OrdernarPlanilha1 and click OK

3.4) Change the button label to "Sort"

4) Call the sort macro when the spreadsheet is changed

Particularly, I don’t like this procedure because it changes the order of the lines while you edit the table and this can cause some confusion. But if you want to implement, just do the following:

4.1) In the VBA window, add the following code to the worksheet module (in the case of our example will be the module Plan1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    OrdernarPlanilha1
End Sub

All right, just use!

More than one table in the same spreadsheet...

If the worksheet has more than one table to be ordered, the sorting routine can be changed as suggested below to be executed for each of the tables:

Public Sub OrdernarPlanilha1()
    OrdernarTabela "Plan1!B1"
    OrdernarTabela "Plan1!B10"
End Sub   

Private Sub OrdernarTabela(ini_col_ordenar As String)

    Range(ini_col_ordenar).Sort Key1:=Range(ini_col_ordenar), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom
End Sub