33 Utilidades para Microsoft Excel

Hoy os presento un manual en pdf, de lo que creo que podrían ser, las 33 mejores utilidades para Microsoft Excel que he publicado en el blog. Quizás algunos de vosotros no estéis de acuerdo, y penséis que hay otros artículos en el blog que deberían incluirse en el manual. Muy probablemente tengáis razón, pero he escogido esas 33 utilidades, después de hacer muchos descartes.

Este manual con 33 utilidades para Microsoft Excel, no pretende ser un manual de cabecera, pero si un manual de consulta, especialmente ideado para aquellos lectores que quieran aprender las posibilidades de las macros en Excel. No obstante, este manual no solo incluye macros, sino que también podréis encontrar en él, funciones propias de Excel, como la TIR, y el VAN, por poner solo dos ejemplos.

El manual, como todo lo que encontrarás en este humilde blog de Excel, es gratuito y de libre distribución, por lo que puedes imprimirlo, enviárselo a tus amigos, compartirlo, y en definitiva, hacer lo que quieras con el :-)

En muchos de los artículos, podréis comprobar que al final de los mismos, hay un enlace para descargar un fichero con todo lo explicado, para que el usuario no tenga que partir de cero escribiendo el código fuente en Excel. Asimismo, se incluye un enlace a la entrada original de este blog, por si en algún momento el lector quiere acercarse hasta aquí, para ver si he realizado algún cambio o modificación en algún artículo del blog, como ha ocurrido recientemente por ejemplo, en el que explico como obtener datos de una página web.

Estas son las utilidades que he incluido en el pdf:

1. Obtener el nombre del archivo.
2. Obtener el nombre de la hoja.
3. Obtener la ruta, el nombre del fichero, y la hoja.
4. Mi primer macro en Excel.
5. Mi primer UserForm.
6. Introducir datos utilizando un formulario.
7. Modificar datos utilizando un formulario.
8. Mi primer ComboBox.
9. Sacándoles provecho a los ComboBox.
10. Macro al abrir o cerrar un libro.
11. Desproteger una hoja de cálculo.
12. Crear carpetas (o directorios), desde Excel.
13. Poner la hora en una celda.
14. Crear hojas con un clic.
15. Buscar hojas ocultas.
16. Mostrar y ocultar hojas, utilizando macros.
17. Leer una base de datos Access.
18. Simultanear filas de colores.
19. Validación con datos en otra hoja.
20. Validación de listas dependientes.
21. Control horario: horas normales y horas extras.
22. Números aleatorios no repetidos.
23. Préstamos y cálculo de hipotecas.
24. Préstamos según el método americano.
25. Préstamos con amortización de capital constante.
26. Calcular la TAE.
27. Calcular la TIR y el VAN.
28. Evolución de un capital a interés simple e interés compuesto.
29. Calcular la letra del NIF/DNI.
30. Controlar vencimientos de facturas y recibos.
31. Calcular vencimientos.
32. Obtener datos de una página web.
33. Calendarios para imprimir.

Aquí os dejo una imagen de una vista a 4 páginas, para que os hagáis una idea de lo que podéis encontrar en el pdf que podéis descargar más abajo:


Ya no os hago esperar más. Aquí tenéis el manual con las 33 utilidades para Microsoft Excel (cliquead en la imagen para descargar el manual en pdf):

Si te ha gustado este manual en pdf, te agradecería que dejases un comentario.

Calendario emergente en Excel

Este artículo nace como respuesta a una consulta de un lector del blog en otro artículo relacionado, donde explicaba como incluir un calendario en un formulario. Concretamente se planteaba la pregunta de si se podía mostrar un calendario emergente en Excel, simplemente cliqueando en una celda.

La respuesta seguro que ya la sabéis, porque sino, no estaría escribiendo este artículo. Pues sí, se puede. En este caso, lo vamos a hacer sin necesidad de incluir el calendario en un formulario. Simplemente haremos que el calendario aparezca, sin más.

Creo que el artículo de hoy os puede ser de mucha utilidad, especialmente si trabajamos con hojas de cálculo en las que tengamos que introducir muchas fechas del mes en curso (del mes en el que estamos), pues con un solo clic en el día del calendario, nos aparecerá la fecha en la celda donde estemos. También lo podemos utilizar para introducir fechas correspondientes a otros meses y/o años, pero ya no será tan rápida la introducción de los datos, pues deberemos realizar más de un clic en el calendario (para seleccionar el mes y/o año, y el día). Puede ser interesante por ejemplo, como complemento llevar el control mensual de cobros y pagos, para hacer un presupuesto diario, o para llevar el control de asistencias del personal, por poner solo tres casos.

Para poder hacer todo esto, lo primero que tendremos que hacer es "dibujar" el calendario. Ojo, con esto de dibujar, me estoy refiriendo simplemente, a darle al calendario las dimensiones que deseemos. Para ello, si usamos Excel 2003, seleccionaremos en Herramientas --> Personalizar --> y en la pestaña de barras de herramientas, seleccionaremos "Cuadro de controles". Una vez hecho esto, nos aparecerá la barra de controles, así que cerraremos la ventana emergente de personalización. Seguidamente, seleccionaremos el icono del final que tiene un dibujillo de un martillo y una llave, y seleccionaremos "Control de calendario 11.0".

En el caso de que utilicemos Excel 2007 y Excel 2010, para mostrar el Cuadro de controles, antes debemos hacer visible la pestaña "Programador". Para ello, nos iremos a Archivo --> Opciones --> Personalizar cinta de opciones --> y pondremos una muesca en "Programador". Una vez tengamos en la cinta de opciones la ficha Programador, dentro de ella, encontraremos el Cuadro de controles, seleccionando "Insertar", cuyo icono es el martillo y la llave.

En el caso concreto de utilizar Excel 2010 es más que probable que no os aparezca el control calendario. Para solucionar este contratiempo, deberéis seguir las instrucciones del comentario que publiqué el 15 de junio de 2011, ante una pregunta de un usuario, y que veréis al final del artículo incluir un calendario en un formulario. Os remito directamente a ese comentario, para no duplicar el contenido.

Aquí tenéis una imagen de Excel 2003, que es la versión que más suelo utilizar:


Una vez tengamos todo listo, y hayamos seleccionado el control calendario, solo nos quedará dibujar el calendario en la hoja de cálculo para que tenga el tamaño que deseemos. Para ello, simplemente deberemos cliquear y arrastrar el mouse, para darle la forma que queramos. Una vez hecho eso, si usamos Excel 2003, pulsaremos en el primer icono de la barra de Cuadro de controles (la escuadra, la regla, y el lápiz), para salir del modo diseño. En Excel 2007 y 2010, simplemente cliquearemos en el icono que hay junto al de Cuadro de controles, con el lápiz, regla y escuadra, para salir del modo diseño. A continuación, cerraremos la barra de Cuadro de controles, pues ya no la vamos a necesitar más.


Una vez hecho esto, solo nos quedará incluir el código de programación, en la hoja donde queramos hacer que aparezca el en cuestión (no en un módulo). Lo que haremos en nuestro ejemplo, será mostrar el calendario siempre que cliqueemos en una celda de la columna A. Da igual que celda sea, pues cuando cliqueemos sobre la columna A, nos aparecerá el calendario.

Muchos estaréis pensando: "pero si ya tenemos visible el calendario, ¿cómo va a aparecer de nuevo cuando cliqueemos en una celda de la columna A?". Bueno, la respuesta es sencilla. Lo que haremos será ocultar el calendario siempre que cliqueemos en cualquier celda que no esté en la columna A, y mostrarlo cuando cliqueemos en cualquier celda de la columna A.

¿Cómo vamos a hacer todo esto?. Pues con un sencillo código que pondremos en el evento SelectionChange. Es decir, un código que se ejecutará al cambiar la selección de la celda. También incluiremos unas sencillas líneas de código para incluir en esa celda (la de la columna A, que ha permitido que el calendario se haga visible), la fecha que seleccionemos en el calendario. Es decir, si cliqueamos en un día del calendario, esa fecha aparecerá el la celda donde estemos situados (esto lo haremos a través del evento Click).

