MOSTRAR INFORMACION DE UNA TABLA EN UN MSGBOX USANDO ARRAY

jesuspg · el 12 de marzo

Hola compañeros, necesito solucionar el siguiente problema:

"Tengo dos tablas de ejemplo, una se llama TABLA y la otra RESULTADOS.

Cada una de ellas contiene su propia clave primaria.

Tabla TABLA

Tabla RESULTADOS

El problema que quiero solucionar es que cada vez que un usuario haga click en una de las celda del campo FK TABLA, se le muestre toda la información detallada de ese registro en concreto, tal y como se muestra en la siguiente imagen.

Video DEMO

Espero que con el vídeo anterior se vea claramente lo que quiero conseguir.

Este es el código que utilizo.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ' Limpiamos los errores anteriores...
   Err.Clear

   ' Tratamos los errores...
   On Error Resume Next

   ' Declaramos las variables de ámbito local...
   Dim columna As Byte, ultimaColumna As Byte
   columna = 1
   ultimaColumna = ThisWorkbook.ActiveSheet.Cells(1, 10000).End(xlToLeft).Column
   Dim celda As Range, tabla As Range
   Set celda = Selection
   Set tabla = ThisWorkbook.Sheets("TABLA").Cells(1, 1).CurrentRegion

   ' Recorremos las columnas de la tabla...
   For columna = 1 To ultimaColumna
      ' Si el nombre de la columna coincide, entonces...
      If ThisWorkbook.Sheets("RESULTADOS").Cells(1, columna) = "FK TABLA" And celda.Column = columna Then
         ' Notificamos al usuario con un mensaje de información...
         MsgBox Prompt:="> PK TABLA: " & Application.VLookup(celda, tabla, 1, False) & vbCr & _
                        "> PROVINCIA: " & Application.VLookup(celda, tabla, 2, False) & vbCr & _
                        "> CAPITAL: " & Application.VLookup(celda, tabla, 3, False), _
                Buttons:=vbInformation, _
                Title:="CLAVE EXTERNA DE LA TABLA"
      ' En caso contrario...
      Else
      End If
   Next columna

End Sub

Así funciona correctamente. Pero, ¿qué pasa? Que este es un ejemplo sencillo de lo que quiero conseguir. En mi aplicación de Excel de verdad utilizo 200 tablas diferentes con 50 campos cada una de ellas (por exagerar un poco).

El problema con el código anterior es que tan sólo es válido para la tabla anterior, así que me gustaría desarrollar un código más genérico y optimizado al mismo tiempo. Es decir, incluir todo en un array o en una lista, y mostrar toda esa información en tan sólo 4 o 5 líneas de código.

Os adjunto el fichero para las pruebas."

Espero haberme explicado bien, cualquier duda por favor comenten.

Agradecería cualquier tipo de aporte que me pueda ayudar a enfocar el problema.

Muchas gracias por vuestro tiempo.

Saludos.

PRUEBAS.xlsm

jesuspg · el 12 de marzo

Es decir, si el usuario hace click en el valor de la celda "2" del campo "FK TABLA" en la tabla de "RESULTADOS", que ese evento APUNTE directamente al registro de la clave primaria de la "TABLA" (Valores: 2, ARAGON, ZARAGOZA) y muestre toda esa información en un msgbox a través de un array o lista de valores.

Se me ocurre algo como la siguiente:

Option Base 1

...

Dim ultimaColumna As Byte
ultimaColumna = ThisWorkbook.Sheets("TABLA").Cells(1, 10000).End(xlToLeft).Column

Dim registro As Variant
registro = Array(1 To UBound(ultimaColumna))

For each registro to ultimaColumna
	Msgbox registro[i]
Next registro

Gerson Pineda · el 12 de marzo

Hola Jesus

Si lo entiendo bien, no es tan complicado

Primero lo que debes apuntar es al nombre de la hoja aquí, el nombre de esa hoja enciérralo en una variable y reemplázalo por ese nombre estático "RESULTADOS"

ThisWorkbook.Sheets("RESULTADOS")

Además es mejor que utilices la función FIND por VLOOKUP es mas potente y versátil

Saludos

JasallBcn · el 12 de marzo

Buenas, si entiendo quieres un codigo reutilizable. como ejemplo puedes pasar parametros a la consulta en cada llamada a la tabla direfente.

Edito: Es lo que dice @Gerson Pineda, no vi que te contesto el antes.

Tablas_v1.xlsm

jesuspg · el 12 de marzo

Hace 22 minutos , JasallBcn dijo:

Buenas, si entiendo quieres un codigo reutilizable. como ejemplo puedes pasar parametros a la consulta en cada llamada a la tabla direfente.

Edito: Es lo que dice @Gerson Pineda, no vi que te contesto el antes.

Tablas_v1.xlsm 28.44 kB · 1 descarga

Hace 2 horas, Gerson Pineda dijo:
Hola Jesus

Si lo entiendo bien, no es tan complicado

Primero lo que debes apuntar es al nombre de la hoja aquí, el nombre de esa hoja enciérralo en una variable y reemplázalo por ese nombre estático "RESULTADOS"
ThisWorkbook.Sheets("RESULTADOS")
Además es mejor que utilices la función FIND por VLOOKUP es mas potente y versátil

Saludos

Hola de nuevo, muchísimas gracias por vuestros comentarios.

El problema es que quiero replicar esto mismo a otras tablas, en las que el número de campos es diferente.

Lo explico a continuación en un vídeo:

El fichero contiene tres tablas (AUTONOMIAS, DATOS y EQUIPOS), cada una de ellas con un número de campos diferentes. Si el usuario hace click en la clave externa, entonces se le muestra por pantalla toda la información referente a "ese registro" en concreto.

Me gustaría crear un procedimiento que primero busque el valor de "ese registro" por una función VLOOKUP, después apuntar a todo ese registro almacenándolo en una variable Array o una lista haciendo un resize hasta la ultima columna de cada tabla, luego recorrer la variable Array que contiene todos los valores de "ese registro" con un bucle, y finalmente mostrar toda esa información en un cuadro MsgBox.

Espero haberme explicado bien.

Lo que quiero evitar es precisamente trabajar con este código tan farragoso, y evitar tener que corregir manualmente cada uno de los datos contenidos en las tablas:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ' Limpiamos los errores anteriores...
   Err.Clear

   ' Tratamos los errores...
   On Error Resume Next

   ' Declaramos las variables de ámbito local...
   Dim columna As Byte, ultimaColumna As Byte
   columna = 1
   ultimaColumna = ThisWorkbook.ActiveSheet.Cells(1, 10000).End(xlToLeft).Column
   Dim celda As Range, autonomias As Range, datos As Range
   Set celda = Selection
   Set autonomias = ThisWorkbook.Sheets("AUTONOMIAS").Cells(1, 1).CurrentRegion
   Set datos = ThisWorkbook.Sheets("DATOS").Cells(1, 1).CurrentRegion

   ' Recorremos las columnas de la tabla...
   For columna = 1 To ultimaColumna
      ' Si el nombre de la columna coincide, entonces...
      If ThisWorkbook.ActiveSheet.Cells(1, columna) = "FK AUTONOMIA" And celda.Column = columna Then
         ' Notificamos al usuario con un mensaje de información...
         MsgBox Prompt:="> PK AUTONOMIA: " & Application.VLookup(celda, autonomias, 1, False) & vbCr & _
                        "> AUTONOMIA: " & Application.VLookup(celda, autonomias, 2, False) & vbCr & _
                        "> CAPITAL: " & Application.VLookup(celda, autonomias, 3, False), _
                Buttons:=vbInformation, _
                Title:="CLAVE EXTERNA DE LA TABLA AUTONOMIAS"
      ' Si el nombre de la columna coincide, entonces...
      ElseIf ThisWorkbook.ActiveSheet.Cells(1, columna) = "FK DATO" And celda.Column = columna Then
         ' Notificamos al usuario con un mensaje de información...
         MsgBox Prompt:="> PK DATO: " & Application.VLookup(celda, datos, 1, False) & vbCr & _
                        "> FK AUTONOMIA: " & Application.VLookup(celda, datos, 2, False) & vbCr & _
                        "> NUMERO DE HABITANTES: " & Application.VLookup(celda, datos, 3, False) & vbCr & _
                        "> SUPERFICIE: " & Application.VLookup(celda, datos, 4, False) & vbCr & _
                        "> DENSIDAD DE POBLACION: " & Application.VLookup(celda, datos, 5, False), _
                Buttons:=vbInformation, _
                Title:="CLAVE EXTERNA DE LA TABLA DATOS"
      ' En caso contrario...
      Else
      End If
   Next columna

End Sub

Adjunto el nuevo fichero para iniciar pruebas.

Muchas gracias por vuestro tiempo y dedicación.

PRUEBAS.xlsm

Gerson Pineda · el 14 de marzo

No te referiste en nada a mi sugerencia

En resumen lo que puedes hacer es utilizar los títulos de la tabla para apuntar al nombre de la hoja [o también podrías capturar el nombre de la tabla, pero siempre tomándolo del titulo]

Ya con eso, solo es cosa de buscar en la columna A la fila, posteriormente concatenar las columnas que te interesan para armar el mensaje

Vamos inténtalo!

Saludos

jesuspg · el 28 de marzo

En 14/3/2022 at 22:10 , Gerson Pineda dijo:

No te referiste en nada a mi sugerencia

En resumen lo que puedes hacer es utilizar los títulos de la tabla para apuntar al nombre de la hoja [o también podrías capturar el nombre de la tabla, pero siempre tomándolo del titulo]

Ya con eso, solo es cosa de buscar en la columna A la fila, posteriormente concatenar las columnas que te interesan para armar el mensaje

Vamos inténtalo!

Saludos

Hola @Gerson Pineda, disculpa por la tardanza en responder. He estado trabajando en ello. Por aquí adjunto la solución. Si tienes cualquier comentario que realizar sobre el programa, acepto cualquier aporte. Muchas gracias por tu tiempo.

PRUEBAS.xlsm

Gerson Pineda · el 29 de marzo

Hola Jesus

¡Estupendo que lo hayas solucionado!

Saludos

jesuspg · el 29 de marzo

Hace 7 horas, Gerson Pineda dijo:

Hola Jesus

¡Estupendo que lo hayas solucionado!

Saludos

Gracias.

Un último apunte que quiero realizar es que si el valor que se está buscando con la función Find en otra tabla está formateado, entonces no lo encuentra. Por ejemplo, si busco el valor "67", pero en otra tabla está formateado como "00067", no lo va encontrar y por tanto no devuelve nada en absoluto.

Gerson Pineda · el 30 de marzo

En 29/3/2022 at 4:10 , jesuspg dijo:

Gracias.

Un último apunte que quiero realizar es que si el valor que se está buscando con la función Find en otra tabla está formateado, entonces no lo encuentra. Por ejemplo, si busco el valor "67", pero en otra tabla está formateado como "00067", no lo va encontrar y por tanto no devuelve nada en absoluto.

Al decir formateado asumo que es desde formato personalizado, pero si es "puro" texto, entonces debes sustituir esos "ceros" por vacíos

Aunque con la función FIND, tienes la posibilidad de buscar "parte" del criterio

Fíjate en el 4to. argumento LookAt para que adaptes a tu caso

Método Range.Find (Excel) | Microsoft Docs

Saludos

jesuspg · el 30 de marzo

Hace 12 minutos , Gerson Pineda dijo:

Al decir formateado asumo que es desde formato personalizado, pero si es "puro" texto, entonces debes sustituir esos "ceros" por vacíos

Aunque con la función FIND, tienes la posibilidad de buscar "parte" del criterio

Fíjate en el 4to. argumento LookAt para que adaptes a tu caso

Método Range.Find (Excel) | Microsoft Docs

Saludos

Si, si. Perdón. Me refería al formato personalizado: (Por ejemplo) VBA > Format(ActiveCell, "00000"), de manera que el valor '1' pase a ser el valor '00001'.

Buena idea "LookAt:= xlPart", pero el problema con ello es que si busco por ejemplo el valor '1' me va a buscar el primer '1' que encuentre dentro del rango, por ejemplo '00111'. Y no es lo mismo el valor '1' que el '00111'. No sé si me explico.

Simplemente, estamos dialogando. Esta vaina ya la solucioné, pero me gusta conocer la opinión de aquellos que tienen mucha más experiencia que yo dentro de la programación con VBA.

Gracias por tu tiempo.

Gerson Pineda · el 30 de marzo

Hace 2 horas, jesuspg dijo:

Me refería al formato personalizado: (Por ejemplo) VBA > Format(ActiveCell, "00000"), de manera que el valor '1' pase a ser el valor '00001'.

El formato personalizado es solo una mascara, por lo que debía funcionar así: