Excel - Agregando controles en hojas de cálculo

martes, marzo 28, 2006

Excel permite la inclusión de controles como botones de opción y listas desplegables en las hojas de cálculo. Los controles son objetos de interfaz gráfica que permite al usuario controlar acciones. Normalmente son utilizados en macros (Vba, Visual Basic for Applications) pero también se los puede usar en una hoja, sin que medie ninguna programación.
Las versiones más recientes de Excel tienen dos tipos de controles, los controles ActiveX (la barra de la izquierda en la figura) y los controles de la barra de herramientas Formularios



En esta entrada daremos un ejemplo práctico del uso de los controles de la barra de herramientas Formularios. No nos detendremos a analizar las diferencias entre los dos tipos de controles ni el uso de los ActiveX.

Como es costumbre de este blog, ilustraremos el uso de los controles con un ejemplo.

Supongamos que hemos diseñado un formulario en una hoja de cálculo Excel, para calcular precios de productos. El precio depende de si el cliente es miembro del club de compras y de las condiciones de pago. El formulario del controlsp01ejemplo se puede descargar aquí.



En este formulario hemos puesto 4 controles: una casilla de verificación (si el cliente es miembro del club marcamos la "V") y tres botones de opción para señalar la forma de pago.
Estos controles funcionan de la siguiente manera:
1 – Activamos la barra de formularios y pulsamos sobre el control requerido (en nuestro caso la casilla de verificación)
2 – ubicamos el control en el lugar requerido y abrimos el menú de formato del control



3 – en la división "control" creamos un vínculo con alguna celda de la hoja.



En esta celda Excel anotará un valor de acuerdo a al situación del control. Si la casilla está señalada ("v"), en la celda vinculada aparecerá el valor "VERDADERO"; de lo contrario Excel anotará el valor "FALSO". La celda vinculada que hemos elegido es A3 de manera que el control oculte el valor de la celda.
4 – Procedemos de la misma manera con los botones de opción. En este caso los tres controles están vinculados a la misma celda (A7 en nuestro ejemplo). El valor en la celda vinculada corresponde al orden de los controles. Es decir, si el control activo es el primero (Contado), el valor de la celda vinculada será "1". Si el control activo es "Hasta 30 días" el valor de la celda vinculada será "2" y así sucesivamente.
Los valores de las celdas vinculadas funcionan como argumentos en las fórmulas que calculan los descuentos

- en la celda C23 tenemos la fórmula =SI(A3=VERDADERO,D22*10%,0)
- en la celda C24 tenemos la fórmula =D22*ELEGIR(A7,10%,5%,0)

De esta manera, las opciones marcadas en los controles nos permiten "controlar" los resultados de las fórmulas.



En el rango A16:A21 usamos validación de datos, con la opción "lista". En la Hoja2 hemos puesto una lista de precios. En esta hoja hemos definidos dos nombres de rangos, uno para la lista de la validación de datos y el otro para la fórmula BUSCARV que calcula los precios de los productos en el rango C16:C21.
Para evitar valor erróneos (#N/A, #DIV/0!, etc), combinamos las funciones de búsqueda con la función de control ESBLANCO.

Categorías: Funciones&Formulas_, LOOKUPS_


Technorati Tags: , , ,

35 comments:

Lalo 20 julio, 2007 21:18  

Estimado Jorge: Muy, muy buena su pagina. Una pregunta. Quiero en una misma hoja usar el comando de opcion por ejemplo: Tipo de cliente Mayorista o Minorista (con el boton opcion de formularios) y en la misma hoja poner Tipo de Credito: 15 dias, 30 dias, 45 dias con el boton opcion. He probado y me linkea todo a una celda y crea 5 opciones, sin embargo necesito que una opcion tenga 2 posibilidades y la otra 3.

Jorge L. Dunkelman 21 julio, 2007 11:49  

Hola Lalo,
últimamente he recibido varias consultas sobre el tema, por lo que estaré publicando una nota explicando las distintas posibilidades.
Mientras tanto te comento que si estás usando el botón de opción de la barra de formularios, lo que tienes que hacer es agruparlos en un cuadro de grupos y asociar cada grupo a otra celda.
En mi futura entrada habrá explicaciones más detalladas.

Jorge L. Dunkelman 21 julio, 2007 15:22  

AL final terminé la nota más rápido de lo que pensaba.
Espero que les sirva.

Elkin Manuel 25 julio, 2007 15:00  

Hola Jorge, te felecito por tu pagina, era lo que andaba buscando. Mi pregunta es, si puedo hacer que el calendario salga horizontal en la hoja de excel y si puede salir todo el año. Lo que pasa es que quiero hacer una aplicacion para sacar los cuadros de turno de mi empresa.

Gracias por tu ayuda.

Jorge L. Dunkelman 29 julio, 2007 20:53  

Hola,
no creo que se pueda hacer ni lo uno ni lo otro. Lo que si se me ocurre es incrustar el control doce veces, una para cada mes, en un hoja; o crear una hoja para cada mes y luego crear una hoja que concentre todos los datos.

Anónimo,  25 octubre, 2007 18:31  

Hola Jorge como estas, antes que nada permiteme felicitarte por tan completa pagina, me gustaria saber si tienes ejemplos de solver...

Gracias
Roman Hernandez
Bogota - Colombia

Jorge L. Dunkelman 25 octubre, 2007 19:37  

Gracias Román. Puedes leer mi nota Uso del Solver en Excel donde muestro un ejemplo sencillo.

Anónimo,  08 diciembre, 2007 13:40  

Sr Dunkelman, sencillamente mucísimas gracias por su blog.

Es como una adicción a la par que infinitamente útil.

Después de ésto decirle que al intentar descargar el ejemplo en http://www.filelodge.com/files/room13/327240/SpFiles/controlsp01.xls no sale nada (pantalla en blanco con el texto "index" en el margen superior izquierdo).

Bueno siempre me pasa con los ejemplos alojados en "filelodge", al contrario que en "snips".

¿Podría obtener el ejemplo, por favor? Es que me interesa mucho ésta entrada.

Un saludo

Jorge L. Dunkelman 08 diciembre, 2007 20:55  

Gracias por los conceptos.
He arreglado el enlace y ahora se puede descargar el archivo.

Anónimo,  23 junio, 2008 14:26  

Hola,

tengo una duda sobre la creacion de los radio buttoms, y es que cuando los creo en mi pagina excel (2003) y accedo a sus propiedades no me aparece el campo contro y no puedo vincular celdas de otra hoja en el mismo libro y no se por que, por que tampoco me aparece la pestaña agregar macro ... me podeis ayudar.

Gracias y un saludo.

Jorge L. Dunkelman 23 junio, 2008 21:30  

Cual de los botones de opción quieres usar? El de la barra de formularios o el de los controles ActiveX?

Albertinho 23 diciembre, 2008 16:52  

Hola Jorge,

Estaba haciendo un formulario con varios controles de la barra de formularios y me preguntaba si era posible la siguiente opción: quiero que haya dos bloques de opciones (con casillas de verificación). Un bloque solo tiene una respuesta que puede ser elegida o no, pero si se elige ya no se deberían poder elegir las del otro bloque. Hay alguna forma de bloquearlas teniendo en cuenta esta condición?
Muchas gracias!

Humano sostenible 15 julio, 2009 22:42  

Hola y si por ejemplo el formulario es para generar una base de datos, cómo se activa para que vaya adicionando resultados? Bueno, no se si esto sea posible, al ver tu nota me surje esta duda

Jorge L. Dunkelman 16 julio, 2009 21:46  

Para crear una base de datos en Excel tienes que ir agrupando en una hoja las filas con los datos. Para eso tienes que usar una macro (o más propiamente, programar un evento). Un control sólo te sirve como interfaz con el usuario.

Anónimo,  30 setiembre, 2009 20:11  

Hola Jorge, que excelente pagina, pero tengo una duda, tengo una hoja de excel y la quiero proteger con contraseña, la hoja cuenta con botones de opcion pero cuando la protejo me bloquea el uso de dichos botones, como le puedo hacer para quitar ese bloqueo, ya intente varias formas pero ninguna me deja, te dejo mi correo y espero puedas ayudarme kirkv_halen@hotmail.com

Jorge L. Dunkelman 01 octubre, 2009 19:20  

Supongo que estás usando los controles de la barra de formularios y que están ligados a una celda de la hoja. Lo que hay que hacer es quitar la protección de la celda (formato de celdas, proteger y allí quitar la marca de "bloqueada")

Anónimo,  12 mayo, 2010 12:56  

Buen día. Que sucede cuando la pestaña "control" no aparece?. Estoy intentando usar un activeX scroll.

Gracias

Sophia

Jorge L. Dunkelman 12 mayo, 2010 17:13  

Hola Sophia,
¿qué versión de Excel estás usando?

ElProfe 16 mayo, 2010 03:15  

Saludos Jorge:

Andaba por la Internet buscando propiedades y características del botón de opcion de la barra de formularios de Excel y algo me mando a visitar tu blog.

Veo que tines un muy interesante sitio con temas y ejemplos útiles y prácticos y por eso te hago llegar mi gratitud y reconocimiento por el material que pones a disposición de tus lectores, pero te felicito de manera especial por la forma tan detallada que explicas cada tema.

Lo que andaba buscando en la Internet tiene que ver con los "Botones de opción" (hechos con la -Barra de formularios-).

Tengo las siguientes inquietudes:

1) Como se hace (o por donde se puede) para conocer (o saber) el nombre de cada botón?

