Validación de datos - Tamaño de la fuente de la lista desplegable

domingo, mayo 22, 2011

En la última semana tres lectores me han consultado sobre el mismo tema: el tamaño de la fuente en las listas desplegables creadas con validación de datos.

El tamaño de la fuente en estas listas está definido por defecto y no puede ser cambiado. Esto genera dos problemas:

1 – Algunos de los valores de la lista desplegable aparecen “cortados”



2 – Si el zoom de la hoja está por debajo del 100%, los valores puede llegar a ser casi ilegibles



En esta nota mostraré dos estrategias para solucionar estos problemas:

1 – usar eventos para cambiar el ancho de la columna que contiene la celda con la validación de datos y el nivel de zoom de la hoja;

2 – usar una combobox de la colección de controles ActiveX.

Partimos de esta situación donde tenemos una lista de departamentos de una empresa, que hemos incluido en el nombre definido “lstDepartamentos” y una lista desplegable en la celda E2. Las columnas A, B y C nos sirven como columnas auxiliares y en aplicaciones prácticas estarán ocultas. EL nivel de zoom es 80%




Solución con un evento simple.


La estrategia es programar un evento de manera que cuando el usuario seleccione la celda que contiene la lista (E2), el ancho de la columna E pase a ser 30 (antes del evento es 15) y el zoom será del 120%. Al seleccionar cualquier celda fuera de E2, el zoom vuelve a ser del 80% y el ancho de la columna 15.
En el módulo de la hoja correspondiente ponemos este código


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Range("E2").ColumnWidth = 30
    Else
        Range("E2").ColumnWidth = 15
        ActiveWindow.Zoom = 80
    End If

End Sub



video



Solución con evento complejo


La estrategia es la misma que en la solución anterior, pero el ancho de la columna será fijado dinámicamente de acuerdo al valor de la lista más largo. Para esto creamos una función UDF (definida por el usuario) para determinar este valor y lo pasamos al código del evento


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim iColWidth As Double
'
    iColWidth = max_len_in_range(Range("lstDepartamentos"))
      
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Target.ColumnWidth = iColWidth
    Else
        ActiveWindow.Zoom = 80
        Range("E2").ColumnWidth = 15
    End If

End Sub


Private Function max_len_in_range(rngList As Range)
    Dim iR As Long
    Dim tmpLen As Integer
  
    For iR = 2 To rngList.Count
        If Len(rngList(iR)) > Len(rngList(iR - 1)) Then
            tmpLen = Len(rngList(iR))
        End If
    Next iR
  
    max_len_in_range = tmpLen
  
End Function

Solución con ComboBox de la colección ActiveX


La ventaja de los controles ActiveX es que sus propiedades y sus eventos pueden ser programados. Esto nos permite “detectar” cuando el usuario activa el control y también cuando lo desactiva. Programamos un evento para cada una de las situaciones:


Private Sub ComboBox1_GotFocus()
    ActiveWindow.Zoom = 120
    Application.EnableEvents = False
    Me.Select
    Application.EnableEvents = True
End Sub

Private Sub ComboBox1_LostFocus()
ActiveWindow.Zoom = 80
End Sub


Los códigos hay que ponerlos en el módulo de la hoja correspondiente.

El archivo con los ejemplos se puede descargar aquí.


37 comments:

Amadeo 22 mayo, 2011 22:33  

Hola Joge

Acabo de verlo...

Está muy bueno. Lo tonto es que lo se hacer y no se me ocurrió aunque sea el evento simple.

De todas formas muchísimas gracias por tu aporte desinteresado

Como siempre muchos saludos

Amadeo



No es

Jean,  23 mayo, 2011 20:18  

Jorge,

Interesante y sencillas las soluciones, son buenos trucos que siempre resultan prácticos.
Muchas gracias y felicitaciones nuevamente por el blog.

Jean

Amadeo 08 junio, 2011 20:20  

Hola

Cómo hacer para que el ComboBox me devuelva valor y no un número en forma de texto?

Lo obtengo de una tabla cuya 1º columna son nº y no textos.

