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.

Definir el rango a imprimir según valor en celda

El lector Boris me consulta:

En una celda con datos tipo lista tengo 2 opciones: Factura, Boleta. ¿Es posible hacer una formula con la función SI, para definir el área de impresión, dependiendo de esas dos opciones?

Una vez más aclaremos que las funciones sólo saben hacer cálculos; no podemos hacer cambios estructurales en la hoja o el cuaderno con funciones.

Sin embargo sí podemos definir el rango del área de impresión basándonos en el valor de una celda. Lo haremos usando la técnica que mostré en la nota sobre área de impresión dinámica en Excel sin macros.

El truco consiste en usar nombres. Supongamos que definimos dos rango a imprimir: Area_1 y Area_2

Lo que hemos llamado Area_1 o Area_2 en el ejemplo puede ser factura y recibo o escenario 1 y escenario 2, etc.

Definimos cada uno de los rangos con nombres

El próximo paso es definir una lista desplegable sencilla con validación de datos y ponerla en una celda donde el usuario pueda elegir el rango a imprimir

El último paso es modificar la definición del nombre Area_de_impresion . Esto lo haremos con el asistente de nombres. En la definición del nombre predefinido "Area_de_impresión" ponemos la fórmula

=INDIRECTO(Hoja1!$C$2)

Si al abrir el Administrador de nombres "Área _de_impresión" no aparece, podemos crearlo definiendo un rango arbitrario en "Área de impresión" del menú Configurar página.

La fórmula =INDIRECTO(Hoja1!$C$2) traduce el texto que aparece en la celda C2, donde hemos puesto la lista desplegable, por el rango definido por el nombre que coincide con el valor de la celda.

Este video muestra el funcionamiento

Impedir ingreso de fórmulas en celdas de Excel

Excel viene provisto con un mecanismo para controlar el tipo de datos permitidos en un rango: Validación de datos. La herramienta controla el tipo de datos ingresado, es decir, constantes (números, fechas, texto), pero no evitará que el usuario ingrese una fórmula en el rango validado.

Este problema se me presentó cuando estaba desarrollando un formulario para ingresar datos de gastos

Este tipo de formularios basados en hojas de Excel requieren poco esfuerzo para construirlos. Los valores permitidos para cada uno de los campos están controlados con Validación de datos; los botones en la parte inferior permiten pasar los datos a otra hoja que hace las veces de base de datos; las filas y columnas innecesarias están simplemente ocultas.

El problema es que validación de datos no impide que una fórmula sea ingresada en las celdas de la columna Monto

Para evitar esto tenemos que usar un evento. En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("rngMonto")).Address = Range("rngMonto").Address Then
        If Target.HasFormula Then
            Target.ClearContents
            MsgBox "Solo valores, no formulas!"
        End If
    End If
   
End Sub

Al introducir un valor en el rango Monto, se dispara el código que evalúa si se trata de una fórmula con la propiedad HasFormula. En caso positivo el contenido de la celda (target) es borrado y aparece el mensaje informando que no está permitido introducir fórmulas.

Este video muestra el funcionamiento de la validación