Agregando valores a un cuadro combinado (ComboBox).

Después de publicar mi nota sobre autocompletar en Validación de Datos, algunos lectores me señalan que no logran encontrar la propiedad ListFillRange donde tienen que poner la referencia al rango que contiene los valores de la lista desplegable. Esto tanto con el control cuadro combinado como con el control cuadro de lista.

Existen ciertas diferencias entre los controles que agregamos directamente en una hoja de Excel y aquellos que agregamos a un UserForm en el editor de Visual Basic.

Cuando ponemos un control ActiveX directamente en la hoja vemos las propiedades del control activando el modo Diseño y abriendo el cuadro de propiedades (ambos iconos aparecen en la barra de Cuadro de Controles). Todo esto lo hemos mostrado en la nota mencionada.

Cuando creamos un cuadro combinado (ComboBox) o cuadro de lista (ListBox) sobre un Userform tenemos que activar el panel de propiedades del editor de Vb (VBE) para ver las propiedades




La propiedad que define los valores de cuadro o de la lista es RowSource. Para agregar valores al cuadro podemos usar una referencia a un rango en una hoja de Excel o podemos hacerlo usando código Vba.

Para ejemplificar el primer caso supongamos que en rango A1:A12 de la Hoja1 tenemos una lista de los meses del año, que podemos crear fácilmente con el menú Opciones-Listas Personalizadas.
En la propiedad RowSource del control ComboBox1 ponemos la referencia, con la sintaxis apropiada (ver el signo de exclamación entre el nombre de la hoja y la referencia al rango)



Para comprobar si nuestro control ha recibido los valores, seleccionamos el UserForm y apretamos F5. El UserForm aparecerá en la hoja activa (en realidad "sobre" la hoja) y podemos apretar la flecha para ver los valores disponibles



Otra posibilidad es agregar los valores usando programación. También aquí se nos abren dos posibilidades.
Si los valores se encuentran en un rango de una hoja, como en el caso que acabamos de mostrar, podemos usar un código como éste

Sub agregar_valores()

UserForm1.ComboBox1.RowSource = "Hoja1!A1:A12"

UserForm1.Show
End Sub

Al correr esta macro se abre el UserForm y podemos ver que los valores son los del rango.
Otra posibilidad es agregar los valores usando el método Add Item de Vba:

Sub add_val()
With UserForm1.ComboBox1
.RowSource = " "
.AddItem "Enero"
.AddItem "Febrero"
.AddItem "Marzo"
.AddItem "Abril"
.AddItem "Mayo"
.AddItem "Junio"
.AddItem "Julio"
.AddItem "Agosto"
.AddItem "Setiembre"
.AddItem "Octubre"
.AddItem "Noviembre"
.AddItem "Diciembre"
End With

UserForm1.Show
End Sub

En resumen, el manejo de las propiedades de los controles es distinto dependiendo si los hemos creado directamente en la hoja con la barra de Controles o han sido programados con Visual Basic.



Technorati Tags: MS Excel

Encabezamiento y pie de página en Excel.

Excel, tal como Word, permite incluir encabezamientos y pies de páginas que aparecen al imprimir las hojas. De esta manera podemos incluir información sobre las hojas o el cuaderno que estamos imprimiendo.
El proceso de agregar estos encabezamientos y pies de páginas en Excel es sencillo. Abrimos el diálogo de configuración de página con el menú Archivo- Configurar página



o pulsando el icono de vista preliminar



Si usamos este segundo método, pulsamos el botón Configurar para abrir el diálogo.



Tanto en el encabezamiento como para el pie de página existen tres secciones: derecha, izquierda y central. Para definir el contenido apretamos el botón de la zona deseada, nos ubicamos en la sección correspondiente y usamos el o los botones correspondientes.

Las posibilidades son (en Excel 2003):

&[Página]
&[Páginas]
&[Fecha]
&[Hora]
&[Ruta de acceso]&[Archivo]
&[Archivo]
&[Etiqueta]

Para incluir texto personalizado sencillamente lo escribimos en la sección deseada. Para cambiarle el formato a la fuente usamos el botón de formato (A).
También podemos incluir una imagen. Una vez incluida podemos usar el botón de configuración de imagen para cambiarle el tamaño.

Podemos combinar las distintas posibilidades, por ejemplo si la impresión incluye muchas hojas podemos definir un pie de página como este



con este resultado



También podemos ordenar la información en varias líneas usando Enter para crea una nueva.
Hasta aquí hemos resumido información básica que seguramente la mayoría de mis lectores conoce.

Ahora pasemos a algunas cuestiones más avanzadas.

