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.