martes, 9 de abril de 2019

VBA: Validación de datos siempre ordenada

Me encontré un cliente que necesitaba tener siempre ordenados los elementos permitidos en una celda con validación de datos tipo lista... la solución que encontré fue emplear un método Range.Sort con un evento de hoja Worksheet_Change.

Veamos en qué consistiría el trabajo.
Tenemos un listado, en modo tabla (que llamaré 'TblCiudad'), que tiene algunas ciudades de España... a la que además hemos asignado un nombre definido:
ndCiudades =TblCiudad[ciudades]

VBA: Validación de datos siempre ordenada


En una celda D2 hemos configurado una validación de datos tipo Lista con origen en el nombre definido anterior 'ndCiudades'

VBA: Validación de datos siempre ordenada



El siguiente paso consistirá en añadir dentro de la ventana de código de la hoja de trabajo (Hoja1 para nuestro ejemplo) el siguiente evento Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
'evento que saltará ante cualquier cambio en la hoja..
Dim rngCiudades As Range
Set rngCiudades = Hoja1.Range("TblCiudad[Ciudades]")

'pero verificamos que únicamente ordenaremos cuando trabajemos en la tabla
If Not Intersect(Target, rngCiudades) Is Nothing Then
'conel méetodo Sort ordenamos el listado...
rngCiudades.Sort _
    Key1:=rngCiudades, _
    order1:=xlAscending, _
    Orientation:=xlSortColumns, _
    Header:=xlYes
End If

End Sub



Una vez insertado el evento podremos comprobar al cambiar algo en la tabla, y solo en la tabla, que se producirá la ordenación deseada... lo que inmediatamente repercutirá en el orden de la celda validada:

VBA: Validación de datos siempre ordenada

9 comentarios:

  1. Ahora se podría hacer más fácil y rápido con las nuevas funciones matriciales dinámicas: =ORDENAR(A2:A15) te da esa solución... A ver si lo implantan ya para todos los usuarios!!!

    ResponderEliminar
    Respuestas
    1. Correcto Sergio...
      pero de momento hay que esperar o pasarse a versiones 'Insiders'
      ;-)

      Eliminar
  2. Muchas gracias Ismael por este interesante artículo.

    Quizás mi pregunta sea muy elemental, pero me he liado en el paso de asignar un nombre definido a la tabla de ciudades (tblCiudad). ¿Cómo configuras ndCiudades =TblCiudad[ciudades]?

    Conozco la pestaña de nombres definidos, en fórmulas, pero no consigo configurarlo en la forma que comentas.

    Saludos.

    Carlos.

    ResponderEliminar
    Respuestas
    1. Hola Carlos
      es más sencillo de lo que parece
      Seleccionas el campo de la TblCiudad y con el rango seleccionados Asignas Nombre definido con ese nombre... bien desde el Cuadro de nombres o bien desde la ficha Fórmulas> grupo Nombres definidos>botón Asignar nombres

      Slds

      Eliminar
  3. Hola Ismael, Excelente en ejemplo, yo al ver el ejemplo me pregunte: ¿Como hacer la validación de datos ordenada sin que la base de datos origen se ordene? y con la GRABADORA DE MACROS lo hice, claro con solo unos pequeños cambios en el código, esto para demostrar lo importante que es la GRABADORA DE MACROS, para los que no sabemos muy bien VBA-Excel.

    Sub Macro3()
    Dim Uf&
    Application.ScreenUpdating = False
    Uf = Range("A" & Rows.Count).End(xlUp).Row - 1
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Hoja2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hoja2").Sort.SortFields.Add2 Key:=Range("G1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Hoja2").Sort
    .SetRange Range("G1:G" & Uf)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    Range("E2").Select
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=$G$1:$G$" & Uf
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    Application.ScreenUpdating = True
    End Sub

    y en el código de la hoja:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCiudades As Range
    Set rngCiudades = Hoja2.Range("TblCiudades[Ciudades]")
    If Not Intersect(Target, rngCiudades) Is Nothing Then
    Call Macro3
    End If
    End Sub

    Lo hice en la hoja 2, los datos en la columna "A", Copio y ordeno los datos en la columna "G". y La validación de datos en E2.

    Saludos.

    ResponderEliminar
  4. Hola, disculpa mi molestia, mi duda es acerca de otro tema de EXCEL, me podrías ayudar, es acerca de una tabla de Posiciones de Equipos de fútbol:
    Columna A: EQUIPO
    Columna B: PARTIDOS JUGADOS
    Columna C: PARTIDOS GANADOS
    Columna D: PARTIDOS EMPATADOS
    Columna E: PARTIDOS PERDIDOS
    Columna F: GOLES A FAVOR
    Columna G: GOLES EN CONTRA
    Columna H: PUNTOS (HA CALCULAR, lo he realizado así =SUMA((C2*3)+(D2*1)+(E2*0)), EXISTE OTRA FORMA CON USO DE FUNCIONES?)

    APARTE SE PIDE HACER UNA TABLA EN LA MISMA HOJA (PARTE DE ABAJO)
    Columna A: EQUIPO
    Columna B: PUNTOS (lo he realizado así con BUSCARV), Se puede hacer de otra manera?
    Columna C: DIFERENCIA DE GOLES (HA CALCULAR, lo he realizado así =SI(F2>=0;F2-G2;0), EXISTE OTRA FORMA DE HACERLO CON FUNCIONES?)

    ResponderEliminar
    Respuestas
    1. Hola Sonia,
      en la columna H sería suficiente
      =(C2*3)+(D2*1)

      En la otra tabla, para las ordenaciones lee este post

      tendrás una ordenación de los puntos de los equipos, y sobre ello podrás recuperar con funciones de búsqueda el nombre (col A).

      Para la diferencia de goles simplemente =F2-G2

      Saludos

      Eliminar
    2. Ok. Muchas gracias :)

      Eliminar