martes, marzo 28, 2006

Excel - Agregando controles en hojas de cálculo

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: , , ,

38 comentarios:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Gracias

    Sophia

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    ResponderBorrar
  35. Don Jorge:
    Gran aporte con este tema.
    Acerca de la intervención de ElProfe, cual seria el código para eliminar toda las casillas de verificación seleccionadas incluyendo la fila donde se ubica y dejar aquellas que no estan seleccionadas.
    Gracias.

    ResponderBorrar
  36. Los controles no están ubicados en celdas por lo que no tenemos forma de identificar la fila donde se ubica. Podemos determinar la fila de la celda ligada (LinkedCell), pero no creo que esto sea lo que estás buscando. Te sugiero que te pongas en contacto conmigo por mail.

    Suponiendo que usamos controles de la colección Formulario, como en la nota, y quieras eliminar la fila de la celda ligada, podrías usar algo así como

    Sub eliminar_boton_fila()
    Dim btn As OptionButton

    For Each btn In ActiveSheet.OptionButtons
    If btn.Value <> 1 Then
    Range(btn.LinkedCell).EntireRow.Delete
    btn.Delete
    End If
    Next btn
    End Sub

    Pero hay que tomar en cuenta que en este

    ResponderBorrar
  37. Miguel, ponte en contacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar

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