imagenes, comentarios y autofiltros

Si existe un tema bastante recurrente en las consultas es el del trabajo con imágenes desde nuestro querido Excel. No en lo que refiere al tratamiento "fotográfico" de ellas, ya que no es Photoshop (aunque se pueden lograr efectos interesantes), pero si en lo concerniente a colocar fotos de empleados, imágenes de tapas de discos, etc, etc, etc. En el blog encontrarán muchas entradas realizadas al respecto, aunque esta nueva pregunta merece su post, ya que difiere un poco de las demás y plantea una situación distinta: mi lector posee una tabla en la que guarda datos de los empleados de la empresa: legajo, apellido, nombre y demás. Entre esos "demás" quiere almacenar en una celda la foto del empleado. La cuestión pasa por que luego utiliza autofiltros..... y necesita que al filtrar la tabla quede la imagen de agente en cuestión.

El problema es que si coloco fotos en las celdas y luego aplico autofiltro los resultados pueden ser bastante indeseados, aunque haremos un proyecto alternativo y utilizaremos los sencillos comentarios de celdas.

Si, algo tan simple como insertar un comentario.

ahá... los comentarios pueden contener imágenes, aunque sea una de sus características menos conocidas

[+/-] Ver el resto / Ocultar

Insertamos un comentario en la celda y sobre la misma hacemos click con el botón derecho del mouse, para seleccionar luego "modificar comentario". De allí seguimos estos pasos:

1) Click botón derecho sobre uno de los lados del comentario.

2) Elegimos "formato de comentario"

3) Vamos a la pestaña "Colores y líneas" del formulario emergente

4) Desplegamos la lista "Color"

5) Clickeamos sobre "efectos de relleno"

6) Seleccionamos la pestaña "Imagen"

7) Ahora click en "Seleccionar imagen"

8) Buscamos la imagen en el ordenador

9) Aceptar / Aceptar.

Si llegamos con vida al punto 8, desde aquí seleccionamos la imagen:

el resultado se aprecia en la primer imagen que subí al post.

Si a estos 9 pasos debemos repetirlos en una empresa.... casi que nos volveríamos locos. Imaginen una plantilla de 40, 60 o 200 empleados y ya tendrán un panorama aproximado. Lo bueno es que esta forma de trabajo puede ser automatizada desde VBA, haciendo que todo resulte muy fácil al usuario final de nuestro proyecto.

Antes de seguir, dijimos, tenemos una tabla con datos de los empleados. No me esmeré mucho en hacerla, pero servirá para la tarea a desarrollar:

El objetivo será lograr que al agregar un nuevo agente (específicamente al ingresar su número de legajo) una macro inserte un comentario con imagen en la tercer columna. Así será luego factible filtrar la tabla como se nos ocurra y que siempre en dicha columna aparezcan las fotos de los empleados. Empecemos.

En primer lugar debemos crear un directorio específico en donde guardaremos las imágenes, para así ir a buscarlas con VBA siempre al mismo lugar. Otro punto a tener en cuenta es que cada foto deberá tener un nombre distinto: aconsejo guardarla con el nombre correspondiente al legajo del empleado; por  lo general ese dato es irrepetible. Mas cosas: altamente recomendable que tengan la misma dimensión (por ej: 200 x 200 píxeles) e idéntico formato (jpg, png, etc, etc), generando así uniformidad al proyecto y sus resultados.

Sigamos. Lo primero a tener en cuenta es que debemos detectar cuando el usuario cambie  el valor de alguna celda de la columna A, que es donde se guardan los legajos. Ya vimos este punto en otras entradas, pero nunca viene mal recordarlo:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("a2:a" & Cells.Rows.Count), Target) Is Nothing Then
    MsgBox Target.Value
End If
End Sub

Uso a Intersect para determinar si la celda cambiante (Target) se encuentra entre el rango A2 (omito los encabezados de tabla) y el total de celdas de la hoja. Si esto sucede, un MsgBox nos mostrará el valor de la celda cambiada:

algo sencillo, pero simple para ver como funciona Intersect.

Vamos a definir las cosas: si algún valor de las celdas que conforman la columna A cambia, almacenaremos el nro de legajo, insertaremos un comentario en la tercer columna, buscaremos la foto de ese legajo en el directorio (que es el propio nro + la extensión jpg) y el trabajo quedará listo.
El evento queda:

Private Sub Worksheet_Change(ByVal Target As Range)
'corroboro que la celda modificada se encuentre dentro de la columna A
If Not Intersect(Range("a2:a" & Cells.Rows.Count), Target) Is Nothing Then
    'si ingresó un valor:
    If Target.Value <> "" Then
        'llamo al sub que colocará la imagen, pasando  como argumento
        'en que fila se realizó la modificación:
        InsertarImagen Target.Row
    End If
End If
End Sub

Notarán que dentro del sub llamo a un procedimiento externo (InsertarImagen), el cual les muestro a continuación:

Sub InsertarImagen(Fila As Long)
Dim Ruta, Foto As String

'armo la ruta de acceso al directorio
Ruta = "C:\Users\damian\Pictures\blogExcel\"
'y ahora armo el nombre de la foto, tomando el nro de
'legajo y añadiendo la extensión:
Foto = Cells(Fila, 1).Value & ".jpg"
'para finalmente crear la ruta de acceso completa:
Ruta = Ruta & Foto

'si no hay ninguna imagen del empleado en el directorio, aviso
'y salgo del procedimiento:
If Dir(Ruta) = "" Then
    MsgBox "No existe foto del agente", vbExclamation
    Exit Sub
End If

'comienza el trabajo
With Cells(Fila, 3)
    .AddComment 'agrego un comentario
    .Comment.Visible = False
    'borro cualquier texto y lo selecciono
    .Comment.Text Text:="" & Chr(10) & ""
    .Select
    .Comment.Visible = True
    .Comment.Shape.Select True 'lo selecciono
End With
With Selection
    'le agrego la imagen:
    Selection.ShapeRange.Fill.UserPicture _
        Ruta
    'y le defino un  ancho y alto
    Selection.Width = 200
    Selection.Height = 200
End With
'oculto el comentario
Cells(Fila, 3).Comment.Visible = False
End Sub

El código de arriba se encuentra bien comentado como para que vayan viendo que hace  cada línea de código. Si ahora voy (por ejemplo) a la fila 3 y allí ingreso el nro de legajo la foto del empleado se  colocará automáticamente:

ja, sin empleados nos agarró.

Y si voy al registro siguiente y coloco el nuevo nro de legajo:

todo marcha sobre ruedas.

Si les paso el archivo tal cual está ahora tendrán varios problemas ya que se pueden producir varios errores, a los cuales debemos controlar, por ejemplo:
a) Si la celda ya tiene un comentario y quiero insertar otro... tira error.
b) Si el usuario al borrar selecciona mas de una celda.... tira error.
c) Si el usuario borra el contenido de una celda de la columna A... deberemos quitar el comentario.

En el código siguiente iremos resolviendo cada uno de los problemas presentes:
Evitar que seleccionen mas de una celda en la columna A, usando el evento SelectionChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'si la seleccion posee mas de una fila o mas de una columna, aviso y selecciono una sola celda
If Not Intersect(Range("a2:a" & Cells.Rows.Count), Target) Is Nothing Then
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
        MsgBox "Solo se puede seleccionar un legajo a la vez", vbCritical
        Cells(Selection.Row, 1).Select
    End If
End If
End Sub

Y así quedaría el código con el controlador de errores listo:

Sub InsertarImagen(Fila As Long)
Dim Ruta, Foto As String

'coloco un controlador de errores:
On Error GoTo salida:

'armo la ruta de acceso al directorio
Ruta = "C:\Users\damian\Pictures\blogExcel\"
'y ahora armo el nombre de la foto, tomando el nro de
'legajo y añadiendo la extensión:
Foto = Cells(Fila, 1).Value & ".jpg"
'para finalmente crear la ruta de acceso completa:
Ruta = Ruta & Foto

'si no hay ninguna imagen del empleado en el directorio, aviso
'y salgo del procedimiento:
If Dir(Ruta) = "" Then
    MsgBox "No existe foto del agente", vbExclamation
    Exit Sub
End If

'comienza el trabajo
With Cells(Fila, 3)
    .AddComment 'agrego un comentario
    .Comment.Visible = False
    'borro cualquier texto y lo selecciono
    .Comment.Text Text:="" & Chr(10) & ""
    .Select
    .Comment.Visible = True
    .Comment.Shape.Select True 'lo selecciono
End With
With Selection
    'le agrego la imagen:
    Selection.ShapeRange.Fill.UserPicture _
        Ruta
    'y le defino un  ancho y alto
    Selection.Width = 200
    Selection.Height = 200
End With
'oculto el comentario
Cells(Fila, 3).Comment.Visible = False

salida:
'si ya existe un comentario con foto:
If Err.Number = 1004 Then
    MsgBox "Por favor primero borre el nro  de legajo y luego vuelva a introducirlo", vbExclamation
    Err.Clear
End If
End Sub

De esta forma nos queda el trabajo terminado y funcional. Si el valor de la celda modificada es igual a "" (vacía) entonces quito el comentario... pero eso lo encontrarán en este archivo, al cual se los dejo con formato .rar con las imágenes del proyecto.
Coloquen puntos de interrupción en el código y con F8 vayan con el "paso a paso" y viendo como cada línea de código va cumpliendo con su tarea.
Ahora cada agente tiene su foto y, aunque filtremos, al pasar el mouse sobre la tercer columna accederemos a las respectivas imágenes.

Suerte.