Saludándote atte estoy a la espera de una posible solución

Jorge L. Dunkelman 09 junio, 2011 06:31  

Hay varias soluciones posibles (evento, celda auxiliar con fórmula, convertir el texto en número dentro la fórmula que lo usa, etc.). Básicamente se trata de convertir el resultado de la combobox (texto) en número. Para evaluar la mejor solución tendría que conocer todo el contexto.
Como tu consulta no está relacionada con el tema de la nbota, te sufgiero que sigamos la charla por mail privado.

Juanjo,  17 marzo, 2012 10:11  

CUANDO DICES:"En el módulo de la hoja correspondiente ponemos este código"
NO SÉ QUÉ ES EL MÓDULO PARA PONER EL CÓDIGO.
AGRADECERÍA ME COMENTASES.
MUY AGRADECIDO Juanjo

Jorge L. Dunkelman 19 marzo, 2012 19:39  

Se trata del código del evento y éste va en el módulo de la hoja. Para acceder a ese módulo puedes hacer un clic con el botón derecho del mouse a la pestaña de la hoja y elegir la opción "ver código". En el blog hay varias notas sobre el tema eventos, ésta el la primera de la serie

Anónimo,  07 mayo, 2012 17:53  

Hola!
Este código no me funcionó en excel 2010. Qué cambios debo realizar para que funcione?.

Jorge L. Dunkelman 07 mayo, 2012 20:43  

Bueno, como se puede ver en las imágenes, el ejemplo fue desarrollado en Excel 2010. Te sugiero qeu descargues el archivo con el ejemplo.

Anónimo,  17 agosto, 2012 05:36  

Buenas Tardes, como cambiaría el tamaño de la lista desplegable de un Celda COMBINADA?

Jorge L. Dunkelman 17 agosto, 2012 11:53  

¿Te refieres a un cuadro combinado(combobox)? Si es el caso, hay dos tipos de controles: el cuadro combinado de la colección Formulario y el control ActiveX.
En el primer caso cambiando el valor de "Lineas de unión verricals" en el cuadro de propiedades del control.
En el control ActiveX se puede hacer manualmente cambiando el valor de la propiedad ListFillRows en el cuadro de propiedades o programando un evento que haga el cambio.

Anónimo,  24 noviembre, 2012 15:31  

Buenísimo! Creo que es lo que necesito. Gracias Jorge.
Lo voy a intentar con excel 2010.
Mi duda es si va a ser visible para gente que no tenga excel 2010, ni la opción de Programador, ni ActiveX...
¿Alquien sabría decirme?
Un saludo!
Patricia

Jorge L. Dunkelman 24 noviembre, 2012 17:20  

Patricia,
también las versiones anteriores de Excel (2003, 200) incluyen los controles ActiveX y Vba (macros). Lo único que hay que asegurarse que el nivel de seguridad permita correr macros.

Anónimo,  25 noviembre, 2012 18:47  

Gracias Jorge. ¡Enhorabuena por el blog! Lo recomendaré!!! Lástima que desde el trabajo el firewall no me deje entrar a blogspot...

De momento no he conseguido que me haga zoom en toda una columna, pero sigo intentando.
Patricia

andres 27 noviembre, 2012 17:53  

Gracias por tu ayuda, me gustaría saber si es posible que el zoom funcionara no solo en una celda en el ejemplo simple, sino en la columna entera, o un área determinada, me sería de gran ayuda.

Gracias de antemano.

Jorge L. Dunkelman 28 noviembre, 2012 07:09  

Andrés, el zoom se aplica a toda la hoja; el cambio en el ancho se aplica a toda la columna. Esa es la forma en que Excel funciona, por eso no entiendo tu consulta.

andres 28 noviembre, 2012 10:10  

Disculpa tal vez me he expresado mal, quisiera que este zoom funcionara no solo al seleccionar en una celda, sino al seleccionar cualquiera de un determinado rango (por ejemplo cualquiera de una determinada columna donde están los desplegables en todas las celdas).