A veces queremos agregar dinámicamente el nombre del usuario, tal como aparece en el sistema. Esto nos permite identificar quien guardó la página. Para esta tarea tenemos que usar una macro como ésta, donde usamos la función Environ de Vba para obtener el nombre del usuario

Sub Footer_user()
    Dim PS As PageSetup, WS As Worksheet
    
    Set PS = ActiveSheet.PageSetup
    
    PS.CenterFooter = Environ("username")
    
    
End Sub



Los encabezamientos y pies de página se definen a nivel de hoja. Para definirlos simultáneamente para más de una hoja tenemos que seleccionarlas previamente, abrir el menú Archivo-Configurar Página y apretar el botón Aceptar.
Para seleccionar varias hojas hacemos un clic a la etiqueta de cada una de las hojas a seleccionar mientras mantenemos apretado la tecla Ctrl. También podemos usar la tecla Mayúsculas para seleccionar un rango de hojas marcando la primer y la última hoja en el rango.
Para seleccionar todas las hojas del cuaderno usamos el menú contextual que se abre apuntado a la etiqueta de la hoja y apretando al botón derecho del mouse.



Para copiar la configuración de página de un cuaderno a otro tenemos dos posibilidades:

1 - Manualmente, movemos una hoja del cuaderno de origen al cuaderno al cual queremos copiar la configuración (asegurándonos que marcamos la opción "crear una copia"). Luego procedemos como señalamos más arriba.

2 - Usando una macro como ésta

Sub CopyHeaderFooter()
   Dim PS As PageSetup, WB As Workbook, WS As Worksheet
   Set PS = ActiveSheet.PageSetup
   For Each WB In Workbooks
     For Each WS In WB.Worksheets
       With WS.PageSetup
         .LeftHeader = PS.LeftHeader
         .CenterHeader = PS.CenterHeader
         .RightHeader = PS.RightHeader
         .LeftFooter = PS.LeftFooter
         .CenterFooter = PS.CenterFooter
         .RightFooter = PS.RightFooter
       End With
     Next
   Next
End Sub


Esta macro (tomada del sitio VitalNews) copia la configuración de página de la hoja activa a todas las hojas de todos los cuadernos abiertos al momento de correrla.

Technorati Tags: MS Excel

Autocompletar en Validación de Datos

Toda persona que haya usado Excel por algún tiempo conoce la funcionalidad Validación de Datos. Una de las características más "sexy" de esta funcionalidad es la posibilidad de crear listas desplegables con facilidad.

En notas anteriores ya hemos mostrado como podemos mejorar aún más esta función creando listas desplegables ordenadas o listas desplegables dependientes.

Pero lo que falta en validación de datos es la funcionalidad autocompletar. Esto es importante cuando tenemos listas con varias decenas o centenas de miembros.

Supongamos que creamos una lista desplegable con todos los países del mundo (o casi todos, según Wikipedia) . Esta lista tendrá 247 miembros. Para facilitar la búsqueda de un país determinado podemos ordenar la lista alfabéticamente, pero de todas maneras si buscamos Zimbabue o Yemen tendremos que pulsar el ratón o el teclado decenas de veces.
La solución es imitar en la lista de validación de datos el comportamiento Autocompletar como existe las celdas de una hoja. La idea es que si pulsamos B aparezca Bahamas, si pulsamos Br aparezca Brasil, etc.

Para lograr esto podemos incorporar a la hoja de Excel un control ActiveX. En este caso usaremos un cuadro combinado (ComboBox).



En nuestro modelo hemos puesto el cuadro combinado sobre la celda B1 y enseguida mostraremos como dirigimos la elección de la lista a la celda B3



Para poder seleccionar el objeto activamos primero el modo de diseño



Luego seleccionamos el cuadro combinado con un clic y abrimos el menú de las propiedades del objeto



En LinkedCell ponemos B3, el ListFillRange ponemos Hoja2!A1:A247 (que es donde tenemos la lista de países) y en MatchEntry ponemos 1-fmMatchEntryComplete



También podemos cambiar otras propiedades como la fuente y el aspecto del objeto.

Cerramos el cuadro de propiedades y cancelamos el modo de diseño haciendo clic sobre el icono.

Ahora al abrir la ventanilla del cuadro combinado vemos los primeros 8 miembros de la lista (también esta propiedad puede ser cambiada en el cuadro de propiedades del objeto). Los miembros aparecen en el orden en que se encuentran en el rango de origen.



Pero lo más interesante es que podemos escribir directamente en la ventanilla y los países aparecerán de acuerdo a las letras que hayamos escrito

Al poner B aparece automáticamente Bahamas



Si agregamos ahora "r", el valor se convierte en Brasil. Nótese que el valor en la celda ligada, B3, se actualiza instantáneamente

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Technorati Tags: MS Excel