Automatically leave dates in order

1

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

Dsd already thank you for being able to help.

    
asked by anonymous 30.12.2017 / 16:23

1 answer

0

Automatic sorting using VBA

To implement the solution, the Desenvolvedor must be enabled. If you have not done it yet, here's how to do it: Show Developer tab .

Taking the following example as an example:

Performthefollowingstepstoautomatesortingbythedatecolumn:

1)Createtablesortmacro

1.1)ClicktheDesenvolvedor

1.2)ClicktheVisualBasicbutton(orpressAlt+F11)toopentheVBAwindow

1.3)IntheInserirmenu,clicktheoptionMódulotoaddModulo1toVBAProject

1.4)IntheModulo1codewindow,addthefollowing:

PublicSubOrdernarPlanilha1()ConstINI_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 specified cell at constant INI_COL_ORDENAR .

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

2)Callsortingmacrowhenopeningspreadsheetfile

2.1)InthecodewindowoftheEstaPasta_de_trabalhomodule,addthefollowingprocedure:

PrivateSubWorkbook_Open()OrdernarPlanilha1EndSub
  • Thisprocedureperformssortingautomaticallywhenthefileisopened

3)Addabuttontocallthesortmacro

3.1)GobacktotheExcelwindowandclickontheDesenvolvedor

3.2)ClickontheInserirControlesbuttonandselectthecontrolBotão

3.3)Drawthebuttonontheworksheet,selecttheOrdernarPlanilha1macro,andclickOK

3.4)Changethebuttonlabelto"Sort"

4) Call the sort macro when the worksheet changes

In particular, I do not like this procedure because it changes the order of the rows 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 spreadsheet module (in case of our example it will be the module Plan1 )

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

Ready,justuse!

Morethanonetableinthesameworksheet...

Ifthespreadsheethasmorethanonetabletosort,thesortroutinecanbechangedaccordingtothefollowingsuggestionstobeperformedforeachofthetables:

PublicSubOrdernarPlanilha1()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
    
02.01.2018 / 04:25