Un saludo y gracias de antemano

Jorge L. Dunkelman 30 noviembre, 2012 15:59  

En la solución con el evento, en klugar de referirlo a una única celda con
If Target.Address = "$E$2"
tendríamos que referirlo a todo el rango. Por ejemplo para que se refiera a toda la columna E usamos

If Target.Column = 5

Anónimo,  05 junio, 2013 18:14  

Jorge, tengo el mismo problema que Andrés solo que necesito que el zoom se active en tres rangos de celdas consecutivas de las columans F, G y H serian la columna 6, 7 y 8 pero solo me lo toma para la última es decir la 8

Jorge Dunkelman 12 junio, 2013 20:57  

No se cuál de las soluciones estás usando, pero en el evento simple tendrías que reemplazar la referencia a una única columna por una referencia a todo el rango de las tres columnas. Por ejemplo

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Union(Target, Range("F:H")).Column = Range("F:H").Column Then
ActiveWindow.Zoom = 120
Range("F:H").ColumnWidth = 25
Else
ActiveWindow.Zoom = 80
Range("F:H").ColumnWidth = 15
End If

End Sub

San Braulio 28 julio, 2013 19:37  

Buen día Jorge, existe alguna manera de que en vez de colocar el código que comentaste la ultima vez en "el módulo de la hoja correspondiente" es decir, hoja por hoja, se pueda colocar en "ThisWorkbook" para que haga referencia a todas las hojas del libro, sin necesidad de estar copiando dicho código hoja por hoja?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Union(Target, Range("F:H")).Column = Range("F:H").Column Then
ActiveWindow.Zoom = 120
Range("F:H").ColumnWidth = 25
Else
ActiveWindow.Zoom = 80
Range("F:H").ColumnWidth = 15
End If

End Sub

Anónimo,  09 agosto, 2013 00:56  

Hola me pareció muy funcional e interesante, ahora mi duda es: ¿cómo aplico el evento para diversas listas? ejemplo: tengo una lista de validación de datos en F5 y tengo otra en D2 y quizás otra en J8, ¿cómo hacer que el evento aplique con las 3 listas al seleccionarlas cada una?

Jorge Dunkelman 12 agosto, 2013 07:39  

Se puede repetir dentro del evento el código para cada celda o usar Or

If Target.Address = "$E$2" Or Target.Address="$D$2 Or...Then

Anónimo,  11 octubre, 2013 22:38  

Podria desarrollar el ejemplo If Target.Address = "$E$2" Or Target.Address="$D$2 Or...Then , soy nueva en esto de VB y me esta dando un error de no coincide tipo y ni idea

ENRIQUE ROSAS 10 marzo, 2014 18:01  

disculpa el codigo es genial y sencillo pero cuando es un renglon que tiene dos por ejemplo estas en el renglon E2 Pero ese renglon le aplique marge & center fucione lo que era el E2 y E3 y no funciona el primer codigo creas poder ayudarme

Jorge Dunkelman 11 marzo, 2014 09:38  

Hola Enrique, una de las reglas de buenas prácticas en Excel es evitar el uso combinar y centrar (merge & center). Fijate en esta nota.
Existen alternativas al uso de combinar y centrar, por ejemplo usar "centrar en la selección" en el menú Formato de Celdas – Alineación.
Si no puedes evitar el uso de combinar y centrar, puedes fijarte en esta nota donde propongo una solución con Vba.

ENRIQUE ROSAS 11 marzo, 2014 14:43  

Gracias "Jorge" por el buen consejo y lo tomare de ahora en adelante, pues todo funciona exelente en las NOTAS que dejaste el problema es que ya teno muy abansado mi reporte y si son demaciadas celdas combinada en uno de los comentarios aqui das un codigo que es para toda la fila ese funciona pero cres que podrian ser para ciertos numero de renglones por ejemplo que fuera de ( E2 , E8 )

de antemano gracias por el consejo y si no se puede hacer nada con la pena pero are todo otrabes hahahahaha Gracias

ENRIQUE ROSAS 11 marzo, 2014 16:06  

A y disculpa tendras un correo al que te pueda enviar una consulta distinta a este tema te lo agradeceria

Jorge Dunkelman 11 marzo, 2014 16:19  

Hola Enrique, en la parte superior del blog hay un botón "Ayuda". Ahí podrás leer las condiciones para solicitar ayuda y ver mi dirección.
Una pregunta sin ningún ánimo de ofender (sinceramente y se te ofende me disculpo de antemano): todos los errores de ortografía en tu comentario ¿son intencionales o se trata de errores de tipeo?

Jhon Alex Espichan Uscamaita 20 marzo, 2014 01:13  

Hola, muy bueno tu codigo. Queria saber cmo lo aplicaria para un conjunto de celdas. Ya sea una columna de "n" celdas, una fila de "m celdas o una matriz de "nxm" celdas.
Estaré muy agradecido. Gracias,

Jorge Dunkelman 20 marzo, 2014 07:03  

Jhon, en el código del evento sencillo, por ejemplo, en lugar de

If Target.Address = "$E$2"

ponemos

If union(target,range("A1:C10")).address=range("A1:C10").address then

Esto hace que el evento responda a cambios en el rango A1:C10. Si queremos limitar el evento a determinadas celdas de una fila, por ejemplo A1;Z1, usamos range("A1:C10").

Jhon Alex Espichan Uscamaita 21 marzo, 2014 01:41  

Muchas gracias Jorge por tu aporte.
Saludos cordiales.

Carlos Kaifer Blanco 11 noviembre, 2014 20:06  

Buenas tardes Jorge.
Me resuelve las necesidades que tengo pero necesito que la solución de evento complejo sea aplicable a todas las celdas de una columna.
Soy neófito y me estoy volviendo loco para intentar añadirlo a tu código.
Puedes ayudarme??

Jorge Dunkelman 12 noviembre, 2014 07:52  

Hola Carlos, supongo que descargaste el archivo con el ejemplo. En el evento complejo el código calcula primero el ancho máximo de los valores de la lista de departamentos (el rango A3:A8 en la hoja) y de acuerdo a este valor determina el ancho de toda la columna. El evento corre cuando se selecciona la celda E2.
Si quieres que se aplique a toda la columna E, por ejemplo, tenés que hacer esta modificación

If Target.Column = 5 Then
ActiveWindow.Zoom = 120
-----------

Carlos Kaifer Blanco 12 noviembre, 2014 16:05  

Buenas tardes Jorge.
Perdóname pero siguiendo las consultas de otros compañeros he podido aprender esto y alguna otra cosa con posterioridad al envió de la consulta.
Felicidades por el blog y por enseñarnos a optimizar nuestro tiempo gracias a tu conocimiento.
Seguimos en contacto.

Carlos Kaifer Blanco 12 noviembre, 2014 17:59  

Buenas tardes Jorge.
Te plante cuestiones en las distintas líneas del código del evento complejo.

Dim iColWidth As Double
'
iColWidth = max_len_in_range(Range("lstDepartamentos"))
En esta parte del código necesito poder indicar distintas listas de una hoja excel. Como uno varias listas??. Como indico la celda donde está indicada la lista??

If Target.Column = 18 Or Target.Column = 27 Then
En esta parte del código debo aplicarlo a 8 columnas. Hay alguna forma de nombrarlas todas sin necesidad de repetir el código 8 veces.
ActiveWindow.Zoom = 120
Target.ColumnWidth = iColWidth
Else
ActiveWindow.Zoom = 80
Range("E2").ColumnWidth = 15
Jorge como puedo decirle que respete la medida que tienen las celdas en el diseño de la hoja, que no es siempre la misma?? No es siempre 15, por ejemplo.
End If

Jorge si quieres que te envié la hoja de excel que estoy preparando lo hago sin problemas para la empresa.


Jorge Dunkelman 13 noviembre, 2014 07:49  

Carlos, te sugiero que sigamos la consulta por mail privado (fijate en el enlace Ayuda, en la parte supeior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP