Activar una macro desde una lista desplegable

sábado, noviembre 13, 2010

En este blog hemos tratado extensivamente el tema de las listas desplegables. Pero siempre queda un detalle más a tratar, como la consulta de un lector sobre cómo accionar una macro a partir de la elección efectuada en una lista desplegable.

La idea es tener una lista desplegable en una celda creada con validación de datos de la cual podemos elegir tres valores. Según la elección, la macro correspondiente es accionada.

Vamos a ejemplificarlo con un modelo absolutamente inocuo, pero útil para los propósitos de la explicación. Programamos tres macros: "dia", "tarde" y "noche". Cuando accionamos la macro "dia" aparece en pantalla el mensaje "Buenos días"; cuando accionamos la macro "tarde", aparece en pantalla el mensaje "Buenas tardes". La tercer opción la dejo librada a la imaginación de mis perspicaces lectores.



En este modelo usamos los siguientes elementos:

  1. una lista desplegable con los nombres de las macros; la lista la creamos con validación de datos-lista
  2. tres macros: "dia", "tarde" y "noche"
  3. un evento de tipo Worksheet_Change que ponemos en el módulo de la hoja correspondiente (en nuestro caso la hoja donde está la lista deslegable)-

Los pasos:

  • Creamos la lista desplegable



  • Creamos las macros



Nótese que las macros van en un módulo común del editor de Vba.

  • Creamos el evento



El código del evento

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strToCall As String
 
    strToCall = Range("C2").Value
 
    On Error Resume Next
    If Target.Address = "$C$2" Then Application.Run strToCall
    On Error GoTo 0
 
End Sub


debe ir en el módulo de la hoja. La función del evento es accionar la macro elegida cuando cambia el valor de la celda C2 que contiene la lista desplegable.
La instrucción On Error evita que la macro se detenga con un error cuando el usuario borra el valor de la celda C2 y ésta queda vacía.


Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

39 comments:

Reynerio Lanzas,  13 noviembre, 2010 17:59  

Hola Jorge.

Excelente. Muchas veces nos hemos encontrado frente a esta necesidad. Solamente para estar seguro. ¿Funciona con un cuadro combinado?

Jorge L. Dunkelman 13 noviembre, 2010 19:57  

Si, pero con ciertos cambios. Además hay dos tipos de cuadros combinados que se pueden usar en Excel: el de la barra de formularios y el control ActiveX.
En cada caso, la programación es distinta ya que el cuadro combinado de la barra de formularios no tiene eventos, pero se le puede asignar una macro. El cuadro combinado de los controles ActiveX se le puede programar eventos.
En definitiva, será el tema de la próxima nota.

Edicson (Venezuela),  18 noviembre, 2010 00:22  

Jorge excelente nota, era lo que estaba buscando, ya que me estaba preguntando como hacer hipervinculos utilizando una lista desplegable, se que tienes otros post al respecto pero así me parece más directo.

Me gustaría que terminaras de completar tu post de Gráficos Enlazados ya que falto la forma de enlazarlo cuando el gráfico está en la misma hoja (tecnica Class Modules)y según tu nota del 11 de marzo de 2008, iba a ser tema de tu próxima nota, pero nunca la publicaste o por lo menos yo no la he conseguido.

Gracias.

Jorge L. Dunkelman 18 noviembre, 2010 17:13  

Ciertamente, nunca publiqué la nota sobre eventos de gráficos insertados en hojas de cálculo. Sucede que dejé de escribir en el blog sobre gráficos. Veremos si encuentro el tiempo disponible en el futuro más o menos cercano...

AU 18 enero, 2011 07:10  

Hola Jorge, gracias por tus aportes.
Tengo una consulta: A partir de una lista de validacion seleccionar un elemento de la lista, pero que el elemento que se registre en la hoja sea otro que se encuentra mapeado en la hoja de cálculo. Ejemplo: Aparece en la lista desplegable el nombre de un producto, pero que en la celda correspondiente se registre el código del producto al seleccionar el nombre de la lista.
Agradeciendo tu ayuda.

Jorge L. Dunkelman 18 enero, 2011 17:59  

Hola AU,
en la celda con la validación de datos no se puede poner ningún valor que no cumpla con las reglas de la validación. Ahora, puedes usar el valor de la lista como argumento en una fórmula con BUSCARV, por ejemplo, para poner el valor buscado en otra celda.

Anónimo,  04 febrero, 2011 21:50  

Estimado Jorge, buenas tardes y es un placer saludarte de nuevo, estoy haciendo un directorio de mis clientes donde tengo todos los datos en una hoja y en otra hoja tengo una macro que al darle correr me oculta las filas cuyo valor sea 0, mi petición es si me puedes pasar el código para agregarlo a la macro y que cuando seleccione un cliente de la lista desplegable la ejecute sin tener que activarla manualmente

