Cargar ListBox obviando ciertas líneas según campo de una celda en una columna especifica
Hoy acudo a ustedes con una pregunta que me ha tenido días rondando.
Tengo un listBox donde cargo los datos de una hoja. La hoja puede variar pero los datos contenidos son los mismos.
En la columna F hay dos Posibles Valores, Salida, Entrega Si el valor de esa celda es entregado no debería cargarse al listbox esa toda esa fila de datos.
Adjunto mi código actual
Sub cargalista()
With Workbooks("Gestion.xlsm").Sheets(ComboDni.Text).Activate 'Activa la Hoja segun el DNI seleccionado
'
'Declaración de variables
'
Dim strTabla As String
Dim rngMirango As Range
Dim rngMirango2 As Range
Dim intColumnas As Integer
strTabla = "MiTabla"
On Error Resume Next
'
'Creamos el nombre a la tabla de la hoja activa
'
ActiveWorkbook.Names("MiTabla").Delete
Set rngMirango = ActiveSheet.Range("B1").CurrentRegion
Set rngMirango2 = rngMirango.Offset(1, 1).Resize(rngMirango.Rows.Count - 1, _
rngMirango.Columns.Count)
rngMirango2.Name = strTabla
intColumnas = rngMirango2.Columns.Count
'
'Formateamos ListBox
'
With Lista
.ColumnCount = intColumnas
.ColumnWidths = "60 pt;60 pt"
.ColumnHeads = True
End With
Lista.RowSource = strTabla
On Error GoTo 0
End With
End Sub
2 Respuestas
Lo que hace la macro es cargar un rango de celdas en el listbox.
Para quitar unas líneas, tendrías que filtrar por los valores que digan "Salida".
Para mantener la propiedad ColumnHeads, debemos copiar lo filtrado a otra hoja.
Crea una hoja y le pones por nombre "temp"
Te anexo el código para filtrar la columna F con "Salida", llevar el resultado a la hoja "temp" y cargar en el listbox
Sub cargalista()
With Workbooks("Gestion.xlsm").Sheets(ComboDni.Text).Activate 'Activa la Hoja segun el DNI seleccionado
'
'Declaración de variables
'
Dim strTabla As String
Dim rngMirango As Range
Dim rngMirango2 As Range
Dim intColumnas As Integer
strTabla = "MiTabla"
On Error Resume Next
'
'Creamos el nombre a la tabla de la hoja activa
'
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
ActiveWorkbook.Names("MiTabla").Delete
Set rngMirango = ActiveSheet.Range("B1").CurrentRegion
Set rngMirango2 = rngMirango.Offset(1, 1).Resize(rngMirango.Rows.Count - 1, _
rngMirango.Columns.Count)
rngMirango2.Name = strTabla
'
'Filtro de la hoja activa y se copia a la hoja temp
'
rngMirango.AutoFilter Field:=6, Criteria1:="Salida"
Set ht = Sheets("temp")
ht.Cells.ClearContents
ActiveSheet.Rows(1).Copy ht.Rows(1)
rngMirango2.Copy ht.Range("B2")
Set rngMirango3 = ht.Range("B1").CurrentRegion
Set rngMirango4 = rngMirango3.Offset(1, 1).Resize(rngMirango3.Rows.Count - 1, _
rngMirango3.Columns.Count)
rngMirango4.Name = strTabla
intColumnas = rngMirango2.Columns.Count
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
'
'Formateamos ListBox
'
With Lista
.ColumnCount = intColumnas
.ColumnWidths = "60 pt;60 pt"
.ColumnHeads = True
End With
Lista.RowSource = strTabla
On Error GoTo 0
End With
End Sub.
.
Hola dante funciona, sin embargo debería borrarse lo que se copia en esa hoja temporal. Además de eso al cargar la hoja temporal al listbox se me altera otro procedimiento que tengo más adelante, donde obtengo el numero de la fila seleccionada para poder proceder a modificar un campo de esa fila.
Lo que hago en estos casos es, en lugar de filtrar, se tiene que pasar registro por registro de la hoja1 a la hoja "temp", además a cada registro se le pone el número de fila en la que está ubicado en la hoja1, de esa forma cuando seleccionas el registro, leemos ese número de fila del list; y de esa forma podremos saber cuál fila real de la hoja es la que quieres modificar.
Te anexo el código, antes de iniciar el "filtro" limpio la hoja "temp"
Sub cargalista()
'Act.Por.Dante Amor
Set h1 = Sheets(ComboDni.Text)
Set h2 = Sheets("temp")
H2. Cells. ClearContents
H1. Rows(1). Copy h2. Rows(1)
'
'Pasamos a la hoja temp los registros que cumplen con la condición
'
j = 2
uc = h1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
For i = 2 To h1.Range("F" & Rows.Count).End(xlUp).Row
If h1.Cells(i, "F") = "Salida" Then
h1.Rows(i).Copy h2.Rows(j)
h2.Cells(j, uc) = i 'almacenamos el número de fila
j = j + 1
End If
Next
'
'Formateamos ListBox
'
uf = h2.Range("F" & Rows.Count).End(xlUp).Row
rango = h2.Range("B2", h2.Cells(uf, uc)).Address
With Lista
.ColumnCount = uc - 1
.ColumnWidths = "60 pt;60 pt"
.ColumnHeads = True
.RowSource = h2.Name & "!" & rango
End With
End SubAhora, para saber la fila que estás seleccionando, la lees de la última columna del list:
Private Sub Lista_Click() fila = Lista.List(Lista.ListIndex, Lista.ColumnCount - 1) End Sub
Avísame si necesitas ayuda para la parte donde modificas los datos.
Sal u dos. Dante Amor
.
Haces que la macro ordene en forma descendente para que todas las salidas estén en primer lugar, luego mediante una función contar contabilizas cuantas salidas hay, después ajustas el rngmirango2 a solo las salidas y listo, en el listbox unicamente se cargaran las salidas.
Sub cargalista()
With Workbooks("Gestion.xlsm").Sheets(ComboDni.Text).Activate 'Activa la Hoja segun el DNI seleccionado
'
'Declaración de variables
'
Dim strTabla As String
Dim rngMirango As Range
Dim rngMirango2 As Range
Dim intColumnas As Integer
strTabla = "MiTabla"
On Error Resume Next
'
'Creamos el nombre a la tabla de la hoja activa
'
ActiveWorkbook.Names("MiTabla").Delete
Set rngMirango = ActiveSheet.Range("B1").CurrentRegion
With rngMirango
.Sort key1:=ActiveSheet.Range(.Columns(.Columns.Count).Address), order1:=xlDescending, Header:=xlYes
cuenta = WorksheetFunction.CountIf(.Columns(.Columns.Count), "salida")
Set rngMirango2 = .Resize(cuenta, .Columns.Count)
End With
With rngMirango2
.Name = strTabla
intColumnas = .Columns.Count
End With
'
'Formateamos ListBox
'
With Lista
.ColumnCount = intColumnas
.ColumnWidths = "60 pt;60 pt"
.ColumnHeads = True
End With
Lista.RowSource = strTabla
On Error GoTo 0
End With
End Sub
Hola, gracias por tu respuesta. Sin embargo el método efectivamente ordena la lista. Pero no carga los datos al listbox
¿En dónde esta el listbox?, ¿En la hoja?, ¿O en un formulario VBA?, ¿En cada hoja sgune el DNI?, su pudieras subir una imagen de tu información podría ver porque no sube los datos.
hola el listbox esta en un formulario. ComboDni es parte de este formulario.
El mismo contiene el nombre de la hoja a seleccionar.
Lo que quieres es correr un procedimiento por fuera del formulario para cargar los datos al listbox, es más fácil hacer que estos datos se carguen al momento de cargar el formulario, este es un ejemplo que puede adaptar a tus necesidades
y esta es la macro que debes colocar en el formulario.
Private Sub UserForm_Initialize()
Set datos = Range("b1").CurrentRegion
With datos
f = .Rows.Count: c = .Columns.Count
Set datos = .Rows(2).Resize(f - 1, c)
.Sort key1:=ActiveSheet.Range(.Columns(c).Address), order1:=xlDescending
cuenta = WorksheetFunction.CountIf(.Columns(c), "salida")
Set salidas = .Resize(cuenta)
End With
With lista
.ColumnCount = c
.ColumnWidths = "60 pt;60 pt"
.ColumnHeads = True
.RowSource = salidas.Address
End With
End Sub