2) Como se hace (o por donde se puede) para cambiar el nombre de los botones.

3) Como hacer automáticamente para que al inicial, los botones queden limpios; es decir con su "Valor sin activar" (el que se encuentra en la pestaña "Control" del "Formato de control")

Si en algo me puedes ayudar, te anticipo las gracias.

Un cordial saludo,

ElProfe

Jorge L. Dunkelman 16 mayo, 2010 08:38  

Los botones de la barra de formularios son sencillos y fáciles de usar, pero no se pueden programar. Todas tus preguntas, excepto la primera, tienen que ver con los controles ActiveX, que son los que aparecen en la barra Cuadro de Controles. Te sugiero leer mi nota sobre los controles ActiveX.
Encuanto al nombre de los controles, cuando apuntas al icono del control aparece un cuadro de texto con el nombre.

ElProfe 16 mayo, 2010 23:22  

Apreciado Jorge:

Muy agradecido por tu respuesta.

Miestras que esperaba alguna luz sobre mis inquietudes, encontré lo siguiente:

1) Como hacer para conocer el nombre que tiene cada botón?

-Encontré tes posibilidades, a saber:-
R1a) La más sencilla; al momento de crear el botón desde la "Barra de Herramientas - Formularios", el botón mismo aparece con el nombre "Botón de opción XX", donde XX es un número consecutivo que se incrementa en la medida en que se van creando mas botones.

R1b) Si el botón ya está creado y le habíamos cambido el nombre, hacemos clic derecho sobre el, para seleccionar el botón (que se vea su contorno que es como una dina malla) y mientras tanto en la parte superior izquierda de la hoja de Excel, justo en el espacio destinado al "Cuadro de nombres" se alcansa a apreciar parte del nombre original del boton (una limitación visual del campo mismo para no quitarle capacidad a la barra de fórmulas), donde se alcansa a leer algo así como "Botón de opci...", pero al hacer clic dentro del "Cuadro de nombres" se conoce el nombre completo del botón seleccionado.

R1c) Si el botón ya está creado y hemos cambido su nombre, hacemos un clic derecho sobre el, para seleccionar el botón y luego vamos a [Insertar][Nombre][Definir] y en la caja de dialogo "Definir nombre", en la sección "Se refiere a:", figura el nombre completo del botón seleccionado.