Un saludo y un abrazo

David Cepeda R

Anónimo,  07 febrero, 2011 20:52  

Estimado Jorge, buenos días

Te dejo el código que tengo grabado en la hoja


Sub OcultaFilasConValorCero()
MuestraTodasFilas

Fila = 1

Do While Range("B" + Trim(Str(Fila))).Value <> ""
If Range("B" + Trim(Str(Fila))).Value = 0 Then
Rows(Fila).EntireRow.Hidden = True
End If
Fila = Fila + 1
Loop
Range("A1").Select
End Sub


Sub MuestraTodasFilas()
Cells.Select
Selection.EntireRow.Hidden = False
Range("B2").Select
End Sub


En la celda "B2" tengo una lista desplegable (con el nombre de cada cliente, y a partir de la celda "B3" hacia abajo por medio de la función:

CONSULTAV($B$2,'BASE DE DATOS CLIENTES'!$A$2:$AD$498,2,FALSO)

Jalo la información que tengo capturada en una base de datos, pero tengo algunos valores en 0, y esas son las filas que oculto con la macro mencionada, la ayuda que te solicito es que al seleccionar de la lista desplegable, automáticamente me oculte las filas que en la columna "B" sea igual a cero

quedo en espera y de antemano te agradezco tus finas atenciones

David Cepeda R

Jorge L. Dunkelman 09 febrero, 2011 20:05  

David,

para eso tienes que programar un evento. Si no estás familiarizado con el tema puedes hacer una búsqueda en el blog con el tema "evento" o en el enlace "Macros".

Anónimo,  18 febrero, 2011 11:05  

Hola Jorge, Gracias por tu ayuda.
He probado a activar una macro desde una lista desplegable y funciona perfecto cuando el nombre de las hojas es texto, pero cuando es un número no funciona. Haber si puedes ayudarme
Gracias

Jorge L. Dunkelman 19 febrero, 2011 07:47  

En la nota no hay ninguna referencia al nombre de la hoja por lo que no puede orientarte. Tendría que mandarme tu archivo para que pueda ver donde está el problema.

Anónimo,  23 diciembre, 2011 19:44  

Hola Jorge. primero quiero felicitarte por tan grandioso aporte que haces, paso a comentarte lo siguiente, quiero hacer una macro que desde una tabla dinámica, específicamente un campo me genere hojas individuales... con su respectivo nombre, ya he pillado por ahí algo de lo que te comento pero el lio es que me tocaría hacer click en cada uno de ellos.. Entonces imagínate el lio si fuesen 1000 o más registros.. La idea es que sea automático, es decir que con un solo click se haga la labor asignada..por espacio del post te paso el codigo en otro... por tu colaboracion y orientacion Gracias.

Jorge L. Dunkelman 23 diciembre, 2011 21:00  

La técnica pra crear hojas a partir de una lista la he mostrado en esta nota.

Anónimo,  26 diciembre, 2011 22:35  

Hola nuevamente jorge, con respecto a lo que respondiste sobre crear hojas, te comento el link que pusiste no funciona, pero igual buscando creo haberlo encontrado, pero el lio que tengo es que me crea hojas siempre y cuando esten en lista, pero no los datos traidos desde una tabla dinamica, me explico, en la casilla b3 tengo la seleccion de estaciones de trabajo, lo que quiero es: una vez filtrada la info mediante tabla dinamica, que cada una de las estaciones se muestren en hojas separadas son los datos filtrados. se logra dando click sobre cada estacion, pero imaginate que sean 1000 estaciones, esto me tiene loco y no se como sacarle automatico, me podrias tirar una manito... mil gracias.

Jorge L. Dunkelman 31 diciembre, 2011 19:06  

De acuerdo a tu definición, tendrías que crear una copia (solo valores) de la tabla dinámica en una nueva hoja. Esto también se hace con macros, pero como podrás comprender, requiere ciertos conocimientos que van más allá de lo que se puede poner en un comentario.

Anónimo,  03 enero, 2012 21:02  

Jorge, buenas tardes !!!!

Muchas gracias por tu aporte, pero tengo una duda, al selecionar la lista corre bien la macro pero el valor que quiero me traiga en otra celda no lo actualiza, tengo que pasar a otra celda y despues regresar para que lo actualice, existe alguna forma para que se actualice automaticamente, es decir al seleccionar la lista.

Gracias, espero haberme explicado.

Jorge L. Dunkelman 06 enero, 2012 14:46  

¿Podrías explicar un poco más que es lo que quieres hacer? La situación es: seleccionas un valor de la lista desplegable y ésto dispara una macro. ¿Qué es lo que hace la macro? ¿Trae un valor de dónde? ¿Es una valor o una fórmula (parece ser una fórmula ya que hablas de actualizar el valor)?
En fin, te sugiero que me envíes el archivo y que sigamos la consulta por mail privado.

Jimmy 22 mayo, 2012 01:58  

Jorge, buen dato
Tengo una pre.. este codigo esta hecho parauna celda especifica, si yo lo quiero para varias celdas, como hago? ej, quiero que me ejegute la macro cuando cambie el valor de cualquier celda de la coumna "J". He intentado con este codigo pero no me funciona, que puedo hacer.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strToCall As String

'strToCall = Range("J8").Value
strToCall = Range("J" & ActiveCell.Row).Value
On Error Resume Next
'If Target.Address = "$J$8" Then Application.Run strToCall
If Target.Address = "$J & $ActiveCell.Row" Then Application.Run strToCall
On Error GoTo 0


End Sub

Jorge L. Dunkelman 26 mayo, 2012 11:12  

Jimmy
tienes que extender el rango que activa el evento de acuerdo y usar la variable Target. Sería algo así (no he probado la macro):


Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Union(Target, Range("A1:A5")).Address = Range("A1:A5").Address Then
Application.Run Target.Value
End If
On Error GoTo 0

End Sub

Clau 29 octubre, 2013 17:45  

Gracias por el aporte, aprovecho para hacerte un comentario-pregunta a ver si me puedes seguir ayudando. Tengo un programa de entrenamiento que he basado en varios libros asociados con diferentes vínculos; para poder navegar dentro de las hojas del mismo libro y desde un libro a otro he utilizado botones con hípervínculos.
Hasta el momento me ha ido bien, el problema ha surgido cuando se ha incrementado el número de usuarios, esto, asociado a la cantidad de cálculos que he insertado en cada hoja ha hecho que las mismas se hayan vuelto muy pesadas, y como resultado la navegación entre las mismas extremadamente leeeenta.
Había pensado en la posibilidad de eliminar los botones para quitarle peso y hacer una lista desplegable con el nombre de cada hoja o libro al que desee ingresar y asociarlo a un hipervínculo o a una macro que me permita acceder a él clicando sobre el nombre de la hoja que aparezca en la lista, pero no he podido encontrar la función para dicha tarea.
Teniendo todos los nombres en una lista desplegable podría sacar todos los botones que están asociados a un hipervínculo para poder navegar, solo existiría una lista desplegable, es decir se reduciría considerablemente el número de elementos.
No se si podrás/an ayudarme o si existe la posibilidad de realizar tal función, desde ya te agradezco tu/vuestro interés y gracias por tu/vuestra docencia. Por cierto tengo Excel 2007.

Jorge Dunkelman 30 octubre, 2013 21:43  

¿Y por qué no usar la técnica que muestro en esta nota?

jose angel surco 29 marzo, 2014 07:12  

y si en vez de poner una lista desplegable con validacion lo hago con un formulario desplegable

Anónimo,  24 octubre, 2014 03:37  

Hola Jorge, una pregunta, es posible utilizando este método, activar una macro para copiar por ejemplo el contenido (un rango de celdas) de la hoja2 en la hoja1, conservando el formato??, mi problema esta en la copia del contenido
Saludos

Jorge Dunkelman 26 octubre, 2014 22:34  

Si, por supuesto. Para ver la orden de copia incluyendo el formato puedes usar la grabadora de macros y luego reeditar el código creado para adaptarlo al rango requerido o definir un variable para el rango.

Office 2010 al dia 21 noviembre, 2014 20:25  

Hola Jorge buenas tardes, una consulta con respecto a la lista desplegable que activa la macro, esto no funciona si la lista de validacion es de numeros ya que a los nombres de las macros no puedo colocarle numeros. Si es asi como haria. Gracias, tus comentarios anteriores son excelnetes...

Jorge Dunkelman 22 noviembre, 2014 17:25  

Lo que puedes hacer es crear una lista de nombres de las macros y en una celda poner la fórmula =INDICE(lista de nombres, celda con validación). La función INDICE da como resultado el nombre de la macro que corresponde al número y que puedes usar como argumento en el evento.

Efrain Alvarado 24 noviembre, 2014 08:38  

Hola me pueden ayudar, como puedo activar una lista desplegable que tengo en el libro de excel en un userform? gracias.

Jorge Dunkelman 24 noviembre, 2014 16:23  

Efrain, tendrías que ser más específico en tu consulta. Supongoq que lo te falta es el evento (macro) que active el userform.
Por lo general se hace con un evento del tipo Worksheet_SelectionChange, de manera que cuando el usuario selecciona la celda indicada (o una celda del rango indicado), aparezca el userform.
Tambipen puedes inscrustar una combobox directamente en una celda sin necesidad de ponerla en un userform.
El el blog hay muchas notas sobre el tema (en la nube de etiquetas apretar "Listas Desplegables").

Anónimo,  03 abril, 2015 00:03  

Excelente, práctico y muy funcional!!!

Anónimo,  06 abril, 2016 11:31  

Hola! Muy bien explicado.
Tengo un problema con la macro, a ver si me puedes ayudar.
Mi lista desplegable consta de números, guiones, etc. por lo tanto no me funciona la macro y no puedo modificar los nombres. Hay alguna manera de solucionarlo?
Muchas gracias!

Jorge Dunkelman 08 abril, 2016 07:00  

Tendría que ver tu cuaderno para darme una idea. Los nombres pueden llevar guiones y números, pero no pueden contener espacios o ser iguales a direcciones de celdas (por ejemplo, ABC123).
Lo que se hae por lo general es definir el nombre en forma "legal" (por ejemplo ABC_123) y hacer la transformación en en el código o con fórmulas.

Anónimo,  21 abril, 2016 08:47  

Hola de nuevo:
Mi macro es la siguiente:
Sub E-45025()
Range("B7:F24").Select
Selection.Clear
Sheets("DATOS").Select
Range("A2:E16").Select
Selection.Copy
Sheets("HOJA").Select
Range("B7").Select
ActiveSheet.Paste
End Sub

El fallo lo marca en el nombre (E-45025), sin embargo si lo cambio por una palabra la macro funciona correctamente.

Todos los datos de mi lista desplegable son códigos de ese estilo con guiones, barras separadoras y puntos.
¿Habría alguna solución?

Muchas gracias por su ayuda.

Jorge Dunkelman 21 abril, 2016 17:24  

En principio no veo ningún conflicto en e nombre de la rutina con las reglas de Vba (los nombres de las rutinas deben empezar con letras y no pueden contener espacios, puntos o los símbolos ! # @ y &).
Fijate si no hay otra rutina con el mismo nombre en el cuaderno. O sencillamente, usa otro nombre :)

Anónimo,  28 diciembre, 2016 18:13  

Hola Jorge!

Tu post me ayudó mucho, claramente explicado. Sin embargo, hay algo que quisiera saber si es posible hacer y ojalá pudieras ayudarme.

Logré que al seleccionar cierto elemento de la lista despegable en la celda K2, la macro diera formato a las celdas A2:J2 para distinguirlas de las demás. El formato que quiero crear tiene listas en todas las celdas de la columna K para dar formato al mismo rango de celdas pero en su propia fila. ¿Esto es posible?

Espero puedas ayudarme. Saludos.

Jorge Dunkelman 02 enero, 2017 18:31  

Hola, es posible por supuesto. Para darte una idea del código te sugiero que grabes la acción (aplicar el formato deseado al rango A2:J2) y luego adaptarlo con variables para que sea efectivo a todos los rangos deseados.
Una forma más sencilla de hacerlo sería con formato condicional. Puedes aplicar el formato condicional a todo el rango deseado, y cuando el valor seleccionado en la celda de la columna K es el indicado el rango A:J recibe el formato definido. Mucho más sencillo que escribir código.

Anónimo,  23 marzo, 2017 11:28  

Hola buen día,

Gracias por tu post, me ayudó un poco sin embargo, en mi caso quiero que al seleccionr un valor de la lista desplegable, me copie los datos de otra hoja y los pegue en la hoja donde esta la lista.
Ya cree las distintas macros para los valores de la lista, pero al seleccionar estos valores en la lista no corren las macros.
El evento está creado en la hoja donde se encuentra la lista.

Aquí estan las macro y el evento:

Sub Febrero2017()
'
' Febrero2017 Macro
'

'
Sheets("FICHA COMPAÑÍA FEB2017").Select
Range("C9:Z37").Select
Selection.Copy
Sheets("FICHA COMPAÑÍA").Select
Range("C9").Select
ActiveSheet.Paste
End Sub
Sub Enero2017()
'
' Enero2017 Macro
'

'
Sheets("FICHA COMPAÑÍA ENE2017").Select
Range("D6:AA34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FICHA COMPAÑÍA").Select
ActiveSheet.Paste
End Sub

Evento:

Private Sub Worksheet_change(ByVal Target As Range)

Dim strToCall As String
CallMonth = Range("C2").Value

On Error Resume Next
If Target.Address = "$D$3" Then Apliccation.Run strToCall
On Error GoTo 0

End Sub

Jorge Dunkelman 23 marzo, 2017 18:46  

Veo algunos porblemas en el evento:

la variable que contiene el nombre de la macro es "CallMonth" y se refiere a la celda C2, pero el evento es disparado por cambios en la celda D3 ????

la variable que contiene el nonbre de la macro es CallMonth pero en si se cumple la condición (cambios en la celda D3) el evento intenta activar la macro definida por la variable strToCall ???

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP