VBA: Aadir imgenes a los controles de Excel. Hoy veremos algo sencillo pero muy vistoso, especialmente para dar ese toque personal y/o profesional a nuestros controles (sobre nuestro Formularios de usuario-UserForm).Veremos la forma de aadir una imagen a nuestros controles (Botones-CommandButton, etc.)

Lo primero que aprenderemos ser a buscar imgenes interesantes.. para ello podemos buscar directamente en Google > Imgenes... e introducir la temtica deseada.

Por ejemplo 'relojes', y tras aplicar algunos criterios de bsqueda, slo tendremos que buscar imgenes del tamao adecuado (no muy grandes!!) y sobre todo asegrate que tengan la extensin .gif, jpg o .bmp

Una vez encuentres aquel que te guste (y cumpla las condiciones), bastar que con el botn derecho del ratn sobre la imagen, presiones Guardar imagen como... e indiques el nombre y ruta donde guardarla...Recurdala porque en breve tendremos que recuperarla...

Por supuesto hay mil sitios donde encontrar imgenes. Por poner un par de ejemplos:www.iconarchive.comhttp://findicons.comincluso a sitios web donde descargarte packs de iconos...

Lo que nos interesa, una vez tenemos la/las imgenes descargadas, accederemos a nuestro control (un CommandButton en nuestro ejemplo) dentro de un formulario, e iremos a la ventana de sus Propiedades:

En concreto nos fijaremos en dos de sus propiedades:PicturePicturePosition (con 12 posiciones de la imagen respecto del Texto del control)Para cargar la imagen simplemente presionaremos los tres puntos de la propiedad Picture y en el explorador buscaremos la imagen que deseemos insertar...

Lo interesante es la posibilidad de esas 12 posiciones relativas de la imagen dentro del control...Saber tambin que la imagen queda dentro del fichero y que no guarda ningn vnculo que la ruta de carga de la imagen.VBA: Cambiar nombres de ficheros con la instruccin NAME. La idea de hoy es cambiar el nombre de un fichero empleando una sencilla macro, basada en la instruccin poco conocida Name.Deberemos saber que esta instruccin Name nos permite cambiar el nombre de un archivo y moverlo a un directorio o carpeta diferente (si as lo deseamos).Tendremos cuidado, ya que Name puede mover un archivo a travs de unidades pero slo puede cambiar el nombre a un directorio o carpeta existente cuando tanto 'el nombre de la ruta antigua' como 'el nombre de la ruta nueva' estn ubicados en la misma unidad.Importante: Name no puede crear un nuevo archivo, directorio o carpeta!!!.

Por ltimo, un par de limitaciones ms...si utilizamos la instruccin Name con un archivo abierto se producir un error.Los argumentos de la instruccin Name no pueden incluir caracteres comodn mltiples (*) ni sencillos (?).

La gran ventaja de esta instruccin es que con sta podemos cambiar nombres de archivos sin abrirlos (como requerira, por ejemplo, el mtodo Workbook.SaveAs)

Conocidas entonces las ventajas e inconvenientes podemos trabajar con ella

La idea del ejercicio de hoy es renombrar los ficheros contenidos en una carpeta/directorio ('E:\excelforo\Cambios'):

Tenemos en la Hoja1 de nuestro fichero 'base' (un tercer fichero independiente), donde hemos incluido nuestra macro, la correspondencia entre los viejos y nuevos nombres:

Con esta hiptesis de trabajo, podemos disponer de nuestro procedimiento, dentro del proyecto de VBA de nuestro Libro de trabajo, dentro del Editor de VB, en un mdulo, insertamos el siguiente cdigo:view plaincopy to clipboardprint?1. SubCambiarNombres()2. DimNombreViejoAsString3. DimNombreNuevoAsString4. 5. 'recorremoslasceldasA2:A5conlosviejosnombres6. ForEachficheroInRange("A2:A5")7. 'asignamosvaloresalasvariables,conelviejoynuevonombre8. NombreViejo="E:\excelforo\Cambios\"&fichero.Value&".xlsx"9. NombreNuevo="E:\excelforo\Cambios\"&fichero.Offset(0,1).Value&".xlsx"10. 'conlainstruccinNameprocedemosalcambiodenombredeunarchivo,directorioocarpeta.11. NameNombreViejoAsNombreNuevo12. Nextfichero13. EndSubSub CambiarNombres()Dim NombreViejo As StringDim NombreNuevo As String

