Mostrar resultado de una búsqueda en un listbox estando oculta la hoja de datos
Necesito hacer un formulario en Excel para buscar datos y que los muestre en un listbox pero quiero que la hoja donde están estos datos esté oculta para que no la vean el resto de los usuarios. Tengo el siguiente código:
'Cambia el TextBox con cada cambio en el Combo
Private Sub cmbEncabezado_Change()
Me.lblFiltro = "Filtro por " & Me.cmbEncabezado.Value
End Sub
'
'Mostrar resultado en ListBox
Private Sub CommandButton5_Click()
Set h1 = Sheets("ENTIDADES")
Set H2 = Sheets("Temporal")
If Me.txtFiltro1.Value = "" Then
MsgBox "Debe introducir un criterio y un valor de búsqueda"
Exit Sub
If cmbEncabezado = "" Then
Exit Sub
End If
End If
Application.ScreenUpdating = False
H2.Cells.Clear
ListBox1.RowSource = ""
h1.Rows(1).Copy H2.Rows(1)
j = cmbEncabezado.ListIndex + 1
n = 2
For i = 2 To Range("a1").CurrentRegion.Rows.Count
If LCase(Cells(i, j)) Like "*" & LCase(txtFiltro1) & "*" Then
h1.Rows(i).Copy H2.Rows(n)
n = n + 1
End If
Next i
u = H2.Range("A1").CurrentRegion.Rows.Count
If u = 1 Then
MsgBox "No existen registros con ese filtro", vbExclamation, "FILTRO"
Exit Sub
End If
ListBox1.RowSource = H2.Name & "!A2:W" & u
Application.ScreenUpdating = True
End Sub
'Activar la celda del registro elegido
Private Sub Listbox1_Click()
Range("a2").Activate
Cuenta = Me.ListBox1.ListCount
Set rango = Range("A1").CurrentRegion
C = rango.Columns.Count
For i = 0 To Cuenta - 1
If Me.ListBox1.Selected(i) Then
Valor = Me.ListBox1.List(i)
rango.Find(What:=Valor, LookAt:=xlWhole, After:=ActiveCell).Resize(1, C).Select
End If
Next i
End Sub
'Dar formato al ListBox y traer los encabezados de la tabla
Private Sub UserForm_Initialize()
For i = 1 To 20
'Me.Controls("Label" & i) = Cells(1, i).Value
Next i
With ListBox1
.ColumnHeads = True
.ColumnCount = 22
.ColumnWidths = "0;150;200;150;100;50;80;40;40;40;60;150;50;80;80;70;60;60;60;300;100;100"
cmbEncabezado.List = Application.Transpose(Sheets("Entidades").Range("A1").CurrentRegion.Resize(1).Value)
cmbEncabezado.ListStyle = fmListStyleOption
End With
End Sub
Ahora mismo solo filtra los resultados si la hoja esta mostrada y estoy en la misma hoja. Si la hoja está oculta o estoy en otra hoja sale el mensaje de que no existen resultados cuando sí que existen..
Alquien me puede echar una mano.
2 Respuestas
Me parece que solamente tienes que cambiar esta línea
If LCase(Cells(i, j)) Like "*" & LCase(txtFiltro1) & "*" Then
Por esta (falta el objeto h1 para hacer referencia a la hoja):
If LCase(h1.Cells(i, j)) Like "*" & LCase(txtFiltro1) & "*" Then
Por otra parte, si la hoja está oculta o te encuentras en otra hoja y aplicas esto:
'Activar la celda del registro elegido
Private Sub Listbox1_Click()
Range("a2").Activate
Cuenta = Me.ListBox1.ListCount
Set rango = Range("A1").CurrentRegion
C = rango.Columns.Count
For i = 0 To Cuenta - 1
If Me.ListBox1.Selected(i) Then
Valor = Me.ListBox1.List(i)
rango.Find(What:=Valor, LookAt:=xlWhole, After:=ActiveCell).Resize(1, C).Select
End If
Next i
End SubCon Range("A2").Activate, te va a seleccionar la celda de la hoja activa.
Lo mismo con
rango.Find(What:=Valor, LookAt:=xlWhole, After:=ActiveCell).Resize(1, C).Select
Te va a buscar en la hoja activa y si encuentra algo te va a seleccionar una celda en la hoja activa, no en la hoja oculta.
Si quieres saber en dónde se encuentra el dato en la hoja oculta puedes hacer lo siguiente:
Private Sub Listbox1_Click()
Set h1 = Sheets("ENTIDADES")
valor = ListBox1.List(ListBox1.ListIndex, 0)
Set b = h1.Columns("A").Find(valor, lookat:=xlWhole, LookIn:=xlValues)
If Not b Is Nothing Then
'En el objeto b tienes todo lo de la celda, puedes obtener
'el color, el tamaño de la celda, el valor, la dirección
'le número de fila, el número de columna,
'el dato de esa fila de la columna C, etc
'
MsgBox "el valor está en la celda : " & b.Address & vbCr & _
"El dato de la celda C es : " & h1.Cells(b.Row, "B").Value
'
End If
End SubDe esa forma tu hoja "ENTIDADES" puede estar oculta, pero puedes traer información, sin necesidad de seleccionarla.
'.[Sal u dos. Dante Amor. No olvides valorar la respuesta. '.[Avísame cualquier duda .
Buenas tardes Dante:
He hecho lo que me has señalado pero me sigue dando el mismo problema... He quitado el que me diga en que celda esta y el valor porque no me haría falta... Te paso el archivo por si le puedes echar un vistazo a ver si ves algo mal..... Por cierto, he creado un botón comando que llama al formulario MODIFICAR_ENTIDADES para editar los datos filtrado en el listbox y poder modificarlos pero en lugar de trasladar los datos me traslada el nombre de los campos y no los datos. Tengo este formulario en otra base y funciona perfectamente... no entiendo por qué aquí no.....
Te paso el archivo por si me puedes ayudar...
https://drive.google.com/open?id=1i6KNnJncOZ0R0xVEmtD41ngS0_1DQQqG
Gracias de antemano por tu tiempo
Antes de revisar tu archivo, vamos resolviendo cada una de las peticiones.
En esta petición estás solicitando
Necesito hacer un formulario en Excel para buscar datos y que los muestre en un listbox pero quiero que la hoja donde están estos datos esté oculta
Antes de avanzar a lo de las modificaciones de datos, primero vamos a cargar el listbox y eso se debe resolver con esto:
If LCase(h1.Cells(i, j)) Like "*" & LCase(txtFiltro1) & "*" Then
Si ya se resolvió lo de la carga del listbox con la hoja oculta, entonces valora esta respuesta y crea una nueva con la siguiente petición y con gusto la reviso.
Buenos días de nuevo Dante:
Tienes razón para el tema de las modificaciones tendría que abrir otra pregunta.... lo que pasa que como formaba parte del mismo formulario aproveché esta consulta. Pero bueno primero vamos a lo que planteaba que era cargar resultados en el listbox con la hoja oculta. He probado lo que me has propuesto pero sigue sin funcionar..... Si quieres echarle un vistazo al archivo que envié para que veas que no funciona te lo agradecería...
Muchas gracias y perdona!!
Envíame tu archivo con la macro actualizada para revisar la carga al listbox
Mi correo [email protected]
En el asunto del correo escribe tu nombre de usuario “susania”
Buenas tardes Dante
Acabo de remitirte el correo tal y como me has indicado.
Muchas gracias!!
También en esta línea falta el h1
For i = 2 To Range("a1").CurrentRegion.Rows.Countdebe ser así
For i = 2 To h1.Range("a1").CurrentRegion.Rows.CountDebes tener cuidado en el código, en cualquier instrucción que hagas referencia a una celda, debes especificar de cuál hoja.
Y eso es todo! [No olvides valorar la respuesta.
Dentro del código del botón, al principio colocas
Hoja1. Visible. XlSheetVisible
Y al final
Hoja1. Visible. XlSheetVeryHidden
La hoja1 es de ejemplo, tu pon la que corresponda
Prueba así y me dices.
Uy perdona, es así: Hoja1.Visible = xlSheetVisible