Excel 2010: Macro para filtrar una hoja protegida

  • -

Excel 2010: Macro para filtrar una hoja protegida

No son pocas las veces que me toca lidiar con macros para Excel que deben trabajar sobre hojas protegidas. Sea para impedir que el usuario arruine una fórmula o simplemente para mantener un control total sobre lo que puede y no puede ingresarse. Y si además tenemos que darle al operador la comodidad de trabajar con filtros pero sin perder la protección de la planilla, se complica.

La tentación inmediata es crear nuestra propia clase para filtrar los datos desde nuestra aplicación pero siendo que las funciones de filtros incorporadas en Excel son tan prácticas y poderosas, esto sería una pérdida innecesaria de esfuerzo y tiempo. Entonces, cómo lo aprovechamos?

Sabemos que sobre una hoja protegida contra cambios, los filtros de Excel no están accesibles desde el panel de herramientas. Tampoco funcionarían desde un macro. Entonces, como primera medida, habrá que desproteger la hoja.

Imprescindible para un filtro es el rango de datos a filtrar, del cual tenemos que obtener almenos un dato de cada caso para que el usuario elija cual va a ser el criterio por el cual filtrar. Por ejemplo si tenemos esta tabla:

Ejemplo-Excel

 

Y quisiéramos que el usuario pueda filtrar los datos para un vendedor en particular, debemos decirle al filtro que su rango a filtrar será la columna B (Vendedor) y le ofrezca al operador que elija como criterio entre los vendedores Pérez, López y Martínez. Es decir, cada uno de los valores únicos sin repetición de esa columna. Para esto podríamos cargar una lista con los valores únicos y mostrarla al operador para que elija.

Finalmente hay que tomar el valor que el usuario eligió y ejecutar el filtro. Inmediatamente después, cómo no queremos que nada más sea modificado por fuera del macro, volvemos a proteger la hoja.

Suena fácil? Lo es.

Para el ejemplo vamos a asumir que el usuario quiere filtrar la columna correspondiente a la celda en la que está plantado en ese momento: ActiveCell.Column y activa el filtro a través de una macro a la que le asignamos la combinación de teclas CTRL+K. Tambien podría activarlo haciendo doble click sobre la celda si hacemos uso del evento BeforeDoubleClick (ThisWorkBook_BeforeDoubleClick) o lo que sea que se nos haya ocurrido.

Resumiendo, convengamos que los pasos necesarios son:

  1. Desproteger la hoja
  2. Setear como rango para el filtro, la columna activa
  3. Tomar los valores únicos de esa columna, cargarlos en una lista y ofrecerla al usuario
  4. Cuando el usuario elige el valor, activar el filtro
  5. Proteger la hoja nuevamente

Necesitamos un formulario (llamado Formulario) al que le incorporamos un ListBox (que llamamos Lista). Y para no enrollarnos con el evento ListBox.Change, el filtro se activará con un botón. Entonces agregamos un botón al que llamaremos Filtrar. A tipear entonces, el código queda así:

Sub preparaElFiltro

 Dim txcelda As String ‘ donde guardamos la dirección de la celda activa

     Dim txpartes As Variant  ‘ para obtener el desglose de la dirección

     Dim txcolumna As String ‘ contendrá la letra de la columna

     Dim columna As Integer ‘ para almacenar el número de la columna

     Dim ultimaFila As Integer ‘ guarda el número de la última fila ocupada de esa columna

     txcelda = ActiveCell.Address ‘ por ejemplo $B$2
     columna = ActiveCell.Column ‘ en este caso, sería 2

     txpartes = Split(txcelda, “$”) ‘ txpartes quedaría como un array: txpartes(0)=$, txpartes(1)=B, txpartes(2)=$, txpartes(3)=2
     txcolumna = txpartes(1) ‘ es decir: B

     Dim txrangoOri As String ‘ en esta variable guardaremos el rango final en formato string

     ActiveSheet.Unprotect “la_clave_que_sea” ‘ desprotegemos la hoja

     ‘ vamos al final de la hoja y simulamos un END – UP para ir a la última celda ocupada en esa columna. Si usamos Excel x64 podríamos poner 1048576…

     ultimaFila = Cells(65536, columna).End(xlUp).Row 

     txrangoOri = Range(Cells(2, columna), Cells(ultimaFila, columna)).Address ‘ guardamos en txrangoOri la dirección de toda la columna ocupada: B2:B22

     ‘ esos datos los vamos a copiar a una columna cualquiera (en este caso ZZ) para limpiar allí los repetidos

     Range(“ZZ:ZZ”).Delete ‘ limpio el destino para asegurarme que no haya datos de algún proceso anterior

     ‘ copiamos los valores originales a la columna dónde la limpiaremos
     Range(txrangoOri).Copy
     Range(“ZZ1”).PasteSpecial xlPasteAll

     ‘ tomo el rango completo que se ocupó en la columna ZZ para pasárselo al comando RemoveDuplicates
     ultimaFila = ActiveSheet.Cells(65536, 702).End(xlUp).Row ‘702 es la columna ZZ
     txrangoOri = Range(Cells(1, 702), Cells(ultimaFila, 702)).Address

     ‘ eliminamos los duplicados
     ActiveSheet.Range(txrangoOri).RemoveDuplicates Columns:=1, Header:=xlNo

     ‘vuelvo a armar ahora el rango limpio de repetidos
     ultimaFila = ActiveSheet.Cells(65536, 702).End(xlUp).Row ‘702 es la columna ZZ
     txrangoOri = Range(Cells(1, 702), Cells(ultimaFila, 702)).Address

     ‘cargamos el rango limpio de duplicados en la lista desde la que el usuario elegirá el criterio para el filtro

     Formulario.Lista.RowSource = txrangoOri

     ‘volvemos a proteger la hoja

     ActiveSheet.Protect “la_clave_que_sea” 

     Formulario.Show ‘ mostramos el formulario al usuario

End sub

‘ Y ahora el código asignado al botón que finalmente hará el trabajo

Private Sub Filtrar_Click()

     ‘como en el caso anterior, tomamos todos los datos de la celda en la que está parado el usuario para poder filtrar la columna correcta

     Dim quecelda As String

     quecelda = ActiveCell.Address

     Dim partes As Variant
     partes = Split(quecelda, “$”)

     Dim columna As String
     columna = partes(1)

     Dim txrango As String

     txrango = columna & “:” & columna

     ActiveSheet.Unrotect “la_clave_que_sea” ‘ desprotegemos la hoja

     Columns(txrango).Select  ‘ Seleccionamos todas la columna a filtrar

    Selection.AutoFilter  ‘ le activamos el filtro

     ‘ y le pasamos como criterio el valor que el usuario seleccionó en la lista
     Range(txrango).AutoFilter field:=1, Criteria1:=Lista.Value

     ActiveSheet.Protect “la_clave_que_sea” ‘ protegemos la hoja

     Formulario.Hide ‘quitamos el formulario

End Sub

 

Y esa es la idea básica. Luego cada uno podrá optimizar el modo en que referencia las celdas y los rangos y decidir que tanto quiere complicar el nivel de filtrado. Tal como está, con este script pueden acumularse filtros en tantas columnas como se desee, obteniendo, desde una base sencilla, filtros por varios criterios y columnas diferentes. No es una mala idea agregarle al formulario un botón QuitarFiltros, que permita volver la planilla a su estado natural, mostrando todos los datos:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Es decir, si había un filtro activo, lo desactiva. Esto debe hacerse, una vez más, desprotegiendo antes y volviendo a proteger la hoja despues.

 

 


Buscar en JADM

Biblioteca