sábado, noviembre 13, 2010

Activar una macro desde una lista desplegable

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.

43 comentarios:

  1. Hola Jorge.

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

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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.

    ResponderBorrar
  4. 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...

    ResponderBorrar
  5. 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.

    ResponderBorrar
  6. 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.

    ResponderBorrar
  7. 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

    ResponderBorrar
  8. 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

    ResponderBorrar
  9. 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".

    ResponderBorrar
  10. 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

    ResponderBorrar
  11. 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.

    ResponderBorrar
  12. 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.

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

    ResponderBorrar
  14. 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.

    ResponderBorrar
  15. 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.

    ResponderBorrar
  16. 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.

    ResponderBorrar
  17. ¿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.

    ResponderBorrar
  18. 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

    ResponderBorrar
  19. 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

    ResponderBorrar
  20. 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.

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

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

    ResponderBorrar
  23. 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

    ResponderBorrar
  24. 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.

    ResponderBorrar
  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...

    ResponderBorrar
  26. 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.

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

    ResponderBorrar
  28. 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").

    ResponderBorrar
  29. Excelente, práctico y muy funcional!!!

    ResponderBorrar
  30. 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!

    ResponderBorrar
  31. 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.

    ResponderBorrar
  32. 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.

    ResponderBorrar
  33. 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 :)

    ResponderBorrar
  34. 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.

    ResponderBorrar
  35. 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.

    ResponderBorrar
  36. 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

    ResponderBorrar
  37. 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 ???

    ResponderBorrar
  38. Buenos días, estoy haciendo una maco, para que con un cuadro combinado en una hoja, pueda cambiar el cuadro combinado de otra hoja diferente...
    Y no lo consigo, a ver si podrían ayudarme
    En la hoja 2 tengo el cuadro que quiero que active la macroen G2 y:

    Sub Listadesplegable3_AlCambiar()

    If Target.Address <> "Hoja1!$G$2" Then Exit Sub
    Call ListaDesplegable
    End Sub

    en el modulo 1 tengo:
    Sub ListaDesplegable()

    Dim intCaseSel As Integer

    intCaseSel = Sheets("Hoja1").Cells(2, "G").Value

    Select Case intCaseSel
    Case 1: Call Apertura
    Case 2: Call Enero
    Case 3: Call Febrero
    Case 4: Call Marzo
    Case 5: Call Abril
    Case 6: Call Mayo
    Case 7: Call Junio
    Case 8: Call Julio
    Case 9: Call Agosto
    Case 10: Call Septiembre
    Case 11: Call Octubre
    Case 12: Call Noviembre
    Case 13: Call Diciembre
    Case 14: Call Explotacion

    End Select

    End Sub

    Sub Apertura()
    '
    ' apertura Macro

    Application.Goto Reference:="Pyg!R2C8"

    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("H367").Select
    End Sub
    Sub Enero()
    '
    ' Enero Macro

    Application.Goto Reference:="Pyg!R2C8"
    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("H367").Select
    End Sub
    Sub Febrero()
    '
    ' Febrero Macro

    Application.Goto Reference:="Pyg!R2C8"
    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("H367").Select
    End Sub
    Sub Marzo()
    '
    ' Marzo Macro

    Application.Goto Reference:="Pyg!R2C8"
    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("H367").Select
    End Sub

    Y así hasta diciembre y resultado de explotación.
    En la hoja 1 tengo el cuadro combinado en H2, tiene 14 posiciones.

    cuando activo el cuadro de la tercera hoja, me sale un mensaje de error diciendo que puede que no tenga habilitadas las macros (me he asegurado de que si lo están), o que la macro no este disponible en el libro. Peero, si después le doy a la macro ListaDesplegable, funciona, asi que mal del todo no esta. Mi intención es que la macro es active automaticamente al elegir una opción del desplegable del cuadro combinado

    ResponderBorrar
  39. Hola José Luís,
    la rutina Listadesplegable3_AlCambiar() tiene que estar en el módulo de la hoja, no en un módulo común y tendría que ser


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = $G$2" then Call ListaDesplegable
    end sub

    Por supuesto el código va en el módulo de la Hoja1

    ResponderBorrar
  40. Por favor, quiero que alguien me diga como hacer para que cuando, al no existir en el listado de un cuadro desplegable condicionado, escribo un registro nuevo en dicho cuadro desplegable, pero en vez de señalarme el error, se active un macro para poder añadir el nuevo registro al listado

    ResponderBorrar
    Respuestas
    1. Te sugiero que dirijas tu consulta a alguno de los muchos foros de Vba. Obviamente no se puede resolver en el marco de un comentario.

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.