Como Ordenar cuadros de lista con VBA

Recientemente recibí una pregunta de un lector que quería ordenar un cuadro de lista usando VBA. Como parecía un problema común al que muchos usuarios podrían enfrentarse, decidí dedicar un poco de tiempo para crear una solución más reutilizable.

En esta publicación, veremos dos tipos diferentes de cuadros de lista:

• Cuadros de lista de UserForm para soluciones VBA personalizadas
• Cuadros de lista de control de formularios para soluciones basadas en hojas de trabajo

Esta publicación puede parecer una gran cantidad de código VBA, pero son comentarios insertados en el código para comprender lo que está sucediendo. Dentro del texto incluí mi proceso de pensamiento, para ilustrar cómo abordé el escenario. Ya sea que mi enfoque sea correcto o incorrecto, así es como pienso sobre VBA.

Hay cuatro métodos de clasificación cubiertos en esta publicación:

• Orden estándar (el orden en que se agregan los elementos al cuadro de lista)
• Orden inverso (el reverso del orden estándar)
• clasificación AZ
• clasificación ZA

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0172 Ordenar ListBox después de load.zip

Tener acceso

Código reutilizable

Uno de los principios clave de esta pequeña tarea era que el código fuera lo más reutilizable posible. Por lo tanto, al abordar esto traté de aplicar los siguientes principios:

• El código que realiza las acciones principales está contenido en un módulo estándar.
• El código contenido en el módulo UserForm intercepta los eventos (es decir, clics en los botones) y luego llama a una macro desde el módulo estándar.
• Las variables utilizadas para las macros dentro del módulo estándar se pasan como argumentos al llamar a la macro.
• La lista se vuelve a crear cada vez que se ordena, sin importar qué método de clasificación se seleccione. Esto garantiza que la lista se actualice para cualquier cambio cada vez que se ordene. El fragmento de código reutilizable que borra y recrea la lista lo llamo "resetMacro".

Cuadros de lista en UserForms

La siguiente captura de pantalla muestra el formulario de usuario y contiene:

• un cuadro de lista
• cuatro botones para las diferentes opciones de clasificación
• un botón de cierre

Código VBA incluido en un módulo estándar.

El siguiente es el código a incluir en el módulo estándar, que realiza las acciones principales.

Mostrar el formulario de usuario

El primer código simplemente mostrará el formulario de usuario cuando se haga clic en un botón.

Sub ShowUserForm()'Mostrar el formulario de usuariofrmListBoxExample.ShowEnd Sub

Llenar el cuadro de lista

El siguiente código enumerará todas las hojas de trabajo en un ListBox. Una variable que hace referencia al ListBox específico se pasa a la macro en tiempo de ejecución, por lo tanto, el código se puede usar con cualquier ListBox. En nuestro ejemplo, se utiliza este código VBA:

• cuando se inicializa el formulario de usuario
• cuando se hace clic en el botón btnStandard
• dentro de todas las demás macros de clasificación para recrear la lista.

Sub UserFormList(myListBox As MSForms.ListBox)'Crear una variable de objeto para contener la hoja de trabajo como un objetoDim ws As Worksheet'Borra el listBoxmyListBox.Clear'Recorre cada hoja de trabajo y agrega a listBoxPara cada ws en ThisWorkbook.Worksheets myListBox.AddItem ws.NameNext wsEnd Sub

Invertir los elementos en el ListBox

El siguiente código primero llamará a "resetMacro" para actualizar el ListBox y luego lo ordenará en orden inverso.

Sub UserFormReverseList(myListBox As MSForms.ListBox, resetMacro opcional como String)'Crear variablesDim ListArray() As StringDim i As Integer'Restablecer el listBox al orden estándar Si resetMacro "" Luego ejecute resetMacro, myListBoxEnd If'Cambiar el tamaño de la variable de matrizReDim ListArray(0 a myListBox.ListCount - 1)'Recorre el listBox e invierte el orden en un ArrayFor i = 0 To myListBox.ListCount - 1 ListArray(i) = myListBox.List(myListBox.ListCount - 1 - i)Siguiente i'Borrar el listBoxmyListBox .Clear'Rellene el cuadro de lista con ArraymyListBox.List = ListArrayEnd Sub

Ordenar AZ el cuadro de lista

El siguiente código primero llamará a "resetMacro" para actualizar el ListBox y luego lo ordenará en orden AZ usando el método de clasificación Bubble.

Sub UserFormSortAZ(myListBox As MSForms.ListBox, resetMacro opcional como cadena)'Crear variablesDim j As LongDim i As LongDim temp As Variant'Restablecer el listBox al orden estándar Si resetMacro "" Luego ejecute resetMacro, myListBoxEnd If'Use el método de clasificación de burbujas para poner listBox en orden AZ Con myListBox Para j = 0 Para .ListCount - 2 Para i = 0 Para .ListCount - 2 Si LCase(.List(i)) LCase(.List(i + 1)) Entonces temp = .List(i) . Lista(i) = .List(i + 1) .List(i + 1) = temporal Fin si siguiente i Siguiente jEnd WithEnd Sub

Si observa que hay un -2 en el código, parece extraño. Entonces, ¿por qué está ahí y qué hace?

• Con el método de clasificación de burbujas, no es necesario ordenar el último elemento. Si todos los demás elementos de la lista están ordenados correctamente, entonces el último elemento también debe estar en el lugar correcto (por lo que es el primer -1).
• El primer elemento en un ListBox de UserForm es el elemento 0, en lugar de 1. Por lo tanto, como la lista comienza en cero, podemos -1 desde el recuento para asegurarnos de recorrer todos los elementos una vez.

Nota: Cuando se utiliza un cuadro de lista de Control de formulario, la primera posición de la lista es 1, por lo que el código VBA de Control de formulario más abajo en la publicación solo tiene -1, en lugar de -2.

ZA ordenar el ListBox

El siguiente código primero llamará a "resetMacro" para actualizar el ListBox y luego lo ordenará en orden ZA usando el método de clasificación Bubble.

Sub UserFormSortZA(myListBox As MSForms.ListBox, resetMacro opcional como cadena)'Crear variablesDim j As LongDim i As LongDim temp As Variant'Restablecer el listBox al orden estándar Si resetMacro "" Luego ejecute resetMacro, myListBoxEnd If'Use el método de clasificación de burbujas para poner listBox en orden ZA Con myListBox Para j = 0 Para .ListCount - 2 Para i = 0 Para .ListCount - 2 Si LCase(.List(i)) LCase(.List(i + 1)) Entonces temp = .List(i) . Lista(i) = .List(i + 1) .List(i + 1) = temporal Fin si siguiente i Siguiente jEnd WithEnd Sub

Código VBA incluido en el módulo de código UserForm

Las siguientes secciones de código están incluidas en el módulo UserForm. El código aquí es principalmente para llamar a las macros en el módulo estándar, que cubrimos en la sección anterior.

El nombre del ListBox en el ejemplo es lstListBox.

Inicializar el formulario de usuario

Cuando el UserForm se abre por primera vez, se ejecuta el código UserForm_Initialize. En nuestro ejemplo, cuando se abre UserForm, llama a la macro UserFormList y pasa lstListBox como una variable de objeto a la macro.

Sub privado UserForm_Initialize()Llamar a UserFormList(Me.lstListBox)End Sub

Al hacer clic en el botón btnCerrar

El botón de cerrar con Descargar el formulario de usuario.

Sub privado btnClose_Click()DescargarmeEnd Sub

Al hacer clic en el botón btnEstándar

El primer botón llama a la macro para devolver la lista a su orden original. Se llama al código UserFormList, pasando lstListBox como una variable de objeto.

Sub privado btnStandard_Click()Llamar a UserFormList(Me.lstListBox)End Sub

Al hacer clic en el botón btnReverse

El segundo botón llama a la macro para invertir el orden de la lista. En nuestro ejemplo, se llama al código UserFormReverseList. Se pasan dos argumentos (1) el objeto ListBox (2) el nombre de resetMacro.

Sub privado btnReverse_Click()Llamar a UserFormReverseList(Me.lstListBox, "UserFormList")End Sub

Al hacer clic en el botón btnAZ

El tercer botón llama a la macro para ordenar la lista en orden AZ. Se llama al código UserFormSortAZ. Se pasan dos argumentos (1) el objeto ListBox (2) el nombre de “resetMacro”.

Sub privado btnAZ_Click()Llamar a UserFormSortAZ(Me.lstListBox, "UserFormList")End Sub

Haciendo clic en el botón btnZA

El cuarto botón llama a la macro para ordenar la lista en orden ZA. Se llama al código UserFormSortZA. Se pasan dos argumentos (1) el objeto ListBox (2) el “resetMacro”.

Sub privado btnZA_Click()Llamar a UserFormSortZA(Me.lstListBox, "UserFormList")End Sub

Esto ahora completa el código utilizado para ordenar un ListBox contenido dentro de un UserForm.

Cuadros de lista de control de formulario en la hoja de trabajo

El código de esta sección se utiliza con un ListBox de control de formulario.

Cada botón está asignado a una macro que requiere argumentos , como resultado, no aparecen dentro del cuadro Asignar macro. Haga clic aquí para obtener más información sobre esta técnica .

Las macros asignadas a cada botón son las siguientes:

• Clasificación estándar: FormControlList “lstListBox”'
• Clasificación inversa: FormControlReverse “lstListBox”, “FormControlList”'
• Orden AZ:FormControlSortAZ “lstListBox”, “FormControlList”'
• Orden ZA:FormControlSortZA “lstListBox”, “FormControlList”'

Código VBA incluido en un módulo estándar.

El siguiente código está incluido en el módulo estándar.

Llenar el cuadro de lista

El siguiente código enumerará todas las hojas de trabajo en un ListBox. El ListBox específico se pasa a la macro como argumento, por lo que se puede utilizar con cualquier ListBox. Esta macro se llama:

• cuando se hace clic en el botón btnStandard
• dentro de todas las demás macros de clasificación para recrear la lista (es decir, "resetMacro").

Sub FormControlList(myListBoxName As String)'Crear variable de objeto para contener listBoxDim myListBox As Object'Establecer la variable en la hoja de trabajo y el listBox llamadoSet myListBox = ActiveSheet.ListBoxes(myListBoxName)'Crear una variable de objeto para contener la hoja de trabajo como un objetoDim ws como hoja de trabajo 'Borrar listBoxmyListBox.RemoveAllItems'Recorre cada hoja de trabajo y agrega a listBoxPara cada ws en ThisWorkbook.Worksheets myListBox.AddItem ws.NameNext wsEnd Sub

Invertir los elementos en el ListBox

El siguiente código primero llamará a "resetMacro" para actualizar el ListBox y luego lo ordenará en orden inverso.

Sub FormControlReverse(myListBoxName As String, opcional resetMacro As String)'Crear variablesDim ListArray() As StringDim i As IntegerDim myListBox As ListBox'Establezca la variable en la hoja de trabajo y el listBox llamadoEstablezca myListBox = ActiveSheet.ListBoxes(myListBoxName)'Restablezca el listBox al estándar orderIf resetMacro "" Luego ejecute resetMacro, myListBoxNameEnd If'Resize the Array variableReDim ListArray(1 To myListBox.ListCount)'Recorra el listBox e invierta el orden en un ArrayFor i = 1 To myListBox.ListCount ListArray(i) = myListBox.List (myListBox.ListCount - i + 1) Siguiente i'Borrar el listBoxmyListBox.RemoveAllItems'Rellenar el cuadro de lista con ArraymyListBox.List = ListArrayEnd Sub

Ordenar AZ el cuadro de lista

El siguiente código primero llamará a "resetMacro" para actualizar el ListBox y luego lo ordenará en orden AZ usando el método de clasificación Bubble.

Sub FormControlSortAZ(myListBoxName As String, opcional resetMacro As String)'Crear variablesDim j As LongDim i As LongDim temp As VariantDim myListBox As ListBox'Establezca la variable en la hoja de trabajo y listBoxSet myListBox = ActiveSheet.ListBoxes(myListBoxName)'Restablezca el listBox al estándar orderIf resetMacro "" Luego ejecute resetMacro, myListBoxNameEnd If'Use el método de clasificación de burbujas para colocar listBox en AZ ordenCon myListBox Para j = 1 Para .ListCount - 1 Para i = 1 Para .ListCount - 1 Si LCase(.List(i)) LCase (.List(i + 1)) Entonces temp = .List(i) .List(i) = .List(i + 1) .List(i + 1) = temp End If Next i Next jEnd WithEnd Sub

ZA ordenar el cuadro de lista

El siguiente código primero llamará a "resetMacro" para actualizar el ListBox, luego lo ordenará en orden ZA usando el método de clasificación Bubble.

Sub FormControlSortZA(myListBoxName As String, Opcional resetMacro As String)'Crear variablesDim j As LongDim i As LongDim temp As VariantDim myListBox As ListBox'Establezca la variable en la hoja de trabajo y listBoxSet myListBox = ActiveSheet.ListBoxes(myListBoxName)'Restablezca el listBox al estándar orderIf resetMacro "" Luego ejecute resetMacro, myListBoxNameEnd If'Use el método de clasificación de burbujas para colocar listBox en AZ ordenCon myListBox Para j = 1 Para .ListCount - 1 Para i = 1 Para .ListCount - 1 Si LCase(.List(i)) LCase (.List(i + 1)) Entonces temp = .List(i) .List(i) = .List(i + 1) .List(i + 1) = temp End If Next i Next jEnd WithEnd Sub

Conclusión

En esta publicación, hemos visto dos métodos para ordenar ListBoxes usando VBA usando segmentos de código reutilizables. Obviamente, para que sea útil, esto debe ser parte de un proyecto más grande. Siéntase libre de tomar esto y adaptarlo según sea necesario.

---