2) Como hacer para cambiar el nombre de un botón?

R2) "blanco es, gallina lo pone y frito se come"; como el refrán, si la respuesta de R1b) habló del "Cuadro de nombres", pues bastaría seleccionarlo como ya se dijo y allí mismo en el "Cuadro de nombres" podemos renombrarlo a nuestro gusto o necesidad.

3) Como hacer para que al inicio, los botones queden limpios automáticamente?

R3) definitivamente el problema era conocer el nombre del botón y una vez identificado este, pues hice un código (de los simples porque de VBA no se nada!), que más o menos es como sigue:

Sub Blanqueador()
' Colocamos botones de opción en blanco

' Botón Pregunta 1
ActiveSheet.Shapes("Botón 01").Select
With Selection
.Value = xlOff
End With

ActiveSheet.Shapes("Botón 02").Select
With Selection
.Value = xlOff
End With

ActiveSheet.Shapes("Botón 03").Select
With Selection
.Value = xlOff
End With

' Botón Pregunta 2
ActiveSheet.Shapes("Botón 04").Select
With Selection
.Value = xlOff
End With

ActiveSheet.Shapes("Botón 05").Select
With Selection
.Value = xlOff
End With

End Sub

...y listo! Eso era todo.

Supongo que "mi código se puede hacer más simple o más práctico", pero en lo que a mi respecta, funcionó bien y conseguí mi objetivo.

Agradezco tu amable atención y recibe un cordial saludo desde Bogotá.

ElProfe

Jorge L. Dunkelman 17 mayo, 2010 08:51  

Profe,
efectivamente, el código se puede hacer más simple:

Sub change_status()
Dim sh As Shape

On Error Resume Next
For Each sh In ActiveSheet.Shapes
sh.Select
Selection.Value = xlOff
Next sh
On Error GoTo 0

End Sub

Y si ya estamos usando Vba, podemos ver todos los nombres de los controles en la hoja en la ventana Inmediato del editor de VB (se abre con Ctrl+G) corriendo este código:

Sub show_shapes_names()

Dim sh As Shape

For Each sh In ActiveSheet.Shapes
Debug.Print sh.Name
Next sh

End Sub

ElProfe 17 mayo, 2010 18:08  

Saludos Jorge:

Muy valiosas tus orientaciones; definitivamente han sido de gran ayuda.

Otro experto de estos temas tambien me sugirió el siguiente código para "limpiar los botones"

Sub Limpiador()
Activesheet.optionbuttons.value = false
End Sub

Al igual que la sugerida por ti, funciona muy bien.

Gracias por tu ayuda y por tu interés.

Cordial saludo,

ElProfe

Lalo 30 junio, 2010 16:49  

Jorge, excelente blog. Una consulta: Se puede hacer que los botones funcionen tipo Formato Condicional? Si en la celda A1 se pone "Si" aparece el boton X de formulario (que tiene un macro asignada), si en la celda A1 se pone "No" aparece el boton Z (que tiene otra macro asignada) y desaparece el boton X.

Jorge L. Dunkelman 01 julio, 2010 06:42  

Lalo,
habría que crear los botones "al vuelo" programando un evento. Es decir, al cambiar el valor de la celda A1 se dispara una macro que crea el botón con todas sus características. En este blog hay una serie de notas que tratan el tema "eventos".

ElProfe 13 julio, 2010 17:50  

Muy buen día Jorge:

Atendiendo tu recomendación, con el código que me diste...

Sub show_shapes_names()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
Debug.Print sh.Name
Next sh
End Sub

...se despliegan, en la "Ventana Inmediato" del editor de VBA, todos los nombres de los diferentes botones (y formas) que tengo en la hoja seleccionada.

Lo anterior es en buena parte la respuesta a mi inquietud, por que me sucede lo siguiente:

Diseñaba un "Menú de opciones" en una hoja y debía crear para ello 12 botones con la barra de herramientas.

Al crear algunos de esos botones, aparecieron unas cuantas "copias fantasmas" de botones (podrían ser errores involuntarias de dedo que los duplican). De varios de ellos no logro establecer donde han quedado; solo se que están allí porque cuando corro tu código, son "delatados" en la lista de nombres.

El caso especial es un "botón fantasma", que se deja visualizar, pero no se deja borrar ni mover ni nada y si me impide ocultar las columnas que no estoy utilizando.

Mi inquietud Jorge, es:
Si, conocido el nombre de cada botón, incluidos los "fantasma", puedo borrar estos últimos desde el editor con un código VBA, a partir del nombre conocido, así no sepa la ubicación? Por favor, podrías darme una idea de eso código?

Gracias por tu amable atención y saludos.

Cordialmente,

ElProfe

Jorge L. Dunkelman 13 julio, 2010 19:21  

Profe,
para borrar un botón determinado usamos

Activesheet.Shapes("nombre del botón").Delete

Para borrar todos los botones de una hoja

Sub del_cb()
Dim s As Shape

For Each s In ActiveSheet.Shapes
s.Delete
Next
End Sub

Si no sabés la ubicación del botón, tendrás que hacer un bucle que pase de una hoja a otra hasta encontrar el botón buscado. Por ejemplo, si queremos borrar el botón cbTest1 sin daber en que hoja está, podemos usar

Sub del_cbTest1()
Dim s As Shape, wsh As Worksheet

On Error Resume Next
For Each wsh In ActiveWorkbook.Worksheets
wsh.Shapes("cbTest1").Delete
Next
On Error GoTo 0

End Sub

ElProfe 15 julio, 2010 16:17  

Saludos Jorge:

Muy eficientes los códigos recomendados.

Finalmente aparecieron 3 'fantasmas' que eran los causantes de que no pudiera ocultar columnas.

Queda solucionado el problema gracias a ti.

Muy amable por tu ayuda

Saludos,

ElProfe

Anónimo,  02 marzo, 2013 18:00  

Buen dia. espero que se encuentre de maravilla y gracias por este espacio.

mi duda es la siguiente. estoy insertando varios botones de control para un formulario el cual para cada pregunta tengan 5 posibles respuestas
llevava 5 preguntas cuando me di cuenta que todos los botones de control estan realcionados, no me respeta o mas bien no se como hacer para que el en formulario al 5o boton se corte la realcion con el 6o boton y que lo considere como nuevo y sobre todo como hacer para poder incluir difenretes celdas link para cada boton.

espero haberme explicado y realmente agradeceria su ayuda.

Jorge L. Dunkelman 02 marzo, 2013 18:53  

Fijate en las técnicas que muestro en esta nota.

Anónimo,  21 marzo, 2013 12:30  

Buenos días,
estupendo este blog. Me asalta una duda. ¿Cómo se puede proteger una hoja de calculo en excel 2003 con botones de opciones, para que estos se mantengan operativos?...aun desbloqueandolos en el control del formato, tanto en los botones de opción, cómo en el recuadro que los engloba, no consigo dar con la forma adecuada...muchas gracias de antemano!

Jorge L. Dunkelman 21 marzo, 2013 13:28  

Tienes que asgurarte que las celdas vinculadas al control no estén bloqueadas. El control en sí no está bloqueado.

Anónimo,  02 setiembre, 2013 18:38  

Buenos días, estoy haciendo un control de cheques emitidos y quería agregar al lado de cada uno una casilla que me permita tildar cada vez que se acredita uno y si es posible que ante esto se cambie el color de la fila. Es esto posible? Muchas gracias
Patricio

Jorge Dunkelman 02 setiembre, 2013 19:39  

Si, es posible aunque no me parece una buena idea, en particular si estás manejando cientos o miles de cheques. En mi opinión sería más fácil usar una columna para señalar el estado del cheque (digamos la columna Acreditado con los valores SI o NO) usando formato condicional para cambiar el color de la fila cuando el valor es SI.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP