jueves, 21 de noviembre de 2013

Cambios de Estado Masivos, con selección de rangos y botón derecho.

Muchas veces debemos de realizar en nuestras planillas distintos controles, como control de documentación, conciliación de pagos o proveedores en donde dados determinados filtros (fechas, vendedores, sucursales, etc) debemos cambiar el estado de alguna columna de dicha base en una o varias filas, según corresponda.
Para ello va este ejemplo donde dado la selección de un rango determinado, se aplicará un Estado a las celdas seleccionadas en una columna prefija particular (columna estado), se fijará la fecha del día en una columna fecha estado ( la fecha en que realizamos el cambio de estado) y nos aplicará un color a las celdas según la opción elegida, todo ello sólo con el Mouse, utilizando el botón derecho del mismo.
Distintos estados para las celdas según corresponda.
Paso 1
Definir en nuestra planilla que columna será la que contenga los estados. En nuestro ejemplo es la columna D.
Habilitar en alguna sección de la hoja, una casilla de verificación vinculada a una celda. Aquí es la casilla "Activa Selección" y está asociada a la celda "E2" que cuando la casilla esté activada tomará el valor "Verdadero" y "Falso" cuando la casilla esté desactivada.  Este valor se utilizará para activar o desactivar el botón derecho del mouse en modo "Opciónes de Estado" = activada, y "Menú Contextual" =desactivada.
La planilla puede tener filtros (se recomienda) y luego cuando seleccionemos no tendrá en cuenta los valores que quedan ocultos al filtrar. Esto es así ya que si filtramos por ejemplo un proveedor o una fecha queremos que los cambios a aplicar trabajen sólo en las celdas visibles al filtro y no en las otras.

Paso 2
Generamos automatización
En la pestaña de la hoja, hacemos un click con botón derecho del mouse y escogemos la opción de ver código.
Entramos a agregarle código a la hoja actual.
Aquí en "Objeto Worksheets" y "Procedimiento BeforeRightClick" tipeamos el siguiente código :
(Objeto y Procedimiento se escogen de las listas desplegables. Private Sub Wor.... y End Sub lo pondrá el editor al haber escogido las opciones)

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = Cells(2, 5)
If Cancel Then
Estados.Show
End If
End Sub

En  Cancel=cells(2,5) , debemos de hacer referencia a la celda que contiene el valor Verdadero o Falso del Paso 1. Aquí desabilitamos o habilitamos el menú contextual de Excel con el botón derecho.

Paso 3
Creamos un formulario que contenga las opciones a seleccionar.
Con botón derecho en el sector "Proyecto" sobre nuestro proyecto se nos habilita para crear el UserForm.
Creamos un UserForm (Formulario) para seleccionar opciones.

Predeterminadamente se llamará UserForm1 (ó 2... 3 ... n) . Nosotros en las propiedades en Name le podemos cambiar de nombre. En nuestro ejemplo "Estados"

Paso 4
Agregamos los controles a nuestro formulario .
Controles sugeridos para agregar a nuestro formulario.

En nuestro caso :
1 Etiqueta, para escribir la palabra fecha.
1 Cuadro de texto, para ingresar el valor de la fecha del cambio de estado.
3 Botones de opción ( o más según la cantidad de estados distintos que queramos indicar. También podríamos hacerlo con un cuadro de listas, pero en el ejemplo se optó por los botones)
1 Botón, para aceptar la selección.

Paso 5
Cuando ya tenemos el formulario creado, en el botón aceptar (haciendo doble clik) ingresamos el siguiente código :
Private Sub Aceptar_Click()
columna = 4
If Estados.Recibido Then
    nuevo_estado = "Recibido"
    Col = 43
    Else
    If Estados.Pendiente Then
    nuevo_estado = "Pendiente"
    Col = 41
    Else
    nuevo_estado = "Observado"
    Col = 45
    End If
End If
Fec = Estados.Fecha
fila1 = Selection.Cells(1, 1).Row ' calcula la primer fila del rango seleccionado
fila2 = Selection.Cells(Selection.Cells.Rows.Count, 1).Row ' calcula la última fila del rango seleccionado

For y = fila1 To fila2 Step 1
If Cells(y, columna).Rows.Hidden = False Then ' toma en cuenta sólo las filas visibles (no las filtradas)

Cells(y, columna) = nuevo_estado
Cells(y, columna + 1) = Estados.Fecha

With Cells(y, columna).Interior
        .ColorIndex = Col
        .Pattern = xlSolid
    End With
    Cells(y, columna).Font.Bold = True
End If

Next y
Estados.Hide
End Sub

También en el formulario ingresamos este código en el procedimiento Activate:
Private Sub UserForm_Activate()
Fecha = Format(Now(), "dd-mm-yyyy") ' En el cuadro de texto "Fecha" deja los valores de la fecha actual.
End Sub

FINAL :
Una vez creado el formulario, estamos en condiciones de que funcione nuestra planilla.
Seleccionamos el rango de filas a las que le queremos cambiar su estado y presionamos botón derecho ( La casilla de verificación debe de estar activada).
Nuestro formulario aparecerá en lugar del menú contextual habitual. Seleccionamos la opción y precionamos "Aceptar". Si no queremos realizar ningún cambio, presionamos la "x" del formulario, arriba a la derecha.
Veremos como las celdas seleccionadas toman el valor especificado y además son coloreadas según la opción. Estos colores se cambian en el código del botón, en la variable Col, actualmente están los colores 45, 43 y 41. Probar con distintos colores ó grabar una macro, escoger color de fondo deseado a la celda, detener la grabación y luego buscar en el código que número corresponde.
Al presionar botón derecho, aparecerá nuestro formulario. La opción deseada se aplicará al rango seleccionado.
Espero les sirva y le saquen provecho. Personalmente creo que es bueno tener en cuenta la posibilidad de utilizar el botón derecho para tareas personalizadas y por otro lado, el trabajo con rangos y su modificación en bloque.
Acá dejo el acceso al archivo de ejemplo : <<<<Click>>>>
Saludos y hasta la próxima.

No hay comentarios: