Uso de controles en hojas de Excel - Spin Button con valores no enteros

En la nota anterior vimos como superar la limitación del uso de números negativos en los controles Spin Button y Scroll Bar. En esta nota veremos un rodeo para usar números no enteros en estos controles.

Supongamos que queremos crear un Control de Número que vaya de 0 a 5, pero en saltos de 0.5 (en algunos países se usa la coma en lugar del punto para separar la parte decimal del número).

Excel nos deja ingresar un número no entero en la casilla Incremento,

pero al apretar Aceptar cambia la definición dejando sólo la parte entera del número. En  nuestro ejemplo, la casilla Incremento mostrará 0.

Como en la nota anterior, el rodeo consiste en usar una celda auxiliar. Siguiendo con nuestro ejemplo, el valor mínimo es 0; el máximo será 10 (resulta de dividir 5 por 0.5) y el incremento 1. En nuestro ejemplo la celda vinculada es A3 y en la celda A4 ponemos la fórmula =A3/2 o su equivalente =A3*0.5

En resumen, la norma es:

• mínimo: 0
• máximo: máximo deseado dividido por el incremento deseado
• incremento: 1
• en la celda auxiliar: celda vinculada multiplicada por el  incremento deseado (o dividida por el inverso del incremento deseado)

Asi que si quisiéramos ir de 0 a 10 con incrementos de 1/3, ponemos 30 en la casilla del máximo y en la celda auxiliar (A4) =A3/3

Si usamos el Control de Número (Spin Button) de la colección ActiveX podemos dar una solución sin usar una celda auxiliar programando eventos para definir el control dinámicamente de acuerdo a valores que ingresemos en celdas de la hoja.

Siguiendo con nuestro ejemplo, ponemos el valor máximo deseado (10) en la celda E3 y el valor de incremento enla celda E4. En las propiedades del control dejamos el valor de la propiedad LinkedCell (celda vinculada) en blanco

Tal como indicamos en la nota anerior, abrimos el editor de Vb en el módulo de la hoja que contiene el control seleccionando el control y apreando Ver Código. En el módulo de la hoja ponemos estos dos eventos:

El evento GotFocus define el valor máximo del control de acuerdo a los valores que ingresamos en las celda E3 y E4 y el evento Change pasa el valor calculado a la celda vinculada con cada cambio del control

Nuevos cursos Excel - Tablas Dinámicas y Macros

El entorno laboral actual exige conocimientos intermedios y avanzados de Excel. ¿Qué es Excel intermedio y avanzado?

Todo usuario novicio sabe usar fórmulas sencillas como SUMA o SI. El usuario avanzado

• conoce funciones avanzadas de búsqueda como BUSCARV, INDICE o COINCIDIR; funciones para calcular de acuerdo a varios criterios como SUMAPRODUCTO, SUMA.SI.CONJUNTO y otras; sabe crear nombres que se refieren a rangos y fórmulas y más.
• Sabe combinar distintas funciones para crear fórmulas eficientes; sabe como auditar y controlar sus fórmulas y conoce las distintas alternativas para cada problema.
• Sabe crear gráficos para presentar datos y análisis en forma eficiente y clara.
• Usa tablas dinámicas para analizar bases de datos en Excel o externas en forma eficiente. Las tablas dinámicas nos permiten realizar análisis de grandes masas de datos en cuestión de segundos y con unos pocos clics.
• Conoce y usa las macros más allá de la simple tarea de grabarlas. Los usuarios avanzados de Excel están familiarizados con Vba (el idioma de programación de Excel) y pueden escribir macros para automatizar su trabajo diario, ahorrando tiempo y dinero.

Todo esto y mucho más puede aprenderse en los excelentes cursos en línea de Excelforo. Para más detalles e inscribirse en los cursos de marzo y abril, haz un clic en el banner

Uso de controles en hojas de Excel - Spin Button con valores negativos

Desde casi los primeros días de este blog he escrito sobre el uso de controles en hojas de cálculos. Los usos son casi ilimitados: dashboards, gráficos dinámicos, listas desplegables, etc.
Excel cuenta con dos colecciones de controles: Formulario y Activex. Ya hemos escrito sobre las ventajas y desventajas de cada una de estas colecciones. En esta nota veremos como sobreponerse a las limitaciónes de los controles Control de Número (Spin Button) y Barra de Desplazamiento (Scroll Bar)

• aceptan sólo números enteros
• no aceptan números negativos

En esta nota veremos un rodeo para poder usar números negativos en estos controles.

Por ejemplo, si queremos usar el control de números (spin button) de la colección Formlarios con valores que vayan de -10 a 10, al tratar de definir el valor mínimo veremos lo siguiente

Para superar esta limitación, siguiendo con nuestro ejemplo,usamos las siguientes definiciones:

Definimos 0 en el valor mínimo y el doble del máximo deseado para el Valor Máximo. En nuestro ejemplo, el valor del control está ligado a la celda A3; en la celda A4 ponemos la fórmula =A3-10. La celda A4 mostrará los valores deseado al accionar el control

Si usamos el control Activex la situación es diferente. Aparentemente Excel acepta el número negativo en la definición del mínimo

Pero al tratar de usar el control veremos lo siguiente:

al descender de 0, en lugar de -1 Excel poner en la celda ligada 65535!! (suena familiar, no es cierto? 65536 es el número máximo de filas en las versiones anteriores a Excel 2007).

En el caso del control ActiveX, la solución consiste en programar un evento para el objeto. En el menú Desarrollador apretamos el botón Modo de Diseñño, seleccionamos el control y activamos la opción Ver Código

Al apretar Ver Código, el editor de Vb se abre en el módulo de la hoja; allí ponemos este código

Con este código el control pasa los números deseados, también los negativos. La ventaja de usar el control ActiveX es que no necesitamos agregar una celda auxiliar.

En la próxima nota veremos como usar estos controles con valores no enteros.