El código del evento SelectionChange de la hoja, será este:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Ejemplo para mostrar el calendario al seleccionar 'cualquier celda de la columna A If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'ahora le diremos donde queremos que aparezca el calendario 'para que no se quede fijo en las celdas de arriba, ya que al hacer scroll, 'se nos quedaría oculto en la parte de arriba. 'Podemos jugar con estos valores, para ver donde se muestra 'el calendario, y para que quede a nuestro gusto Calendar1.Left = Target.Left + Target.Width + 60 'para que no se nos deforme el calendario, si estamos en las últimas filas 'incluimos un condicional que controlará la celda donde estamos y 'la comparará con la última fila de la hoja de cálculo If ActiveCell.Row + 20 >= Cells.Rows.Count Then Calendar1.Top = Target.Top - 145 Else Calendar1.Top = Target.Top - 50 End If 'hacemos visible el calendario Calendar1.Visible = True 'Mostramos la fecha de hoy Calendar1.Today Else 'ocultamos el calendario, en el caso de no 'seleccionar una celda de la columna A Calendar1.Visible = False End If End Sub

Y el del evento Click del calendario será este:

Private Sub Calendar1_Click() 'Al hacer clic en una fecha del calendario 'ponemos el valor en la celda donde estemos situados ActiveCell = Calendar1.Value 'ocultamos el calendario Calendar1.Visible = False End Sub

Con este sencillo código, obtendremos algo tan vistoso como esto, cada vez que nos situemos en una celda de la columna A:


Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo. Si os ha sido de utilidad, espero vuestros comentarios.

Ejecutar macro al seleccionar un elemento de un combobox

El otro día me preguntaba una lectora del blog, como se podía ejecutar un macro al cambiar un elemento de un ComboBox. Esta idea le había surgido, tras leer el artículo donde explicaba como ejecutar un macro, al cambiar una celda. Además, no solo quería que se ejecutase el macro al pulsar un botón del UserForm, que daba conformidad al elemento seleccionado en el ComboBox, sino que quería que se ejecutase simplemente al seleccionar el elemento (el item), del ComboBox.

Como ambas opciones son interesantes, vamos a verlas como siempre, con un sencillo ejemplo. Primero veremos la opción más normal o habitual, es decir, la que ejecuta el macro al pulsar el botón que da conformidad al elemento seleccionado en el Combobox.

Para ello, supondremos que tenemos un listado de marcas de vehículos, que alimentarán y llenarán el ComboBox1 que tenemos dentro del UserForm1. Al seleccionar el elemento nº 2 de la lista (con independencia de la marca de coches de que se trate) y pulsar el botón "OK", ejecutaremos un macro, que mostrará un mensaje. Esto podíamos hacerlo directamente, sin necesidad de llamar al macro que contiene el MsgBox, pues nos hubiera bastado con insertar el MsgBox en el evento change del propio ComboBox1, pero para ilustrar como lanzar un macro al cambiar el item, lo mejor es hacerlo de esta forma indirecta, es decir, llamando al macro, en lugar de insertando las líneas del macro directamente. Aparte de lanzar el macro, también nos desplazaremos a la celda que contiene el elemento seleccionado en el ComboBox1.

El segundo ComboBox, que llamaremos ComboBox2, hará lo mismo (en este caso con marcas de motos, en lugar de coches), pero sin necesidad de pulsar el botón "OK", porque de hecho ya no existirá ese botón pues en el UserForm2, solo aparece un desplegable, y nada más. Es decir, haremos lo mismo que con el UserForm1, pero sin necesidad de confirmarlo pulsando el botón "OK". La simple selección del elemento, lanzará el macro, y nos desplazará hasta la celda que contiene el valor seleccionado.

Necesitaremos algo como esto:


Lo primero que necesitaremos, será lanzar los UserForm, desde los botones que veis en la imagen anterior. Para ello escribiremos estas líneas en un módulo VBA:

Sub lanzar_userform1() 'llamamos al userform1 UserForm1.Show End Sub

Sub lanzar_userform2() 'llamamos al userform2 UserForm2.Show End Sub

Necesitaremos también los dos UserForm, tal y como muestran las siguientes imágenes (uno con el botón OK, y otro sin el):



Al activarse el UserForm1, es decir, al lanzarlo mediante el botón de la primera imagen, llamado "Lanzar el UserForm1", llenaremos dinámicamente el ComboBox1, con las marcas de vehículos. Para ello, necesitaremos este código, que irá dentro del UserForm1:

Private Sub UserForm_Activate() 'Nos situamos en la celda que nos interesa Range("A2").Select 'Hasta que no encuentre una celda vacía, que llene el combo Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox1.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop End Sub

El botón con el texto "OK" (el nombre del botón es CommandButton1), llevará asociado este código, también dentro del UserForm1:

Private Sub CommandButton1_Click() 'Llamamos al macro "mensaje", siempre y cuando 'hayamos elegido el elemento nº 2 del ComboBox If ComboBox1.List(ComboBox1.ListIndex) = ComboBox1.List(1) Then 'llamamos al macro "mensaje1" mensaje1 End If 'Nos desplazamos a la celda que contenga el dato 'seleccionado en el combobox, para lo cual 'pasamos el dato a una variable celda = ComboBox1.List(ComboBox1.ListIndex) Cells.Find(What:=celda).Activate End Sub

Y ahora necesitaremos el macro llamado "mensaje1", tal y como podemos ver en el código anterior. Este macro, irá en un módulo VBA, y contendrá estas líneas:

Sub mensaje1() 'Mostramos un mensaje, al seleccionar el elemento nº 2 'del Combobox (ponemos 1, porque los elementos comienzan 'a numerarse por cero: 0, 1, 2, 3, etc. MsgBox ("Has seleccionado el elemento nº 2 de la lista, es decir: " & UserForm1.ComboBox1.List(1)) End Sub

Ahora nos tocará hacer lo mismo con el UserForm2. Para alimentar el ComboBox2, utilizaremos este código:

Private Sub UserForm_Activate() 'Nos situamos en la celda que nos interesa Range("B2").Select 'Hasta que no encuentre una celda vacía, que llene el combo Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox2.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop End Sub

Al seleccionar un elemento del ComboBox2, lo que haremos será llamar al macro "mensaje2". En este caso, sin necesidad de confirmar la selección mediante el botón "OK", porque de hecho no existe ese botón en el UserForm2. Necesitaremos este código, que se ejecutará cada vez que se cambie un elemento seleccionado del ComboBox2:

Private Sub ComboBox2_Change() 'Si seleccionamos el elemento 2 de la lista, 'mostraremos un mensaje (recordemnos que los elementos 'del combo se numeran empezando por cero (0, 1, 2...) If ComboBox2.List(ComboBox2.ListIndex) = ComboBox2.List(1) Then 'llamamos al macro "mensaje2" mensaje2 End If 'Nos desplazamos a la celda que contenga el dato 'seleccionado en el combobox, para lo cual 'pasamos el dato a una variable celda = ComboBox2.List(ComboBox2.ListIndex) Cells.Find(What:=celda).Activate End Sub

Ahora, igual que en el caso anterior, necesitaremos el macro llamado "mensaje2", tal y como podemos ver en el código anterior. Este macro, irá en un módulo VBA, y contendrá estas líneas:

Sub mensaje2() 'Mostramos un mensaje, al seleccionar el elemento nº 2 'del Combobox (ponemos 1, porque los elementos comienzan 'a numerarse por cero: 0, 1, 2, 3, etc. MsgBox ("Has seleccionado el elemento nº 2 de la lista, es decir: " & UserForm2.ComboBox2.List(1)) End Sub

Aquí os dejo unos pantallazos, que ilustran este artículo. El primero de ellos, es cuando llamamos al UserForm2:


El siguiente pantallazo nos muestra como se ejecuta el macro "mensaje2", al ejecutar el elemento nº 2 de la lista, es decir, al cliquear sobre la marca de motos Derbi (he desplazado el MsgBox hacia abajo, para que veáis que en el UserForm2 está seleccionado ese segundo item):


Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo.

Validar la entrada de datos en un formulario

Si nos ha dado por utilizar formularios en nuestras aplicaciones Excel, porque consideramos que son más elegantes, cuidadas, y porque le dan un toque más profesional, llegará un momento en el que deseemos controlar más todavía lo que un usuario introduce por ejemplo en un TextBox. Seguramente nos habremos preguntado ¿qué pasa si un usuario introduce letras en un TextBox que luego utilizo para hacer operaciones con los datos de una celda?. Pues es evidente que nos dará un error, pues estaremos operando con texto y números. Pensemos por ejemplo, cuál será el resultado de multiplicar un texto por una cifra, ...¿nos dará error, verdad?.

Pues bien, vamos a intentar que eso no suceda. Vamos a hacer que en un TextBox de nuestro formulario, solo se puedan introducir números. También explicaremos como hacer lo contrario, es decir, cómo permitir que el usuario solo escriba texto, y ya puestos, para que nadie lo pregunte :-) también validaremos la entrada de fechas en formato dd/mm/aaaa.

Para el primer caso, es decir, si solo vamos a permitir que el usuario introduzca números, vamos a suponer que tenemos un formulario tan sencillo como este que podéis ver en la imagen:


Para ver la utilidad del dato introducido, supondremos que vamos a hacer una operación con ese valor. Es decir, vamos a imaginar que si el usuario introduce una cifra cualquiera, la multiplicaremos por 6, y escribiremos ese valor en la celda B11. Algo como lo que muestra la siguiente imagen:


El código asociado al TextBox del UserForm, será este:

Private Sub TextBox1_Change() 'Solo permitiremos que se introduzcan números 'para lo cual, si el valor introducido es una letra 'lo omitiremos, y nos quedaremos con el resto de caracteres, 'es decir, con los números If Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) > 1 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1) ElseIf Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) = 1 Then TextBox1 = Clear End If End Sub

Y el código asociado al botón "OK" que se puede ver en ese mismo userform, es este:

Private Sub CommandButton1_Click() 'Si hay errores, que continúe On Error Resume Next 'Vamos a multiplicar el valor introducido por el usuario 'por una cifra cualquiera. En este caso, la multiplicaremos 'por 6, y escribiremos el dato resultante en la celda B11 Range("B11") = TextBox1 * 6 'liberamos el formulario de memoria Unload UserForm1 End Sub

Como veis, es muy sencillo. No hace falta ni que lo comente más allá de lo que ya está comentado dentro del propio código.

Ahora vamos a imaginar que solo queremos que el usuario entre letras. En ese caso, ya no nos será útil multiplicar esas letras por 6, así que omitiremos hacer operaciones con esos datos introducidos, y simplemente los mostraremos en la celda. En el caso de este nuevo formulario, le hemos puesto nombres diferentes a los objetos, de tal forma que lo que antes era TextBox1, ahora es TextBox2, lo que antes era CommandButton1, ahora es CommandButton2, y así sucesivamente. En el caso que nos ocupa ahora, deberemos poner este código asociado al TextBox2:

Private Sub TextBox2_Change() 'Solo permitiremos que se introduzcan números 'para lo cual, si el valor introducido es una letra 'lo omitiremos, y nos quedaremos con el resto de caracteres, 'es decir, con los números If IsNumeric(Right(TextBox2, 1)) Then TextBox2 = Left(TextBox2, Len(TextBox2) - 1) End Sub

Y este otro en el botón "OK":

Private Sub CommandButton2_Click() 'Si hay errores, que continúe On Error Resume Next 'Vamos a escribir el dato introducido, en la celda E11 Range("E11") = TextBox2 'liberamos el formulario de memoria Unload UserForm2 End Sub

Para el caso de que queramos validar si el dato introducido es una fecha con formato dd/mm/aaaa, y suponiendo que nuestro TextBox se llama ahora TextBox3, en lugar de TextBox2, el botón se llama CommandButton3 en lugar de era CommandButton2, y así sucesivamente, tendremos que utilizar este código asociado al TextBox (al TextBox3, como hemos comentado):

Private Sub TextBox3_Change() 'si hay errores, que continúe On Error Resume Next 'impedimos que se introduzca texto If Not IsNumeric(Right(TextBox3, 1)) And Right(TextBox3, 1) <> "/" Then TextBox3 = Left(TextBox3, Len(TextBox3) - 1) End If 'contamos lo que mide de largo largo_entrada = Len(TextBox3) 'evaluamos la posición de lo que vamos escribiendo Select Case largo_entrada 'después del 2º carácter, que añada una barra Case 2 'controlamos algunas manipulaciones de los números If Right(TextBox3, 1) = "/" Then TextBox3 = Left(TextBox3, Len(TextBox3) - 1) Else TextBox3 = TextBox3 & "/" End If 'después del 5º carácter, que añada una barra Case 5 'controlamos algunas manipulaciones de los números If Right(TextBox3, 1) = "/" Then TextBox3 = Left(TextBox3, Len(TextBox3) - 1) Else TextBox3 = TextBox3 & "/" End If End Select 'si el largo ocupa más de 10 caracteres, 'solo nos quedamos con los primeros 10 caracteres If Len(TextBox3) > 10 Then TextBox3 = Left(TextBox3, 10) 'Deshabilitamos el botón "OK", si la fecha tiene menos de 10 caracteres If Len(TextBox3) < 10 Then CommandButton3.Enabled = False Else CommandButton3.Enabled = True End If 'evitamos que haya más de 2 barras en el TextBox If Len(TextBox3) = 10 And Len(Replace(TextBox3, "/", "")) < 8 Then TextBox3 = Clear End If End Sub

Y este otro en el botón "OK":

Private Sub CommandButton3_Click() 'Si hay errores, que continúe On Error Resume Next 'Vamos a escribir el dato introducido, en la celda H11 Range("H11") = TextBox3 'liberamos el formulario de memoria Unload UserForm3 End Sub

Solo queda por solucionar un tema, y es validar que la fecha sea correcta, no solo en formato, que eso ya lo hemos visto en este artículo, sino en su validez o consistencia, para que por ejemplo nadie introduzca una fecha como esta: 44/55/2222, pues los días como máximo son 31, y los meses 12. Pero eso, casi es mejor que lo validéis después de darle al botón OK, en lugar de impedir la entrada de fechas de ese tipo. Os lo dejo que lo resolváis vosotros, pues no parece muy complicado a simple vista, ya que el alcance de este artículo es validar que se entran datos de acuerdo a un criterio: números, textos, o fechas en formato dd/mm/aaaa, sin evaluar si esta última es correcta.

He resumido todo esto que he explicado, en una hoja de cálculo que tiene este aspecto, para el que esté interesado en verlo con ejemplos prácticos, pues siempre ayudan mucho más:


Desde aquí podéis descargar el fichero, con los ejemplos que hemos visto en este artículo.

Números aleatorios no repetidos

Ya hemos visto en varios artículos donde a través de unos ejemplos, ilustrábamos el uso de los números aleatorios. Entre ellos, una mini aplicación para obtener combinaciones de 6 números al azar entre 49 números, como hacer una tontería a través de números aleatorios dando el efecto de una máquina tragaperras, y como desplazarnos a una celda de forma aleatoria. En el primero de los ejemplos, a través de funciones propias de excel, y usando un bucle do while, íbamos controlando que no se repitieran los números aleatorios.

Ahora vamos a hacer eso mismo, pero utilizando exclusivamente funciones de visual basic para aplicaciones (VBA). Antes de poner el código, vamos a explicar como funcionará este sistema. En nuestro ejemplo generaremos tres números entre el 1 y el 100, y lo primero que haremos será generar un número aleatorio. En cuanto tengamos ese primer numerito, lo añadiremos al carro de la compra (valga el simil). A continuación generaremos un segundo número aleatorio, y comprobaremos si ya lo tenemos en el carrito de la compra (porque puede ser que el segundo número generado, sea igual que el primero). Si no lo tenemos en el carrito, lo añadiremos, y si ya lo tenemos previamente en el carrito, generaremos un nuevo número hasta encontrar uno que no tengamos en el carrito, y en ese momento lo añadiremos. Finalmente haremos lo mismo con el tercer número.

Una vez hecho eso, tendremos un carrito de la compra, donde habremos metido 3 números elegidos al azar. Estos números estarán separados por comas. Para llamar a cada uno de los números, nos bastará finalmente con buscar el elemento del carrito (del array): elemento 0, elemento 1, y elemento 2, …recordemos que por defecto, el elemento cero es el primero de la lista.

Esta sería una propuesta, para solucionar el problema de los números aleatorios y que no salgan repetidos:

