martes, 19 de octubre de 2010

VBA: Cambio color de etiqueta.

Una curiosa cuestión que plantea un amigo del blog es cómo automatizar el cambio de color de la etiqueta de una Hoja cuando ocurra algo predeterminado en una hoja.:

...¿Hay forma de aplicar un formato condicional a la etiqueta de una hoja Excel 2007, para que cambie de color cuando se da una situación concreta?
Te explico el caso, llevo un libro en el que se procesa información de varias provincias. Cuando una de ellas me ha remitido toda su información, sería interesante que la etiqueta de la hoja (la que aparece en la parte inferior de la misma, cambiase de color, para informarme de que la información de esa hoja está completa....


El cambio de color de las etiquetas de las hojas de cálculo de mis Libros de trabajo es importante para mi, y hasta ahora siempre los cambiaba manualmente, pero ciertamente el automatizar este cambio de color, en función a que las hojas detrabajo estén completas o no, me parece algo útil.
para ello construiré una sencilla macro en VBA que mecanice el proceso.

El código siguiente se incluirá dentro del editor de VBA (Alt+F11) dentro de cada Hoja (no en un módulo), aplicando un evento change:


'macro que cambia el color la etiqueta de una hoja en función de una celda completada.
Private sub worksheet_change(ByVal target As Range)
Set etiqueta = ActiveWorkbook.Sheets(1).Tab
If Range("a1") <> "" Then etiqueta.ColorIndex = 3 Else etiqueta.ColorIndex = -4142
End sub



Con este código asociamos el cambio de color de la etiqueta de la 'Hoja 1' a que se encuentre algún valor en la celda A1.

12 comentarios:

  1. Sencillo y funcional.

    ResponderEliminar
  2. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  3. Hola, como deberia adaptar el codigo, si en vez "algun valor en la celda A1" tengo un "Boton de Opcion".
    Es un excel tipo encuesta con un formulario de opciones.
    Muchas Gracias

    ResponderEliminar
    Respuestas
    1. Hola, supongo hablas de un OptionButton en la hoja de cálculo...
      prueba con este código (son tres porcedimientos):
      Option Explicit
      Dim valorOpcionButton1 As Boolean

      Private Sub OptionButton1_Click()
      If OptionButton1.Value = True Then ActiveSheet.Tab.ColorIndex = 3 Else ActiveSheet.Tab.ColorIndex = -4142
      End Sub

      Private Sub OptionButton1_Change()
      Me.OptionButton1.Value = valorOpcionButton1
      End Sub

      Private Sub OptionButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      valorOpcionButton1 = Not valorOpcionButton1
      Me.OptionButton1.Value = valorOpcionButton1
      End Sub

      Según esté marcado o no el OptionButton la pestaña de la hoja sepondrá roja o no...
      Slds

      Eliminar
  4. Buenas noches Ismael

    Necesito adaptar una hoja con esas caracteristicas, cómo podría cambiar el color en esa pestaña si es que en la celda A1 aparece el valor 0?

    Gracias por la respuesta.

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      la idea podría ser la misma.. lanzar el proceso cuando algo cambie en la hoja..
      Podría ser entonces:
      Private sub worksheet_change(ByVal target As Range)
      Set etiqueta = ActiveWorkbook.Sheets(1).Tab
      If Range("a1") .value=0 Then etiqueta.ColorIndex = 3 Else etiqueta.ColorIndex = -4142
      End sub

      Así se realizará la comprobación cada vez que algo cambie en la hoja y se verificará el valor de la celda A1

      Saludos

      Eliminar
    2. Hola Ismael, buenas noches.

      Me tome el atrevimiento de usar el código que amablemente nos compartes con unos pequeños ajustes:

      Tengo 4 hojas, las tres primeras hojas en su celda A1 toman los valores de la cuarta hoja, de A1, A2 y A3, respectivamente, con la siguiente expresión:

      =SI(Hoja4!A1="","",Hoja4!A1)
      =SI(Hoja4!A2="","",Hoja4!A2)
      =SI(Hoja4!A3="","",Hoja4!A3)


      El inconveniente que tengo es que no cambia el color de la etiqueta a menos que lo haga de forma manual, sin estar formulado.

      El código en la hoja1 es:

      'macro que cambia el color la etiqueta de una hoja en función de una celda completada.
      Private Sub worksheet_change(ByVal target As Range)
      Set etiqueta = ActiveWorkbook.Sheets(1).Tab
      If Range("a1") <> "" Then etiqueta.ColorIndex = 3 Else etiqueta.ColorIndex = -4142
      End Sub

      El código en la hoja2 es:

      'macro que cambia el color la etiqueta de una hoja en función de una celda completada.
      Private Sub worksheet_change(ByVal target As Range)
      Set etiqueta = ActiveWorkbook.Sheets(2).Tab
      If Range("a1") <> "" Then etiqueta.ColorIndex = 3 Else etiqueta.ColorIndex = -4142
      End Sub

      Y el de la hoja3:

      'macro que cambia el color la etiqueta de una hoja en función de una celda completada.
      Private Sub worksheet_change(ByVal target As Range)
      Set etiqueta = ActiveWorkbook.Sheets("prueba").Tab
      If Range("a1").Value <> 0 Then etiqueta.ColorIndex = 3 Else etiqueta.ColorIndex = -4142
      End Sub

      Me podría ayudar a entender que sucede, que estoy haciedo mal.

      Eliminar
    3. Hola Marlon,
      el problema es que el evento Change se ejecuta cuando se modifica o se introduce algún valor en una celda y luego se valida (esto es, cuando lo cambiamos 'manualmente').
      Para lo que quieres tendríamos que emplear el evento _Calculate.
      Subiré un post al blog una explicación para el caso.

      Saludos

      Eliminar
  5. hola Ismael.
    Copie el primer código para los cambios en la celda A1, lo copie en la 3 pero se modifica el color de la hoja 1, que cambio have falta para que se modifique la hoja en la que grabo asigno la macro?.
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      entiendo te refieres al evento del post, si quieres que se ejecute en la hoja donde añades la macro sería:

      ...
      Set etiqueta = ActiveWorkbook.ActiveSheet.Tab
      If Range("a1") <> "" Then etiqueta.ColorIndex = 3 Else etiqueta.ColorIndex = -4142
      ...

      Saludos!

      Eliminar
  6. Hola Ismael, mi pregunta es, tengo un excel con muchas hojas de tres colores posibles, como hago para contar el total de los tres diferentes colores?
    Gracias y saludos

    ResponderEliminar
    Respuestas
    1. Hola Andrés
      podrías lanzar algo parecido a lo que sigue:
      Sub colores()

      For Each sh In Sheets
      Set etiqueta = sh.Tab
      If etiqueta.Color = 192 Then x = x + 1
      Next sh

      MsgBox x
      End Sub

      el valor 192 corresponde al color a buscar...
      Puedes cambiar la condición para incorporar otros colores, o emplear un OR para añadir los tres a la vez

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.