sábado, 17 de julio de 2021

46 - Eventos de Hoja (Parte 2)

EVENTOS: SELECTIONCHANGE y CHANGE

2 - Evento SelectionChange: Este evento se ejecuta al seleccionar una celda o rango de celdas. 

Como permanentemente seleccionamos celdas dentro de una hoja (al revisar contenido, o ingresar datos, etc) se deberá evaluar correctamente en qué celdas o rangos debe ser ejecutado este evento. Por eso este evento generalmente se utiliza al seleccionar celdas de títulos. 

Ejemplo 1:  pasar a la hoja del mes seleccionado.


 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'col donde se puede seleccionar para ejecutar macro de avance

If Target.Column >= 7 And Target.Column <= 10 Then

    'se controla si no es una celda numérica

    If Not IsNumeric(Target.Value) Then

        'se controla posible error en el nombre de hoja 

        On Error Resume Next

        'se selecciona hoja cuyo nombre coincida con el texto del título. Previamente se la visibiliza

        With Sheets(Target.Text)

            .Visible = True

            .Select

        End With

    End If

End If

End Sub


Ejemplo 2: al seleccionar alguna celda de título se ordena la tabla por esa columna.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'se ejecuta al seleccionar alguna celda en columnas 8 a 11 de la fila 4

If Target.Column >= 8 And Target.Column <= 11 And Target.Row = 4 Then

    On Error Resume Next

    'si la celda no está vacía se ordena la col de la celda seleccionada

    If Target.Value <> "" Then

       Call ordenaTabla(Target.Column)

    End If

End If

End Sub

Y en un módulo se colocará la siguiente macro de ordenamiento. Como en la macro de llamada se asigna la columna que debe ser ordenada, aquí también debe colocarse ese argumento.

Sub ordenaTabla(nrocol)

'variables: última fila del rango, la col a filtrar y el rango total de datos

finx = Cells(4, nrocol).CurrentRegion.Rows.Count + 2

rgoFilt = Range(Cells(4, nrocol), Cells(finx, nrocol)).Address

rgoTot = Range("H4:K" & finx).Address

'ordenamiento

    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range(rgoFilt), _

        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.ActiveSheet.Sort

        .SetRange Range(rgoTot)

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End Sub

3 - Evento: Change

A diferencia del evento anterior donde se ejecuta un proceso al seleccionar una celda o rango, aquí con el evento Change se ejecutará al modificar la celda o rango.

Ejemplo 1: al modificar las celdas de la última columna de una tabla, se completan los campos de las 2 primeras columnas de ese registro. Al mismo tiempo también se ordena la tabla por columna de Concepto (col D)


Private Sub Worksheet_Change(ByVal Target As Range)

'se ejecuta en la tabla (col B:F)

If Target.Column = 6 And Target.Row > 2 Then

    If Target.Count = 1 Then

        'se coloca la fecha del día en col B y el nro correlativo en col C

        Range("B" & Target.Row) = Date     

        Range("C" & Target.Row) = Application.WorksheetFunction.Max(Range("Analisis346[[#All],[ID]]")) + 1

        'se ordena la tabla

        Call macroOrdena

        'se posiciona en la fila siguiente, col D

        Range("D" & Target.Row + 1).Select

    End If

End If

End Sub



Ejemplo 2:  En otra tabla, al modificar las celdas de la primera columna (J) se coloca una fórmula en la última (O).

NOTA: como solo se puede tener un evento de cada tipo en la misma hoja, al tener dos procesos se deberá establecer correctamente el rango de acción de cada uno de ellos. Así nos quedará entonces el código para este evento en la hoja donde se encuentren las 2 tablas.

Private Sub Worksheet_Change(ByVal Target As Range)

'se ejecuta en la tabla (col B:F)

If Target.Column = 6 And Target.Row > 2 Then

    If Target.Count = 1 Then

        Range("B" & Target.Row) = Date

        Range("C" & Target.Row) = Application.WorksheetFunction.Max(Range("Analisis346[[#All],[ID]]")) + 1

        'Call macroOrdena

        Range("D" & Target.Row + 1).Select

    End If

Else

    'se ejecuta en el rango J:O

    If Target.Column = 9 And Target.Row > 2 Then

        If Target.Count = 1 Then

            Range("O" & Target.Row).FormulaR1C1 = "=IF(RC[-5]="""","""",MONTH(RC[-5]))"

        End If

    End If

End If

End Sub



Descargar libro de ejemplo desde aquí.

Ver Video N° 46 aquí.

martes, 6 de julio de 2021

45 - Eventos de Hoja (Primera parte)

1 - EVENTO:  BEFOREDOUBLECLICK

Si bien algo ya se comentó en la planilla de Turnos (videos 43 y 44) acerca del uso de un evento de hoja vamos a desarrollar a lo largo de un par de entradas los principales Eventos de Hoja y de Libro.

En esta entrada veremos algunos ejemplos del evento: BeforeDoubleClick

NOTA: El evento BeforeRightClick cumple la misma función y se programa del mismo modo. Pero como el clic derecho se utiliza en la hoja para otras funciones es más recomendable el 'doble clic'.

IMPORTANTE: Las macros de los Eventos de Hojas se colocan en cada objeto HOJA donde se vaya a trabajar.


Ejemplo 1:
  Doble clic para ejecutar macro que inserta filas a medida que se rellena cada tabla.de Conceptos. Se evalúa si el Target (celda donde se hizo el doble clic) se encuentra en col 1 y si se trata de una celda combinada. En ese caso se avanza con el proceso de insertar la fila.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'control de ubicación
If Target.Column <> 1 Then Exit Sub
If Target.MergeCells = False Then Exit Sub

'control de filas aún disponibles
X = Target.Row - 1
If Range("D" & X) = "" And Range("E" & X) = "" Then
    MsgBox "Debes completar las filas vacías antes de habilitar nuevas.", , "Atención"
    Range("D" & X).Select
    Exit Sub
End If
'se habilitan nuevas filas
Call nuevo_Ingreso(X)
    'MsgBox "Se agrega fila " & X + 1
Target.Offset(0, 1).Select
End Sub

Ejemplo 2: Doble clic para filtrar una tabla por el valor de la celda seleccionada. Esta opción permite ir haciendo filtrado por diferentes columnas. 

En la imagen se observan 2 tipos de tablas. En la macro se evalúa si el doble clic se realizó dentro de las primeras 5 col de la hoja. En ese caso se trata de un objeto Tabla y la instrucción de filtro varía con respecto a la del rango común. 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error Resume Next

'se evalúa que la celda tenga un valor para filtrar

If Target.Value <> "" Then

    'se evalúa si Target se encuentra entre las primeras 5 columnas

    If Target.Column <= 5 Then

        'se resta 1 columna porque la tabla se inicia en B

        colx = Target.Column - 1

        'se filtra por el valor del Target

        ActiveSheet.ListObjects("Analisis34").Range.AutoFilter Field:=colx, Criteria1:=Target.Value

    Else

        ' se trata de filtrar un rango que se inicia en col H (col 8)

        colx = Target.Column - 7

        'se establece el fin de rango

        fini = Range("H" & Rows.Count).End(xlUp).Row

        'se filtra el rango por el valor del Target

        ActiveSheet.Range("$H$4:$K$" & fini).AutoFilter Field:=colx, Criteria1:=Target.Value

    End If

End If

End Sub

Como se trata de un libro donde habrá 12 hojas mensuales con el mismo diseño de tabla, habrá que colocar el código en el evento BeforeDoubleClick de cada hoja, ajustando el nombre de la Tabla en cada una de las macros.

NOTA: en casos como éste conviene entonces colocar la macro solamente en el objeto LIBRO (ThisWorkBook) y quitarlas de las hojas.

Aquí hay que tener el cuidado de no nombrar las hojas por su nombre sino por su valor de índice.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'se exceptúan las hojas donde no se encuentren estas tablas
If ActiveSheet.Name <> "CAJA-1" Then
    On Error Resume Next
    If Target.Value <> "" Then
        If Target.Column <= 5 Then
            colx = Target.Column - 1
            ActiveSheet.ListObjects(1).Range.AutoFilter Field:=colx, Criteria1:=Target.Value
        Else
            colx = Target.Column - 7
            fini = Range("H" & Rows.Count).End(xlUp).Row
            ActiveSheet.Range("$H$4:$K$" & fini).AutoFilter Field:=colx, Criteria1:=Target.Value
        End If
    End If
End If
End Sub


Descargar libro de ejemplo desde aquí.


Ver Video N° 45 aquí.