'recorremos las celdas A2:A5 con los viejos nombresFor Each fichero In Range("A2:A5") 'asignamos valores a las variables, con el viejo y nuevo nombre NombreViejo = "E:\excelforo\Cambios\" & fichero.Value & ".xlsx" NombreNuevo = "E:\excelforo\Cambios\" & fichero.Offset(0, 1).Value & ".xlsx" 'con la instruccin Name procedemos al cambio de nombre de un archivo, directorio o carpeta. Name NombreViejo As NombreNuevoNext ficheroEnd Sub

Tras ejecutarlo, conseguimos nuestro objetivo...

Los nombres de los ficheros han sido reemplazados por los nuevos.Usar el Tabulador para desplazarse por la hoja de clculo. Al hilo de la entrada anterior, veremos cmo emplear el Tabulador para desplazarse en horizontal.. y cmo cambiar de fila a la izquierda.

El asunto es sencillo pero prctico, queremos desplazarnos sobre una hoja de clculo introduciendo ciertos valores (si fuera el caso), pero tambin queremos que al cambiar de fila retrocedamos al inicio o izquierda... Por ejemplo, queremos movernos en horizontal entre las columnas A:F, y que al llegar al lmite derecho (columna F) el carro nos retorno a la columna A, y la fila siguiente (una fila ms abajo).

La accin es bien simple, no situaremos en la celda A1 y tras introducir el dato NO presionaremos Enter sino Tabulador, y al llegar al final de la fila (celda F1) y slo en ese caso, presionaremos Enter, lo que nos llevar a la celda B2.Vemoslo en el siguiente video:

Una combinacin recomendada (Tabulador + Enter) cuando debemos introducir manualmente muchos datos, y queremos despreocuparnos de los saltos de fila...Mover seleccin despus de presionar Enter. Un usuario, a travs de un comentario, preguntaba la forma de moverse dentro de un rango y saltar de fila al llegar a una celda presionando slo el Enter.La idea es poder introducir datos directamente en un rango de celdas (por ejemplo B3:G7) y que presionando Enter, al llegar a la ltima columna (columna G), salte tras validar con Enter a la fila siguiente y la columna B.Por ejemplo, tras introducir un valor en G3, y presionar Enter salte a la celda B4...

Debemos tomar dos acciones. La primera consiste desde la Ficha Archivo, entrar en las Opciones de Excel > Avanzadas > Seccin Opciones de Edicin > Despus de presionar Enter, mover seleccin Direccin:

Seleccionaremos la opcin de Direccin: Derecha

Segunda accin a tomar. Seleccionar el rango continuo donde queremos introducir informacin (rango B3:G7). Comenzaremos situndonos en B3 y cada vez presionemos Enter, nos desplazaremos hacia la derecha en horizontal!!, hasta llegar al lmite derecho de nuestra seleccin (columna G), en cuyo momento, saltaremos a la primera columna (columna B) y fila siguiente...Lo vemos en el vdeo siguiente:

Suma condicional tridimensional 3D sin macros. Frecuentemente se me pregunta por la manera de realizar sumas condicionadas sobre diferentes hojas, y siempre les dirijo hacia esta entrada del blog:VBA: Una funcin personalizada de frmula 3D.Y es que la teora nos dice que la funcin SUMAR.SI o SUMAR.SI.CONJUNTO y sus hermanas para contar o promediar, no admiten el mtodo 'tridimensional' (como se explica en el link anterior).

El trabajo a realizar en nuestra hoja de clculo (sin macros) es, en esencia, similar al procedimiento detallado en el post de ms arriba.Lo que haremos ser, mediante matriciales, recorrer y sumar de manera condicional, cada hoja que indiquemos.

Comencemos con el desarrollo viendo las hojas a sumar: tenemos en nuestro ejemplo tres hojas: DptoA, DptoB y DptoC con unos orgenes de estructura idntica, tres columnas de datos: Mes, Comercial e Importe:

En la hoja Resumen llegaremos, con una sola frmula a:

haz clic en la imagen

El primer paso fundamental es crear un Nombre definido con una Constante matricial que represente los nombres de las hojas que queremos sumar. En mi ejemplo:Hojas ={"DptoA";"DptoB";"DptoC"}puesto que son esas tres hojas sobre las que quiero trabajar.

El siguiente paso, y ltimo, es generar nuestra frmula.. si nos situamos en la celda C3 de la hoja 'Resumen' escribiremos:=SUMA(SUMAR.SI.CONJUNTO(INDIRECTO(Hojas&"!C2:C13");INDIRECTO(Hojas&"!A2:A13");$B3;INDIRECTO(Hojas&"!B2:B13");C$2))y ejecutaremos matricialmente!!! (presionando Ctrl+Mayusc+Enter en lugar de solo Enter).

Si nos fijamos tenemos una 'doble suma', ya que hemos anidado un SUMAR.SI.CONJUNTO en una funcin SUMA.El truco consiste en aplicar un SUMAR.SI.CONJUNTO sobre cada hoja de las descritas en el nombre definido 'Hojas' de manera independiente, y luego al ser una frmula matricial, el resultado de cada hoja se acumula con la funcin SUMA... es decir, es como si hiciramos:=SUMAR.SI.CONJUNTO(DptoA!$C$2:$C$13;DptoA!$A$2:$A$13;$B3;DptoA!$B$2:$B$13;C$2)+SUMAR.SI.CONJUNTO(DptoB!$C$2:$C$13;DptoB!$A$2:$A$13;$B3;DptoB!$B$2:$B$13;C$2)+SUMAR.SI.CONJUNTO(DptoC!$C$2:$C$13;DptoC!$A$2:$A$13;$B3;DptoC!$B$2:$B$13;C$2)

Interesante ver cmo hemos construido, con la funcin INDIRECTO, los rangos sobre los que trabajar, por ejemplo:INDIRECTO(Hojas&"!C2:C13")que devolvera convertido en referencia de Excel los rangos:DptoA!C2:C13DptoB!C2:C13DptoC!C2:C13VBA: Comprobar si una hoja est o no protegida. Hoy veremos una pequea utilidad con la instruccin .ProtectContents, que detecta si la Hoja est o no protegida...

Para completar un poco este mtodo tan sencillo, tomaremos algunas acciones segn la respuesta a la pregunta:1 mostraremos un Mensaje advirtiendo de la situacin2 cambiaremos el color de fondo de la celda B13 cambiaremos el color de la pestaa de la hoja

Todo lo asociaremos al evento _Activate de la hoja de trabajo.

Por tanto, en la ventana de cdigo de la hoja que nos interese... asocindolo a un evento _Activate, es decir, cuando activemos la hoja de trabajo (en mi ejemplo la Hoja1):view plaincopy to clipboardprint?1. PrivateSubWorksheet_Activate()2. 'comprobamossilahojaencuestintieneactivadolaproteccin3. IfHoja1.ProtectContents=TrueThen4. 'encasoafirmativo(estprotetida)5. 6. 'desprotegemoslahojapararealizarlasaccionessiguientes7. Hoja1.Unprotect8. 'laceldaB1laponemosdecolorRojo9. 'ycambiamoselcolordelapestaadelahojaaRojo10. Hoja1.Range("B1").Interior.color=vbRed11. Hoja1.Tab.color=vbRed12. 'marcamosunmensaje13. MsgBox"LaHojaestProtegida."14. 'finalizamosdejandolahojaprotegida..comoestaba15. Hoja1.Protect16. Else17. 'encasoafirmativo(estdesprotegida)18. 'laceldaB1laponemosdecolorVerde19. 'ycambiamoselcolordelapestaadelahojaaVerde20. Hoja1.Range("B1").Interior.color=vbGreen21. Hoja1.Tab.color=vbGreen22. 'marcamosunmensaje23. MsgBox"LahojaestDesprotegida."24. EndIf25. EndSubPrivate Sub Worksheet_Activate()'comprobamos si la hoja en cuestin tiene activado la proteccinIf Hoja1.ProtectContents = True Then 'en caso afirmativo (est protetida) 'desprotegemos la hoja para realizar las acciones siguientes Hoja1.Unprotect 'la celda B1 la ponemos de color Rojo 'y cambiamos el color de la pestaa de la hoja a Rojo Hoja1.Range("B1").Interior.color = vbRed Hoja1.Tab.color = vbRed 'marcamos un mensaje MsgBox "La Hoja est Protegida." 'finalizamos dejando la hoja protegida.. como estaba Hoja1.ProtectElse 'en caso afirmativo (est desprotegida) 'la celda B1 la ponemos de color Verde 'y cambiamos el color de la pestaa de la hoja a Verde Hoja1.Range("B1").Interior.color = vbGreen Hoja1.Tab.color = vbGreen 'marcamos un mensaje MsgBox "La hoja est Desprotegida."End IfEnd Sub

Podemos comprobar cmo al activar (entrar) en la Hoja1, se dispara el evento y en consecuencia el control con .ProtectContents:

O bien..

Un truco para la Suma tridimensional en diferentes hojas... Hoy contar un pequeo truco que sirve para sumar el valor de una celda o rango de diferentes hojas de clculo.. esto es, hablar de una frmula tridimensional.Veremos cmo de una manera muy sencilla puede conseguir operar sobre todas las hojas de clculo del libro excepto con la de la hoja activa.

Tiempo atrs ya cont algunas cosas de estas frmulas 3D:ver ejemplo 1yver ejemplo2

Comenzaremos este sencillo procedimiento mostrando datos en la celda B2 de las tres hojas de mi Libro de trabajo de Excel:

Vemos valores de 100, 200 y 300 para las Hojas1, 2, y 3 respectivamente...La idea es, a continuacin, sumar todos los valores de la celda B2 de todas las hojas salvo la de la hoja donde introduzco la frmula tridimensional (en mi ejemplo Hoja2):=SUMA('*'!B2)fijmonos de la forma en que lo he escrito, en lugar de un rango u hoja, introduzco un asterisco entre apostrofes!!!

El resultado era el comentado, la suma del valor de B2 de la Hoja1 y de B2 de la Hoja3, adems Excel se ha encargado de transformar el literal transcrito a:=SUMA(Hoja1!B2;Hoja3!B2)

Con ms hojas el resultado final de la frmula, en cuanto a su forma final es ms clara:=SUMA(Hoja1!B2;Hoja3:Hoja5!B2)Formato de celda: Oculta. El tema de hoy dira que no es algo muy habitual en nuestras hojas de trabajo, pero a veces conviene recordar funcionalidades de Excel.Hablar de la opcin de Ocultar celda dentro de los formatos de celdas.Accederemos a esta opcin desde Formato de celda > pestaa Proteger > opcin Oculta

Recordar que esta opcin slo ser hbil cuando nuestra hoja de trabajo est protegida (al igual que Bloquear celda).

Veamos el efecto de sta sobre el siguiente ejemplo. En el rango F3:G5 he incluido una serie de frmulas, que resumen la informacin del rango A1:C10.

Con la configuracin normal, al seleccionar cualquier celda con valores o frmulas, podremos visualizarlas en la barra de frmula:

Se observa claramente como aparece visible en la Barra de frmulas la funcin insertada en dicha celda.

Ahora cambiaremos y marcaremos la opcin Ocultar, tras seleccionar el rango F3:G5, y a continuacin Protegeremos la hoja (desde la ficha Revisar > grupo Cambios > botn Proteger hoja).Si nos situamos encima de alguna de esas celdas del rango F3:G3 y nos fijamos en nuestra Barra de frmulas comprobaremos la diferencia... no vemos nada.

En cualquier otra celda, fuera del rango 'ocultado', veramos el contenido (valor o funcin) en nuestra Barra de frmulas.La funcin IMPORTARDATOSDINAMICOS en Tablas dinmicas. Profundizaremos en la posibilidad de trabajar con datos a partir de informacin resumida mediante Tablas dinmicas, se trata en realidad de obtener valores de una Tabla dinmica mediante el uso de la funcin IMPORTARDATOSDINAMICOS.Muy importante recordar que para emplear esta funcin debemos activar desde las Opciones de tabla dinmica la herramienta Generar GetPivotData!!!.

Veamos un ejemplo prctico, partiremos del siguiente listado que representa la recogida de efectivo (monedas) de una pequea tienda de 'chuches' en diferentes das, y a partir de este listado hemos construido una tabla dinmica con la configuracin que se ve en la imagen siguiente:

Observemos que he optado por modificar los nombres de los elementos resumidos.El diseo ha consistido en llevar el campo 'Fecha' al rea de filtro de pgina y los campos '2 eur', '1 eur', etc al rea de valores, resumido por Suma, adems he recolocado la distribucin de los valores por filas.

Bien, la meta es trabajar sobre estos datos para determinar cul es el total de euros ingresado en un da cualquiera (por ejemplo el 21/1/2014), sabiendo el nmero de monedas para cada valor.En este caso trabajaremos en un rango anexo al de la tabla dinmica, a la izquierda para evitar futuras superposiciones y borrado de datos.

Veamos la frmula completa que nos resolver el problema:=SI(DERECHA(N6;3)="eur";IMPORTARDATOSDINAMICOS(TEXTO(N6;"@");$N$5)*VALOR(IZQUIERDA(N6;ENCONTRAR("_";N6)-1));IMPORTARDATOSDINAMICOS(TEXTO(N6;"@");$N$5)*VALOR(IZQUIERDA(N6;ENCONTRAR("_";N6)-1)/100))

haz clic en la imagen

La frmula es un poco larga, pero sencilla de entender por partes.Lo ms importante, y objetivo de esta entrada:IMPORTARDATOSDINAMICOS(TEXTO(N6;"@");$N$5)

con esta funcin nos referimos a la tabla dinmica situada a partir de $N$5 y en concreto al dato que corresponde con el primer argumento:TEXTO(N6;"@")es decir, al valor resumido correspondiente al elemento '2_ eur'... y ojo con el tratamiento que le hemos dado, para que Excel comprenda que nos referimos al elemento '2_ eur' (o cualquier otro) forzamos con la funcin TEXTO y un formato "@".Esto nos permitir arrastrar nuestra frmula aprovechndonos de los elementos ya definidos en la tabla dinmica.

El resto de la frmula es un condicional SI que distingue con funciones de texto si el valor facial de la moneda a estudio es de euros o cntimos, realizando el producto por el valor facial correspondiente.

Finalmente slo nos quedara sumar el resultado de nuestras frmulas...VBA: SELECT CASE para distribuir datos entre hojas. En ms de una ocasin he expuesto usos de la instruccin SELECT CASE, y en esta ocasin (cuando las posibilidades de eleccin son altas), motivado por la pregunta de un lector, volver a dar una explicacin de cmo emplearlo:Realice una Macro para un Formulario de introduccin de datos Codigo-Nombre-Direccin-Telefono-Email y 5 hojas con los mismos campos. El cdigo se refiere a nombre de la calle de la direccin(BO-VE-FE-FR-BE) ese yo lo introduzco en el campo CODIGO, tengo 5 hojas con diferente nombre de calle (BONELLI-VENERE-FERRATO-FERRARI-BENEDETTI) y una con el nombre FORMULARIO donde se ingresan los datos, Necesito que al introducir los datos en este formulario se transfieran a la hoja correspondiente de acuerdo al cdigo.

Esta vez se trata de dirigir datos registrados en una plantilla (una de nuestras hojas de clculo llamada 'Formulario') hacia diferentes hojas del mismo libro, segn corresponda al cdigo introducido.

Veamos el asunto algo ms claro. Tenemos una primera hoja con datos introducidos, en cuyo primer campo (columna A) aparecen ciertos cdigos que corresponden a las hojas de color verde siguientes:

En la imagen vemos, a modo de ejemplo, dos registros con dos cdigos distintos FR y CO... lo que deber llevarse a dos hojas distintas: FR a la hoja 'Ferrari' y BO a la hoja 'Bonelli'.

Para ello insertamos un mdulo en nuestro proyecto VBA con el siguiente cdigo:view plaincopy to clipboardprint?1. SubTraspaso()2. DimfilaAsLong,ultfilaAsLong3. DimrngAsString4. 5. 'recorremoslosdiferentesregistrosdelFormulario6. ForEachceldaInRange("TblDatos[Codigo]")7. fila=celda.Row8. rng="A"&fila&":"&"E"&fila9. Setorigen=Sheets("Formulario").Range(rng)10. 'identificamoslahojadestino,segn'Cdigo'11. SelectCasecelda.Value12. Case"BO":Setdestino=Sheets("Bonelli")13. Case"VE":Setdestino=Sheets("Venere")14. Case"FE":Setdestino=Sheets("Ferrato")15. Case"FR":Setdestino=Sheets("Ferrari")16. Case"BE":Setdestino=Sheets("Benedetti")17. CaseElse:MsgBox"Cdigonovlido":ExitSub18. EndSelect19. 20. 'copiamoselregistroalahojadestino21. origen.CopyDestination:=destino.Range("A"&Rows.Count).End(xlUp).Offset(1,0)22. Nextcelda23. EndSubSub Traspaso()Dim fila As Long, ultfila As LongDim rng As String

'recorremos los diferentes registros del FormularioFor Each celda In Range("TblDatos[Codigo]") fila = celda.Row rng = "A" & fila & ":" & "E" & fila Set origen = Sheets("Formulario").Range(rng) 'identificamos la hoja destino, segn 'Cdigo' Select Case celda.Value Case "BO": Set destino = Sheets("Bonelli") Case "VE": Set destino = Sheets("Venere") Case "FE": Set destino = Sheets("Ferrato") Case "FR": Set destino = Sheets("Ferrari") Case "BE": Set destino = Sheets("Benedetti") Case Else: MsgBox "Cdigo no vlido": Exit Sub End Select

'copiamos el registro a la hoja destino origen.Copy Destination:=destino.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)Next celdaEnd Sub

A su vez, en la ventana del cdigo asociado a la hoja 'Formulario', para automatizar el proceso 'Traspaso' incluimos un evento _Deactivate, para que al salir de la hoja 'formulario' realice el traspaso de informacin:view plaincopy to clipboardprint?1. PrivateSubWorksheet_Deactivate()2. CallTraspaso3. EndSubPrivate Sub Worksheet_Deactivate() Call TraspasoEnd Sub

En este caso lo interesante no es el paso de copiado de registro, si no cmo identificamos el valor del 'Codigo' y mediante SELECT CASE los dirigimos a la hoja correcta, definiendo una variable con objeto... lo que facilita posteriormente su uso par el pegado en la hoja destino.

Permitir editar directamente en las celdas de Excel. Probablemente te sorprenda el ttulo de esta entrada del blog: Permitir editar directamente en las celdas... y quiz te sorprenda mucho ms si te cuento que esta opcin de Excel nos permite habilitar/deshabilitar la posibilidad de convertir nuestras celdas vinculadas o referenciadas en unos 'hipervnculos muy especiales', en cuyas celdas bastar hacer doble clic para ir a esa referencia o vnculo origen; incluso nos abrir el Libro de trabajo origen si estuviera cerrado!!.Por defecto esta opcin se encuentra marcada, y por tanto al hacer doble clic sobre una celda, con o sin vnculo, la accin resultante es precisamente que se nos permite editar dicha celda.Ojo con la contrapartida, ya que si optamos por deshabilitar esta opcin, es decir, si decidimos trabajar a modo de 'hipervnculo', slo podremos modificar el contenido de las celdas en la barra de frmulas.

La siguiente cuestin es desde donde podemos configurar esta opcin?. Obvio, desde la ficha Archivo > Opciones de Excel > Avanzadas > seccin Opciones de edicin:

Comprobemos en este video el efecto de desmarcar esta opcin:

Sin duda cmodo y fcil en determinadas ocasiones.