proteccion y validacion de datos con macros

Supongamos que tenemos ciertos datos que proteger en nuestra hoja de cálculos. Lo mas problable es que vayamos al formato de celda (ctrol + 1), bloqueemos las celdas en cuestión y listo. Pero en versiones anteriores de Excel (antes de la 2003) nos encontraremos que luego de proteger la hoja, un sin número de herramientas estarán deshabilitadas. Esta falencia se vé, en parte, solucionada en versiones mas recientes, en donde un formulario nos brinda mas libertad y podremos configurar que es lo que deseamos proteger:

esta imagen corresponde a Excel 2007 y podemos agregar unas cuantas omisiones a la protección, lo cual nos da una gran flexibilidad.

[+/-] Ver el resto / Ocultar

Con las macros y mediante los eventos de VBA podemos también crear interesantes herramientas para proteger nuestras hojas y/o validar los datos que se colocan en ellas, sin quitar ninguna funcionalidad. Lo mas interesante es que si lo trabajamos a nivel ThisWorkbook no deberemos ir bloqueando hoja x hoja ni nada por el estilo.

Les muestro:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub
Este procedimiento se encuentra en la ventana de código del objeto ThisWorkbook, a la cual accedemos haciendo doble click sobre el mencionado nodo, desde el Explorador de Proyectos de VBA, dejándonos en nuestras manos la posibilidad de captar el evento "SheetChange", o sea "Cambios en una Hoja"

Noten que trae dos parámetros: Sh y Target, los cuales no son otra cosa que la hoja y el rango en donde VBA detectó un cambio.

Veamos un ejemplo muy sencillo e ilustrativo: quiero saber si alguien cambió el valor del rango A1 en la Hoja2. El código sería:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'si el nombre de la hoja es Hoja2
If Sh.Name = "Hoja2" Then
    'si la fila es 1 y la columna es 1 (A1)
    If Target.Row = 1 And Target.Column = 1 Then
        'muestro el nombre del rango, el nombre de la hoja y el
        'nuevo valor agregado por el usuario:
        MsgBox "ha cambiado el valor del rango " & Target.AddressLocal _
        & " en la hoja " & Sh.Name & vbCrLf & "El valor es: " & Target.Value
    End If
End If
End Sub

El ejemplo en una imagen con lo que sucede al ingresar un dato en A1:

Podemos ir mucho mas allá con esto. Me es factible controlar el tipo de datos:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'si la celda no contiene valores salgo del sub
If Target.Value = "" Then Exit Sub
'si el nombre de la hoja es Hoja2
If Sh.Name = "Hoja2" Then
    'si alguna de las celdas modificadas se encuentra dentro
    'del rango A1:E5 y el dato colocado NO es numérico borro
    'el valor ingresado:
    If Target.Row <= 10 And Target.Column <= 5 Then
        If Not IsNumeric(Target.Value) Then Target.Value = ""
    End If
End If
End Sub

Y por mas que quiera..... dentro del rango A1:E10 me resulta imposible colocar un valor que no sea numérico:

Otra cuestión muy consultada es la siguiente: que pasa si el usuario elimina una columna o fila. Si tenemos bien identificada el área de trabajo (en este caso: A1:E10) esto no supondría un problema mayor. Para una mayor comodidad, seleccionamos el citado rango y le damos un nombre, por ejemplo Area1.

a la izquierda de la barra de fórmulas podremos nombrar el Rango A1:E10 como "Area1"

Luego insertamos el siguiente código:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'si el nombre de la hoja es Hoja2
If Sh.Name = "Hoja2" Then
    'si luego de algún cambio en la hoja detectamos que nuestro rango
    'tiene menos de 10 filas, utilizo el método Undo del objeto Application
    'para deshacer la última acción realizada
    If Range("Area1").Rows.Count < 10 Or Range("Area1").Columns.Count < 5 Then
        Application.Undo
    End If
End If
End Sub

Y nadie podrá eliminar filas ni columnas dentro del rango especificado.

Con el evento:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
End Sub
estatermos en condiciones de controlar las acciones cada vez que se selecciona un rango distinto. Vean que los argumentos son los mismos. Podría evitar que seleccionen el rango A1:A6 con el siguiente código:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Selection.Address = "$A$1:$A$6" Then
    'si el usuario seleccionó el rango a1:a1, quito dicha selección y
    'la envío a c5
    Range("c5").Select
End If
End Sub

Las posibilidades al combinar estos dos eventos son infinitas y nos dará la posibilidad de condicionar el ingreso/modificación de datos al máximo.