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
ejemplo 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: Controles en hoja Excel, BUSCARV, VLOOKUP, Funcion ELEGIR
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.
ResponderBorrarHola Lalo,
ResponderBorrarú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.
AL final terminé la nota más rápido de lo que pensaba.
ResponderBorrarEspero que les sirva.
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.
ResponderBorrarGracias por tu ayuda.
Hola,
ResponderBorrarno 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.
Hola Jorge como estas, antes que nada permiteme felicitarte por tan completa pagina, me gustaria saber si tienes ejemplos de solver...
ResponderBorrarGracias
Roman Hernandez
Bogota - Colombia
Gracias Román. Puedes leer mi nota Uso del Solver en Excel donde muestro un ejemplo sencillo.
ResponderBorrarSr Dunkelman, sencillamente mucísimas gracias por su blog.
ResponderBorrarEs 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
Gracias por los conceptos.
ResponderBorrarHe arreglado el enlace y ahora se puede descargar el archivo.
Hola,
ResponderBorrartengo 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.
Cual de los botones de opción quieres usar? El de la barra de formularios o el de los controles ActiveX?
ResponderBorrarHola Jorge,
ResponderBorrarEstaba 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!
Sólo con programación
ResponderBorrarHola 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
ResponderBorrarPara 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.
ResponderBorrarHola 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
ResponderBorrarSupongo 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")
ResponderBorrarBuen día. Que sucede cuando la pestaña "control" no aparece?. Estoy intentando usar un activeX scroll.
ResponderBorrarGracias
Sophia
Hola Sophia,
ResponderBorrar¿qué versión de Excel estás usando?
Saludos Jorge:
ResponderBorrarAndaba 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
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.
ResponderBorrarEncuanto al nombre de los controles, cuando apuntas al icono del control aparece un cuadro de texto con el nombre.
Apreciado Jorge:
ResponderBorrarMuy 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
Profe,
ResponderBorrarefectivamente, 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
Saludos Jorge:
ResponderBorrarMuy 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
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.
ResponderBorrarLalo,
ResponderBorrarhabrí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".
Muy buen día Jorge:
ResponderBorrarAtendiendo 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
Profe,
ResponderBorrarpara 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
Saludos Jorge:
ResponderBorrarMuy 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
Buen dia. espero que se encuentre de maravilla y gracias por este espacio.
ResponderBorrarmi 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.
Fijate en las técnicas que muestro en esta nota.
ResponderBorrarBuenos días,
ResponderBorrarestupendo 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!
Tienes que asgurarte que las celdas vinculadas al control no estén bloqueadas. El control en sí no está bloqueado.
ResponderBorrarBuenos 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
ResponderBorrarPatricio
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.
ResponderBorrarDon Jorge:
ResponderBorrarGran 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.
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.
ResponderBorrarSuponiendo 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
Miguel, ponte en contacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).
ResponderBorrar