Sub aleatorios_no_repetidos() 'llamamos a la función randomize, 'para que todo funcione correctamente Randomize 'indicamos el nº máximo y el mínimo maximo = 100 minimo = 1 For i = 1 To 3 'generamos el primer nº aleatorio registro_obtenido = Int((maximo - minimo + 1) * Rnd + minimo) 'generamos un aleatorio hasta encontrar uno no repetido Do While InStr(numeros, " " & registro_obtenido & ",") > 0 'lo añadimos al array registro_obtenido = Int((maximo - minimo + 1) * Rnd + minimo) Loop 'lo añadimos al array numeros = numeros & " " & registro_obtenido & "," Next 'quitamos la última coma numeros = Mid(numeros, 1, Len(numeros) - 1) 'formamos un array como Dios manda numeros = Split(numeros, ", ") 'escribiremos los números aleatorios 'en las celdas A1, A2, y A3 Range("A1") = numeros(0) Range("A2") = numeros(1) Range("A3") = numeros(2) End Sub

Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo.

Ejecutar un macro al cambiar una celda

La mayoría de las veces, cuando ejecutamos un macro, lo hacemos de forma consciente, previa solicitud, es decir, bajo petición, ya sea pulsando un botón que tiene asociado el macro, o bien desde los menús seleccionando el macro de la lista de macros disponibles.

Otras veces, ejecutamos una macro, cuando cambia cualquier celda de cualquier hoja de cálculo del libro (a través del evento SheetChange), o bien cuando cambia cualquier celda de una hoja de cálculo determinada (a través del evento Change).

Pero, ¿podemos ejecutar una macro, no solo a petición, o cuando cambia cualquier celda de la hoja, sino también cuando cambia una celda específica de la hoja de cálculo?. Es decir, ¿podemos ejecutar un macro solo si cambia una celda (o varias), impidiendo que se ejecute el macro, si se cambian otras celdas de la hoja?. Pues la respuesta, como debéis intuir, es sí. Por supuesto que se puede ejecutar un macro bajo esas condiciones.

Lo primero que tenemos que pensar es que tiene que cambiar "algo", para que se ejecute el macro. Ese "algo" será una celda de la hoja de cálculo. Y más concretamente esa celda será una que habremos elegido nosotros, es decir, no valdrá que cambie cualquier celda, sino solo la elegida por nosotros.

Para ver como funciona este macro, lo haremos con un ejemplo. Supongamos que tenemos la Hoja1 de un libro de excel, y queremos que nos muestre un mensaje (Msgbox), cada vez que alguien cambie el dato que tenemos en la celda B5. Por tanto, el mensaje solo se mostrará si alguien cambia la celda B5, y no se mostrará nada, si se cambia la celda B4, la B6, la N55, LA J677, o cualquier otra que no sea expresamente la celda B5.

Tenemos que pensar que lo primero que debe ocurrir es que debe cambiar algo, por tanto, debe producirse el evento Change (si es que lo aplicamos a la Hoja1 en concreto). Por tanto, deberemos poner algo dentro de la Hoja1, es decir, algo como esto:


Lo que irá dentro de ese evento Change, es un condicional, que evaluará si cambia o no la celda B5, para que en ese caso, se dispare el mensaje. La propia Microsoft nos propone hacer algo como esto (los comentarios del código son míos, no de Microsoft):

Private Sub Worksheet_Change(ByVal Target As Range) 'pasamos a una variable, la celda o celdas 'que queremos evaluar si cambian o no datos = "B5" 'como estamos dentro del evento "Change", algo tiene 'que estar cambiando... Pues bien, si la celda activa 'es la misma que la celda que hemos puesto en 'la variable llamada "datos", entonces 'que muestre un mensaje (recordemos que una doble 'negación es una afirmación) If Not Application.Intersect(ActiveCell, Range(datos)) Is Nothing Then 'mostramos un mensaje MsgBox ("Oyeeeee, sé que estás cambiando la celda " & datos & ".") End If End Sub

Y efectivamente, ese código funciona correctamente si cambiamos la celda B5 y pulsamos intro. Pero ¿qué ocurre si en lugar de pulsar intro, una vez editada la celda B5, pulsamos cualquier tecla de desplazamiento?. Pues que no funciona la solución propuesta por Microsoft. ¿Y qué pasa si editamos el contenido de la celda B4 (la que está encima de B5), y en lugar de pulsar intro, una vez cambiado el valor de esa celda, pulsamos la telcla de desplazamiento hacia abajo?. Pues que se ejecuta el macro, con lo cual, estamos en las mismas, ...no funciona correctamente el macro.

Es decir, la solución propuesta por Microsoft funciona, siempre y cuando utilicemos la tecla intro (la tecla enter), para editar (para cambiar) el contenido de las celdas, pero los usuarios medianamente avezados, suelen utilizar las teclas de desplazamiento, en lugar de la tecla intro, para finalizar la edición de las celdas, ...y en ese caso, la solución de Microsoft puede no funcionar (no siempre falla, evidentemente, pues para que falle, la celda activa debe ser la celda B5, o cualquier de sus celdas contiguas vertical u horizontalmente).

Para solucionar ese contratiempo, podemos cambiar la solución propuesta por Microsoft, y utilizar esta otra que es parecidísima:

Private Sub Worksheet_Change(ByVal Target As Range) 'pasamos a una variable, la celda o celdas 'que queremos evaluar si cambian o no datos = "B5" 'como estamos dentro del evento "Change", algo tiene 'que estar cambiando... Pues bien, si la celda activa 'es la misma que la celda que hemos puesto en 'la variable llamada "datos", entonces 'que muestre un mensaje (recordemos que una doble 'negación es una afirmación) If Not Application.Intersect(Target, Range(datos)) Is Nothing Then 'mostramos un mensaje MsgBox ("Oyeeeee, sé que estás cambiando la celda " & datos & ".") End If End Sub

Si observamos bien, la diferencia está en que Microsoft nos propone incluir Activecell, y en esta nueva versión lo cambiamos, y ponemos Target.

Si quisiéramos mostrar el mensaje al cambiar varias celdas contiguas, por ejemplo cuando cambiamos B5, B6, o B7, entonces haríamos esto:

Private Sub Worksheet_Change(ByVal Target As Range) 'pasamos a una variable, la celda o celdas 'que queremos evaluar si cambian o no datos = "B5:B7" 'como estamos dentro del evento "Change", algo tiene 'que estar cambiando... Pues bien, si la celda activa 'es la misma que la celda que hemos puesto en 'la variable llamada "datos", entonces 'que muestre un mensaje (recordemos que una doble 'negación es una afirmación) If Not Application.Intersect(Target, Range(datos)) Is Nothing Then 'mostramos un mensaje MsgBox ("Oyeeeee, sé que estás cambiando alguna celda del rango " & datos & ".") End If End Sub

Si por el contrario, lo que queremos es mostrar el mensaje al evaluar un rango discontinuo de celdas, por ejemplo las celdas B5, B7, y B12, entonces haríamos esto:

Private Sub Worksheet_Change(ByVal Target As Range) 'pasamos a una variable, la celda o celdas 'que queremos evaluar si cambian o no datos1 = "B5" datos2 = "B7" datos3 = "B12" 'como estamos dentro del evento "Change", algo tiene 'que estar cambiando... Pues bien, si la celda activa 'es la misma que la celda que hemos puesto en 'la variable llamada "datos", entonces 'que muestre un mensaje (recordemos que una doble 'negación es una afirmación) If Not Application.Intersect(Target, Range(datos1)) Is Nothing Or _ Not Application.Intersect(Target, Range(datos2)) Is Nothing Or _ Not Application.Intersect(Target, Range(datos3)) Is Nothing Then 'mostramos un mensaje MsgBox ("Oyeeeee, sé que estás cambiando alguna de estas celdas " & _ datos1 & ", " & datos2 & ", " & datos3 & ".") End If End Sub

Bueno, como veis, es bastante sencillo. El truco consiste en incluir dentro del evento Change de la hoja que queramos evaluar (aunque también puede ser en el libro), un condicional que evalúe si cambia determinada celda, y que será la que dispare el macro. En nuestro caso, en lugar de disparar un macro, lo que hemos hecho, ha sido mostrar un mensaje (mediante un Msgbox), aunque el mensaje, bien podría haber estado en un macro aparte, y de igual forma podríamos haberlo ejecutado. Tan solo habríamos tenido que llamar al macro desde el condicional del Intersect.

Combobox: sacándoles provecho

Como son varias las consultas que he recibido esta pasada semana, sobre el uso de los combobox, y a pesar de que ya son varios los artículos que tratan el tema, vamos a ver algunas cosas que creo que pueden ser de utilidad a muchos de los lectores del blog.

Vamos a ver varias cosillas sobre los combobox, para sacarles el máximo provecho. En concreto, en este artículo explicaré como hacer las siguientes cosas:

• Ejemplo 1: Cómo crear combobox dependientes, donde los ítems sean las diferentes hojas de cálculo que componen el libro (esto ya lo vimos en otro artículo, pero para un combobox no insertado en un formulario), y como el segundo combobox (el dependiente) muestra los datos que hay en la hoja seleccionada en el primer combobox.


• Ejemplo 2: Cómo crear un combobox con valores únicos a partir de una lista donde se repiten varios de los valores. Es decir, solo se mostrarán los ítems de tal forma que no estén repetidos, a pesar de que tengamos una lista con valores repetidos.


• Ejemplo 3: Cómo acceder a un elemento seleccionado en un combobox dependiente (aunque la técnica también nos servirá si lo que queremos es acceder simplemente a un elemento de un único combobox), o lo que es lo mismo, como saber en qué celda está el elemento del combobox que hemos seleccionado.

Ejemplo 1:

Comencemos por el principio. Vamos a ver como crear combobox dependientes dentro de un Userform, con los nombres de las hojas que componen el libro, y con los datos de la hoja seleccionada, que cargarán en el combobox secundario.

Una vez tengamos creado el userform, con sus dos combobox, tan solo tendremos que añadir estos códigos. El código del primer combobox será este, suponiendo que lo hayamos llamado Combobox1:

Private Sub ComboBox1_Enter() 'En caso de error, que continúe On Error Resume Next 'Esto se producirá cuando nos 'situemos sobre el combobox1 'antes quitaremos todo lo que haya en el combobox1 ComboBox1.Clear 'Vamos a llenar dinámicamente el combobox 'con los nombres de las hojas For i = 1 To Sheets.Count 'Añadimos los nombres de las hojas al combobox ComboBox1.AddItem Sheets(i).Name Next End Sub

El código del segundo combobox (el dependiente), será este, suponiendo que lo hayamos llamado Combobox2:

Private Sub ComboBox2_Enter() 'En caso de error, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Limpiamos lo que haya ComboBox2.Clear 'Pasamos el dato de la hoja, a una variable hoja_elegida = ComboBox1.List(ComboBox1.ListIndex) 'seleccionamos esa hoja Sheets(hoja_elegida).Select 'seleccionamos la cela A1 Range("A1").Select 'Llenamos el combo con los datos del combobox1 'para lo cual supondremos que los datos de cada 'hoja están a partir desde A1 hacia abajo, y 'hasta encontrar una fila vacía Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox2.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'Volvemos a la hoja1 que es donde 'tenemos el botón para lanzar el userform Hoja1.Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Para lanzar el userform, nos bastará con incluir este código en un módulo VBA (suponiendo que el userform se llame userform1):

Sub lanzar_formulario() 'Lanzamos el userform UserForm1.Show End Sub

Ejemplo 2:

Para el caso de que deseemos crear un combobox con valores únicos (vamos a suponer que también cargamos las hojas que componen el libro, como primer combobox, y que los datos de cada hoja, se cargarán en el segundo combobox), lo que tendremos que hacer es añadir este código en el combobox2 (el código del combobox1 será el mismo que en el ejemplo 1 que acabamos de ver hace unos instantes):

Private Sub ComboBox2_Enter() 'En caso de error, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Limpiamos lo que haya ComboBox2.Clear 'Pasamos el dato de la hoja, a una variable hoja_elegida = ComboBox1.List(ComboBox1.ListIndex) 'seleccionamos esa hoja Sheets(hoja_elegida).Select 'seleccionamos la cela A1 Range("A1").Select 'Llenamos el combo con los datos del combobox1 'para lo cual supondremos que los datos de cada 'hoja están a partir desde A1 hacia abajo, y 'hasta encontrar una fila vacía Do While Not IsEmpty(ActiveCell) 'si el dato no está repetido, lo añadimos 'a la variable "datos", separando los 'elementos por comas If InStr(datos, ActiveCell) = 0 Then 'añadimos el dato datos = datos & "," & ActiveCell End If 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'eliminamos la primera coma datos = Right(datos, Len(datos) - 1) 'separamos cada elemento por la coma dato_individual = Split(datos, ",") 'recorremos todos los elementos For i = 0 To UBound(dato_individual) 'Añadimos los elementos ComboBox2.AddItem dato_individual(i) Next 'Volvemos a la hoja1 que es donde 'tenemos el botón para lanzar el userform Hoja1.Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si os fijáis bien, lo que hemos hecho en ese combobox2, es crear un array (o un vector), donde vamos añadiendo los valores que nos vamos encontrando en cada hoja de cálculo, siempre y cuando no estén ya en el array. Estos elementos los vamos separando por comas. Luego al final, lo que hacemos es separar los elementos del array nuevamente por ese carácter (la coma), para escribirlos en el combobox2.


Ejemplo 3:

Finalmente, vamos a ver como acceder a uno de los elementos del combobox dependiente. Es decir, vamos a acceder al elemento seleccionado del combobox2. Para que se entienda mejor, si hemos seleccionado un item (un elemento del combobox), que está en la hoja3, y en la celda A2, nos situaremos encima de ella, una vez pulsado el botón de aceptar que crearemos en el formulario, expresamente para tal fin.

Tanto el código del combobox1, como el del combobox2, serán los mismos que los que vimos en el ejemplo 1 anterior, así que nos ahorraremos el volverlos a poner aquí. La única diferencia que encontraremos entres aquel primer ejemplo y este tercer ejemplo, será que en este ejemplo de ahora, vamos a añadir un botón al userform (dejaremos el nombre que tiene por defecto, es decir, CommandButton1), y su código será el siguiente:

Private Sub CommandButton1_Click() 'En caso de error, que continúe On Error Resume Next 'leemos lo que nos devuelven los combobox hoja_elegida = ComboBox1.List(ComboBox1.ListIndex) dato_elegido = ComboBox2.List(ComboBox2.ListIndex) 'vamos a la hoja elegida Sheets(hoja_elegida).Select 'vamos a la celda con el valor elegido en el combobox2 Cells.Find(What:=dato_elegido).Activate 'Eliminamos el userform de la memoria Unload UserForm1 End Sub

Al pulsar sobre el botón "Aceptar", lo que estamos haciendo, es seleccionar el elemento del combobox1, es decir, la hoja seleccionada, y seleccionar también el elemento del combobox2. Luego buscará ese item o elemento del segundo combobox, en la hoja seleccionada en el combobox1, para situarnos sobre él. Una vez hecho todo eso, descargaremos el userform de memoria, para liberar recursos del sistema.


Desde aquí podéis descargar un fichero comprimido en formato zip, con los tres ejemplos que hemos visto en este artículo.

Usuarios, hojas y permisos

Hoy vamos a ver como podemos mostrar determinadas hojas de cálculo dependiendo del usuario que abra el libro de excel. Para ello, vamos a suponer que tenemos una red de ordenadores, donde diferentes usuarios utilizan un mismo libro de excel, alojado por ejemplo en el servidor. Vamos a suponer también, que solo a determinados usuarios, les dejaremos interactuar con determinadas hojas. El resto de usuarios no podrán usar esas hojas de cálculo que requieren de más privilegios. Esto nos servirá de complemento a otro macro que ya vimos en su momento, y que nos sirve para controlar los cambios que han hecho los usuarios que utilizan un mismo libro de excel.

Para comenzar, lo primero que haremos una vez abierto el libro, será seleccionar en el menú Herramientas, la opción Compartir libro…, y le pondremos una muesca a la opción Permitir la modificación por varios usuarios a la vez. Esto también permite combinar libros. Una vez hecho esto, guardaremos el fichero.

Ahora crearemos un macro dentro de un módulo, para el evento Workbook Open, es decir, que será un macro que se ejecutará al abrirse el fichero. El código que usaremos, será el siguiente:

Private Sub Workbook_Open() 'Creamos un vector con todos los usuarios con 'permisos de mayor nivel, para lo cual creamos una lista administradores = "Contabilidad,Facturacion,Personal,Mateo,Ignacio,Irene" 'separamos los elementos de la matriz '"administradores" usando split, y por la coma administrador = Split(LCase(administradores), ",") 'Miramos si el nombre del usuario que ha abierto 'el fichero de excel, está en esa lista usuario = LCase(Application.UserName) For i = 0 To UBound(administrador) posicion = posicion + InStr(usuario, administrador(i)) Next 'Si el nombre del usuario no está dentro de 'la lista, entonces, la posición será igual a 0, 'por lo que el usuario no tendrá derechos de administrador If posicion = 0 Then 'en ese caso, ocultamos las hojas que no queremos mostrar '(en este caso suponemos que es la hoja3, pero el nombre 'interno que se ve desde VBA, no el nombre de la pestaña) Hoja3.Visible = xlSheetVeryHidden Else 'en esl caso de ser un usuario administrador, 'mostraremos las hojas que queremos mostrar '(en este caso suponemos que es la hoja3, pero el nombre 'interno que se ve desde VBA, no el nombre de la pestaña) Hoja3.Visible = xlSheetVisible End If 'grabamos los cambios ActiveWorkbook.Save End Sub

Ese macro que acabamos de ver, lo que hace es mostrar la hoja3 del libro, siempre y cuando el usuario que lo abra, sea alguno de los que aparecen en la lista que hemos incluido. En caso de no aparecer en esa lista, la hoja3 se ocultará.

Evidentemente deberemos proteger el modo VBA para que ningún usuario tenga acceso al código del macro. Para ello, desde el modo VBA, seleccionaremos en el menú Herramientas, la opción Propiedades de VBAProject..., y en la pestaña Protección, marcaremos con una muesca la opción Bloquear proyecto para visualización, y a continuación donde se nos solicita una contraseña (dentro de esa misma pestaña), introduciremos una contraseña que podamos recordar. Finalmente volveremos a escribir la contraseña de nuevo, para cerciorarnos de que no nos hemos equivocado al introducir la contraseña la primera vez, y listo, pulsaremos sobre el botón aceptar, y grabaremos el fichero.

Si no sabes cual es tu nombre de usuario, o el nombre de usuario de algunos de los PC’s de la red, puedes ejecutar este sencillo macro, que nos devolverá el nombre del equipo:

Sub nombre_del_usuario() respuesta = MsgBox("El usuario de este equipo es: " & Application.UserName) End Sub

Si deseáis dar más permisos (más niveles de seguridad), podéis adaptar el macro anterior. Por ejemplo, podéis dar permisos de administrador para que un grupo de usuarios vea todas las hojas, luego otro grupo que vea todas las hojas menos una, y luego otro grupo que solo pueda ver una de las hojas del libro.

Como veis, es muy sencillo esto de dar permisos, y mostrar una u otra hoja a uno u otro usuario, dependiendo del equipo (del ordenador) que abra al libro de excel.

Combobox dependientes

Hace poco me preguntaba un usuario, en un comentario del blog, como aplicar las listas de validación dependientes, pero en lugar de con listas de validación, con combobox. En una respuesta que le di, le colgué un ejemplo, que es el que utilizaré en este artículo, explicando su funcionamiento con detalle.

Lo primero que tendremos que preparar, son los datos de origen, y que se cargarán en los diferentes combobox. En este caso, usaremos dos combobox, aunque su funcionamiento es el mismo, si anidamos más de dos combobx dependientes.

Para explicar el funcionamiento, supondremos que tenemos una lista de países, con sus respectivas ciudades. En este ejemplo hay tres países, y tres ciudades por cada país, aunque si lo deseamos podemos añadir más países, y también más ciudades (no tienen porqué tener todos los países el mismo número de ciudades, ya que si lo deseamos, podemos poner en un país 5, 6, o 20 ciudades, y en otro 2, 3, o las que deseemos). Vamos a trabajar con el siguiente ejemplo:


A continuación, crearemos un UserForm, para lo cual accederemos al modo VBA (Alt+F11), y desde el menú Insertar, seleccionaremos la opción UserForm. Se nos presentará un UserForm en blanco, y lo que tendremos que añadir son dos etiquetas (label), y dos combobox (cuadros combinados). Si no nos apareciera el Cuadro de herramientas, para poder añadir esos controles, desde el menú Ver, seleccionaremos Cuadro de herramientas.

Tendremos que montar un UserForm como este que muestra la imagen:


A continuación, añadiremos este código a UserForm, haciendo doble clic en cualquier control del mismo (o en el propio UserForm), que nos servirá para que al cargarse el UserForm, se añadan al combobox1 (al combobox donde se cargan los nombres de los países) los datos que tenemos en la hoja1:

Private Sub UserForm_Initialize() 'Al inciar el formulario, 'seleccionamos la hoja con los datos Hoja1.Select 'Seleccionamos la celda A1 Range("A1").Select 'Hasta que no encuentre una columna vacía 'que llene todo con datos Do While ActiveCell <> Empty ComboBox1.AddItem ActiveCell.Value 'nos desplazamos una columna a la derecha ActiveCell.Offset(0, 1).Select Loop End Sub

Añadiremos también este otro código, que hará que al cambiar el combobox1 (el de los países), se carguen las ciudades en el combobox2, correspondientes al país elegido:

Private Sub ComboBox1_Change() 'Llenamos el combobox2 al recibir el foco, 'pero antes lo limpiamos ComboBox2.Clear 'seleccionamos la hoja1 Hoja1.Select 'miramos lo que hay seleccionado en el combobox1 'Listindex + 1 nos devuelve la columna del país, 'porque el listindex empieza siempre por cero columna = ComboBox1.ListIndex + 1 'seleccionamos la celda que proceda, 'a partir de la segunda fila Cells(2, columna).Select 'vamos a llenar dinámicamente el combobox 'dependiendo del país elegido Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox2.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop End Sub

Insertaremos también un módulo VBA, y añadiremos este código para que al cliquear en un botón que añadiremos a nuestra hoja de cálculo, se cargue el formulario (el UserForm):

Sub UserForm() 'llamamos al userform UserForm1.Show End Sub

Una vez hayamos añadido un botón, para que al cliquear sobre él, se cargue el UserForm, obtendremos algo parecido a lo que aparece en la siguiente imagen (podéis cliquear sobre ella, para verla ampliada), donde hemos seleccionado un país (en este caso España), y donde podemos elegir una de las tres ciudades correspondientes a este país:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Euroconversor

Aunque hace ya muchos años que entró en vigor el euro, son muchas las personas, que cuando ven un precio o un importe en euros, hacen mentalmente la conversión a pesetas. ¿Por qué?. Pues porque han sido muchos años los que hemos convivido con la peseta, como para que nos olvidemos de ella de golpe. Especialmente, es la gente mayor la que más dificultades tiene para saber realmente el "valor" de un precio o de un importe en euros.

Para facilitar esta tarea de conversión -y también, porque esta semana no estaba my inspirado, y he tenido que echar mano del baúl de los recuerdos-, vamos a programar un sencillo conversor que nos servirá para convertir una cantidad en pesetas, a su contravalor en euros. De la misma forma, también podremos convertir una cifra en euros, a su contravalor en pesetas. Con alguna ligera adaptación, esto también nos puede servir, para convertir diferentes monedas que tengan una relación de cambio fija.

Necesitaremos crear un UserForm (un formulario de usuario), donde insertaremos:

• 3 etiquetas de texto (Label), de las cuales, una de ellas será para introducir el texto "Introduce una cantidad". Otra de las etiquetas servirá mostrar el contravalor calculado de la moneda que deseamos convertir (a esta etiqueta, le daremos color azul celeste, para que podamos localizarla en el UserForm, y para darle un toque distinto), y la tercera etiqueta servirá para mostrar el texto "Ptas" o "Euros". A estas etiquetas les pondremos por nombre Texto1, Texto2, y Texto3, respectivamente


• 1 cuadro de texto (TextBox), al que le pondremos por nombre Cantidad.


• 2 botones de opción (OptionButton), de los cuales, uno servirá para convertir de euros a pesetas, y el otro de pesetas a euros. Les pondremos por nombre DeEurosAPtas, y DePtasAEuros, respectivamente.


• 1 imagen de una moneda de 1 euro y de 1 peseta (todo ello en una misma imagen). Le pondremos por nombre Image1


• 1 CommandButton, para mostrar el mensaje de "Cerrar ventana", y al que le pondremos por nombre Cerrar.

Al UserForm, le daremos el nombre de Euroconversor, para lo cual simplemente tendremos que introducir ese nombre en la propiedad "Nombre", tal y como figura en la siguiente imagen (ver la zona remarcada en rojo):


Para darle nombre al resto de elementos (TextBox, OptionButton, etc.), desde el modo VBA, cliquearemos encima de cada uno de los
Para que se ejecute el macro, hemos habilitado un botón en la hoja de cálculo. Cada vez que se cliquee sobre el mismo, se nos cargará el UserForm:


Para que se cargue el UserForm, en un módulo de VBA, crearemos este macro:

Sub Cargar_conversor() 'Que se cargue el UserForm del Euroconversor EurosPesetas.Show End Sub

Para que funcione el euroconversor, deberemos añadir todos estos códigos que a continuación os incluyo. Para colocar el código, deberéis cliquear en el UserForm, cuando lo tengáis a la vista, desde el modo VBA (ver la primera imagen que hay en este artículo).

Para convertir de euros a pesetas, añadiremos esto (en el código correspondiente al formulario):

Private Sub DeEurosAPtas_Click() 'Si hay errores, que continúe On Error Resume Next 'Si la cantidad está vacía... If Cantidad = Empty Then 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus 'mostramos un mensaje MsgBox (Chr(13) + " Por favor, introduce una cantidad. " _ + Chr(13) + Chr(13)), vbOKOnly, " Datos incompletos" End If 'Si Cantidad no es numérica If Not IsNumeric(Cantidad) Then 'eliminamos la entrada Cantidad = Empty 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus Else 'si es numérica,le damos formato con 2 decimales Cantidad = Format(Cantidad, "##,##0.00") 'que ponga el texto "Euros", en la etiqueta correspondiente Texto3.Caption = "Euros" 'le damos color negro a la cifra convertida, 'es decir, al resultado obtenido Texto2.ForeColor = RGB(0, 0, 0) 'que añada el texto "Ptas", al resultado Texto2.Caption = Format(Cantidad * 166.386, "#,##0") & " Ptas" End If End Sub

Para convertir de pesetas a euros, añadiremos esto (en el código correspondiente al formulario):

Private Sub DePtasAEuros_Click() 'Si hay errores, que continúe On Error Resume Next 'Si la cantidad está vacía... If Cantidad = Empty Then 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus 'mostramos un mensaje MsgBox (Chr(13) + " Por favor, introduce una cantidad. " _ + Chr(13) + Chr(13)), vbOKOnly, " Datos incompletos" End If 'Si Cantidad no es numérica If Not IsNumeric(Cantidad) Then 'eliminamos la entrada Cantidad = Empty 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus Else 'si es numérica,le damos formato sin decimales Cantidad = Format(Cantidad, "##,##0") 'que ponga el texto "Euros", en la etiqueta correspondiente Texto3.Caption = "Ptas" 'le damos color azul a la cifra convertida, 'es decir, al resultado obtenido Texto2.ForeColor = RGB(23, 48, 141) 'que añada el texto "Ptas", al resultado, 'y con formato de 2 decimales Texto2.Caption = Format(Cantidad / 166.386, "#,##0.00") & " Euros" End If End Sub

Cada vez que cliqueemos en el TextBox llamado Cantidad, reiniciaremos los datos del UserForm. El código que necesitaremos es este:

Private Sub Cantidad_Enter() 'Si hay errores, que continúe On Error Resume Next 'que elimine todas las entradas Cantidad = Empty Texto2.Caption = Empty Texto3.Caption = Empty 'desmarcamos las opciones de los botones DeEurosAPtas.Value = False DePtasAEuros.Value = False End Sub

Y finalmente, para cerrar el UserForm, si cliqueamos en el botón habilitado a tal efecto, necesitaremos este código:

Private Sub cerrar_Click() 'Si hay errores, que continúe On Error Resume Next 'Descargamos el formulario de la memoria Unload Me End Sub

Aquí os dejo dos pantallazos tras introducir la cifra de 1.275. Primero convertiremos esa cifra a pesetas, y en la otra imagen, convertiremos esa cifra, de pesetas a euros:



Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Llenar dinámicamente un combobox

Para alimentar con datos, un combobox, tenemos varias opciones:

a) Llenar el combo con los datos de determinado rango, y cuyas filas son siempre las mismas (por ejemplo, con los datos del rango E1:E10, con los datos de B12:B15, etc.). En este caso, sabremos que nuestro rango siempre tiene 10 filas, o 4 filas, o las que sean, pero sabremos que siempre es una cantidad fija en invariable.

b) Llenar el combo con los datos de un rango que puede crecer o decrecer, es decir, que en un momento dado el combo puede tener 3 elementos, y al cabo de cinco segundos puede tener 20 elementos. En este caso, estamos hablando de que nuestro combobox se alimenta de un rango dinámico.

Para insertar un combobox directamente en nuestra hoja de cálculo, sin tener que insertarlo en un UserForm, haremos lo siguiente: Desde excel, seleccionaremos Ver, a continuación Barras de herramientas, y seguidamente Cuadro de controles, tal y como aparece en la figura de la izquierda.

Seleccionaremos el control llamado Cuadro combinado (acercando el ratón a cada control, nos aparecerá el nombre de cada uno de ellos). Una vez seleccionado, dibujaremos el combobox en la hoja de cálculo, dándole el tamaño y posición que deseemos. Arriba a la izquierda, en la barra de fórmulas, nos aparecerá su nombre, …por defecto ComboBoxY, donde Y es un número correlativo, de tal forma que si es nuestro primer combobox, se llamará ComboBox1, si es el 2º, se llamará ComboBox2, y así sucesivamente.

Para el primer caso, es decir, si queremos llenar el combobox con los datos de un rango fijo, entonces lo tenemos bien fácil. Imaginemos que tenemos un rango con datos que va de I8 a I10, y queremos que esos valores almacenados en esas celdas (en las 3 celdas: I8, I9, e I10), sean los que aparezcan en nuestro combobox. Supongamos que estamos trabajando con un combobox que se llama ComboBox3. En ese caso, el código que podríamos utilizar sería este (estoy suponiendo que el combobox no está insertado dentro de un UserForm, porque en ese caso, el código sería algo distinto, ya que utilizaríamos RowSource, en lugar de usar ListFillRange), y desde la pantalla para VBA, lo pegaríamos en la hoja1, que es donde tenemos nuestro combobox, y no en un módulo:

Private Sub ComboBox3_GotFocus() 'Este código se ejecutará cada vez que 'nos situemos sobre el combobox ComboBox3.ListFillRange = "I8:I10" ComboBox.RowSource End Sub

Si por el contrario, el rango que alimenta el combobox es dinámico, deberemos añadir los elementos al combobox, mediante el método AddItem.

Supongamos por ejemplo que tenemos un libro con 3 hojas y queremos que en nuestro combobox nos aparezcan los nombres de esas tres hojas. En ese caso, el código del combobox (en este caso, nuestro ComboBox1) sería el siguiente (estamos haciendo un combobox directamente sobre nuestra hoja de cálculo, y no insertado dentro de un formulario), y que tendríamos que pegar desde VBA, en la hoja1, que es donde tenemos nuestro combobox, y no en un módulo:

Private Sub ComboBox1_GotFocus() 'Esto se producirá cuando nos 'situemos sobre el combobox1 '********* Llenamos el combobox1 ********* 'antes quitaremos todo lo que haya en el combobox1 ComboBox1.Clear 'Vamos a llenar dinámicamente el combobox 'con los nombres de las hojas For i = 1 To Sheets.Count 'Añadimos los nombres de las hojas al combobox ComboBox1.AddItem Sheets(i).Name Next End Sub

Con ese código, recorremos los nombres de las hojas, y se cargarán en el combobox, cada vez que éste reciba el foco (cada vez que nos situemos sobre él).

Si deseamos que se ejecute cierta acción, cada vez que elijamos un elemento de la lista de ese ComboBox2, añadiremos el código que proceda, también dentro de la hoja1. En nuestro caso, cada elemento corresponde a una hoja de cálculo del libro, y eso será lo que nos aparezca en el desplegable del combobox. Nosotros mostraremos un mensaje, aunque podríamos haber hecho cualquier cosa: ir a la hoja en cuestión, eliminar la hoja, copiarla, etc:

Private Sub ComboBox1_Click() 'Mostramos un mensaje MsgBox ("Has hecho clic sobre: " & ComboBox1.List(ComboBox1.ListIndex)) End Sub

Si lo que deseamos es llenar dinámicamente un combobox a través de los datos que hay en un rango de celdas continuo, entonces haremos lo siguiente (suponemos que nuestro combobox se llama ComboBox2, y que está insertado directamente en la hoja de cálculo, y no en un formulario), pegándolo en la hoja1 que es donde tenemos nuestro combobox:

Private Sub combobox2_gotfocus() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'fichamos la celda donde estamos, para volver a ella celda = ActiveCell.Address 'Esto se producirá cuando nos 'situemos sobre el combobox2 '********* Llenamos el combobox2 ********* 'antes quitaremos todo lo que haya en el combobox2 ComboBox2.Clear 'Vamos a llenar dinámicamente el combobox 'con los datos de la celda F8 en adelante '(siempre que los datos sean continuos) Range("F8").Select Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox2.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'volvemos donde estábamos Range(celda).Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si deseamos que se ejecute cierta acción, cada vez que elijamos un elemento de la lista de ese ComboBox3, añadiremos el código que proceda, también dentro de la hoja1. En nuestro caso, cada elemento corresponde a los datos que hay desde F8 en adelante (rango continuo de datos), y eso será lo que nos aparezca en el desplegable del combobox. Nosotros mostraremos un mensaje, aunque podríamos haber hecho cualquier otra cosa:

Private Sub ComboBox2_Click() 'Mostramos un mensaje MsgBox ("Has hecho clic sobre: " & ComboBox1.List(ComboBox2.ListIndex)) End Sub

A modo de resumen, hoy hemos visto varias cosas nuevas:

a) Cómo crear un combobox, sin necesidad de insertarlo dentro de un UserForm.
b) Cómo llenar un combobox de forma dinámica, sin tener que tocar el código cada vez que cambien las dimensiones del rango de datos que lo alimentan.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Mostrar imágenes asociadas a un desplegable

Quizás te hayas preguntado alguna vez, como podrías seleccionar un dato de un desplegable, y que al lado salga su foto, es decir, que salga la foto asociada al elemento seleccionado del desplegable. Esto es especialmente útil por ejemplo, si tenemos un listado desplegable de productos o de empleados, y queremos que al seleccionar cada empleado o artículo, nos salga al lado la foto de cada uno de ellos.

¿Verdad que parece un tema interesante?. Pues nada, vamos a tratar de explicarlo, porque no tiene mucho secreto.

Lo primero que tendremos que hacer, es que las imágenes (fotos de los empleados, de los productos, etc.) sean todas del mismo tamaño, para que no se muestren unas imágenes grandes y otras pequeñas, y nos quede poco elegante. Además, es importante que las fotos no tengan espacios vacíos, ni acentos (tampoco los elementos del desplegable), para que los usuarios de todos los países puedan utilizar el código sin problemas. En lugar de utilizar espacios vacíos, usaremos guiones. Por ejemplo, si tenemos un listado de artículos, y hay uno que se llama Bidones de 50 litros, a la foto le pondremos como nombre Bidones-de-50-litros.jpg. Lo recomendable -para tener un código bastante simple, como el que os muestro en este artículo-, es que las fotos tengan todas la misma extensión, …o todas "jpg", o todas "gif", o todas "png", etc. En el caso de que utilicéis otra extensión distinta a "jpg", tendréis que cambiar la línea del código fuente que veréis al final de este artículo, para que en su lugar aparezca "gif", o la extensión que utilicéis para vuestras imágenes.

Las fotos y el libro de excel con el desplegable, los guardaremos en la misma carpeta o directorio. Si estuvieran en directorios diferentes, deberíamos modificar el código VBA para informar de la ruta correcta de las imágenes.

Vamos a explicarlo con un ejemplo. Yo he utilizado una serie de vehículos clásicos (de los cuales soy un apasionado, todo sea dicho de paso), de tal forma que cada vez que seleccionemos uno de los coches, nos saldrá debajo su foto correspondiente. Si por ejemplo seleccionases en el desplegable, el Golf GTI mk1, aparecería esto en tu pantalla:


Y si seleccionases por ejemplo el Seat 124 Sport, aparecería esto de forma automática:


Lo primero será crear en la Hoja2, un listado con los coches que tenemos. En este caso tenemos 5 vehículos, tal y como se muestra en la imagen siguiente:


Ahora, le pondremos un nombre al rango de datos comprendido entre B4 y B8, para lo que seleccionaremos ese rango, y definiremos un nombre para el mismo. En este caso, le he puesto como nombre coches, tal y como se puede ver en la zona enmarcada en rojo, en la siguiente imagen (para definir un nombre, la forma más sencilla es escribir el nombre en esa zona enmarcada en rojo, y pulsar intro):


Ahora ya sabemos que el rango de celdas que hay entre B4 y B8 de la Hoja2, se llama coches.

A continuación pasaremos a la Hoja1, y por ejemplo, en la celda C4, crearemos la lista desplegable con los coches, para que cada vez que seleccionemos un modelo, nos salga debajo su foto correspondiente. Para ello, seleccionaremos en el menú Datos, la opción Validación…, y en la pestaña Configuración, que es la que nos saldrá por defecto en primer plano, elegiremos en Permitir la opción Lista, y en Origen introduciremos el nombre definido anteriormente, precedido del signo igual, tal y como se muestra en la siguiente imagen:


De tal forma que tendremos algo como esto que se muestra en la siguiente imagen, al pulsar en la flechita del desplegable:


Una vez hecho esto, solo nos quedará copiar y pegar el código que hará que se nos muestre la foto, pero no lo incluiremos en un módulo, sino dentro de la Hoja1, tal y como se muestra en esta imagen:


El código que tenemos que copiar y pegar en la Hoja1, es este:

Private Sub Worksheet_Change(ByVal Target As Range) 'Si ha errores, que continúe On Error Resume Next 'Si cambiamos el dato de la celda C4, 'mostramos la foto de ese vehículo If Target.Cells = Range("C4") Then 'Ocultamos el procedimiento Application.ScreenUpdating = False 'pasamos a una variable, el nombre de la foto, 'que será el mismo que el nombre del coche, pero 'separado con guiones, y sin acentos, para que 'todos los usuarios puedan verlo correctamente foto = Range("C4").Value 'en la foto, reemplazamos los espacios, por guiones foto = Replace(foto, " ", "-") 'ahora le añadimos la extensión "jpg" foto = foto & ".jpg" 'ahora buscamos la foto en el mismo directorio 'donde tenemos este fichero de excel rutayarchivo = ActiveWorkbook.Path & "\" & foto 'borramos la foto del coche (si hubiera alguna) Me.Shapes("foto_del_coche").Delete 'creamos el objeto fotografia, con la foto insertada Set fotografia = Me.Pictures.Insert(rutayarchivo) 'haremos que la foto ocupe desde B6 hasta D21, 'para que no salgan fotos supergrandes, o 'superpequeñas, y salgan más "normalitas" With Range("B6:D21") Arriba = .Top Izquierda = .Left Ancho = .Offset(0, .Columns.Count).Left - .Left Alto = .Offset(.Rows.Count, 0).Top - .Top End With 'le ponemos un nombre al objeto "fotografia" 'para poder borrarla cuando cambie la celda D6 '(ver que borramos la foto que hubiese, antes de insertar la nueva) With fotografia .Name = "foto_del_coche" .Top = Arriba .Left = Izquierda .Width = Ancho .Height = Alto End With 'eliminamos el objeto Set fotografia = Nothing 'ponemos todo como estaba Application.ScreenUpdating = True End If End Sub

Ese código lo que hace es insertar la foto, de cada uno de los coches, cada vez que seleccionemos uno de ellos. Para ello, debemos tener presente una serie de variables que he utilizado, para simplificar el código VBA:

1.- Las fotos y el fichero deben estar en el mismo directorio.
2.- Las fotos y los modelos de vehículos, deben tener el mismo nombre (en ambos casos sin acentos, y en el caso de las fotos, sustituyendo los espacios por guiones).
3.- Las fotos deben tener todas la misma extensión (en el código verás que aparece por ahí la extensión ".jpg").

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo, junto con las imágenes de los vehículos clásicos, para que podáis ver en funcionamiento como se crean listas de validación con imágenes asociadas a cada elemento del desplegable.