domingo, 19 de septiembre de 2021

48 - Eventos del Libro Excel

 Si bien en entradas anteriores (Eventos de Hoja Excel) ya vimos algunos ejemplos de macros colocadas a nivel de Libro, a continuación veremos los principales eventos para el objeto Libro, es decir aquellas macros que colocamos en el objeto ThisWorkBook.

OPEN: se ejecuta al abrir un libro. Aquí colocaremos las instrucciones que nos mostrará el libro con el formato deseado: qué barras quitamos, cómo ver la pantalla, qué hoja mostramos primero, qué hojas ocultamos, y así con el resto.

Ejemplo

Private Sub Workbook_Open()

Sheets("MENU").Select                       'hoja de inicio

[B3].Select

 

ActiveWindow.DisplayWorkbookTabs = False     'sin pestañas

Application.DisplayFormulaBar = False        'sin barra fórmula

Userform1.Show                                'mostrar un Userform

End Sub


ACTIVATE: se ejecuta al activar un libro. Al abrir el libro ya se lo está activando. Pero aquí colocaremos las instrucciones que necesitamos que se ejecuten al pasar de otro libro a éste que ya se encuentra abierto. Por ejemplo, mostrar una hoja que siempre está oculta y al entrar al libro la necesitamos hacer visible para ejecutar algún proceso.

Ejemplo: 

Private Sub Workbook_Activate()

Sheets("Resultados").Visible = True

End Sub


DEACTIVATE: se ejecuta cuando desactivamos el libro, ya sea para pasar a otro o para cerrarlo. Siguiendo el ejemplo anterior, aquí podríamos volver a ocultar la hoja que mostramos al activarlo.

Nota: las instrucciones se ejecutan estando ya en el nuevo libro activado. Por lo tanto hay que indicarle el nombre del que se acaba de desactivar para realizar alguna acción allí.

Ejemplo: 

Private Sub Workbook_Deactivate()

Workbooks("RESUMEN ANUAL.xlsm").Sheets("Resultados").Visible = xlVeryHidden

MsgBox "Acabas de salir del libro RESUMEN"

End Sub


BEFORE PRINT: se ejecuta al llamar a la opción de Impresión. En este evento se puede indicar cuál será la impresora a utilizar, la cantidad de copias. También se puede evitar la impresión de alguna hoja como en la siguiente macro:

Ejemplo: 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'seleccionar la impresora

 

'impedir que cierta hoja se imprima

If ActiveSheet.Name = "Resultados" Then Cancel = True

End Sub


BEFORE SAVE: se ejecuta al guardar el libro. Aquí podríamos indicar el guardado de alguna copia en otra subcarpeta. También es conveniente dejar el libro con las mismas medidas de seguridad que veremos en el evento siguiente.

Ejemplo

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'guardar además una copia

ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "/COPIAS/" & Format(Now, "hh-mm-ss") & "_" & ActiveWorkbook.Name

End Sub


BEFORE CLOSE: se ejecuta al cerrar el libro. Aquí podríamos dejar las mismas instrucciones que en el guardado si no desarrollamos el evento BeforeSave (hacer una copia en otra subcarpeta). 

Nota: Es conveniente dejar el libro con las mismas medidas de seguridad que establecemos en el evento Open. Es decir que si al abrir ocultamos ciertas hojas, debiéramos dejarlas también ocultas aquí en este evento como también en el de guardado (Save). Considerar que al abrir un libro bien podría abrirse sin habilitar las macros y por lo tanto no se ejecutarán las instrucciones de Open.... lo que dejaría visibles aquellas hojas a proteger.

Ejemplo

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ocultar algunas hojas

Sheets("Resultados").Visible = xlVeryHidden

End Sub


NEWSHEET: se ejecuta al insertar una nueva hoja. Ya sea que presionemos el botón + desde las pestañas como si ejecutemos una instrucción agregando una nueva hoja. Podemos indicarle por ejemplo la ubicación.

Ejemplo

Private Sub Workbook_NewSheet(ByVal Sh As Object)

ActiveSheet.Move Before:=Sheets("Enero")

End Sub


Los eventos iniciados con SHEET:  son las mismas macros que colocaríamos en los objetos HOJA. Pero que tienen un alcance a todas las hojas del libro, exceptuando las que no requieran de estos procesos. 

Ejemplo:

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

If ActiveSheet.Name <> "PORTADA" And ActiveSheet.Name <> "MENU" Then

    Call procesoHoja

End If

End Sub


Importante: si se programa un evento iniciado con Sheet en el objeto ThisWorkBook no debe ser colocado en el objeto HOJA si se trata del mismo proceso. 


Ver video N° 48

lunes, 16 de agosto de 2021

47- Eventos de Hoja (Parte 3)

 A continuación veremos otros Eventos de hoja Excel, siguiendo con el tema desarrollado en entradas 45 y 46.

4 - ACTIVATE:  acción que se ejecuta al ingresar o activar una hoja.

Ejemplo: al ingresar a una hoja se muestra un formulario o Userform:

Private Sub Worksheet_Activate()

'mostrar un formulario al entrar a la hoja

Userform1.Show

End Sub


5 - DEACTIVATE: acción que se ejecuta al salir o desactivar una hoja.

En el evento Deactivate, la hoja activa pasa a ser la de destino, por lo tanto en la macro habrá que indicar cuál es la hoja que se está desactivando.

Ejemplo: al salir de una hoja llamada JULIO se la oculta.

Private Sub Worksheet_Deactivate()

'al salir de la hoja se la oculta.

Sheets("JULIO").Visible = False

End Sub


Nota: Con Visible = False la hoja puede volverse visible desde el menú Formato, Mostrar Hojas. Para evitar esto se la debe ocultar con Visible = xlVeryHidden

6 - FOLLOW HYPERLINK: este evento se ejecuta cuando hacemos clic en algún hipervínculo.

Ejemplo: en la siguiente imagen tenemos una lista con nombres de hojas, con  hipervínculo.  Las hojas se encuentran ocultas y para el ingreso a ellas se solicita una clave.


Ese código se ejecutará antes de ingresar a la hoja. Para ello debemos extraer del hipervínculo la parte del texto que corresponde al nombre de la hoja, o sea del argumento 'subaddress'. Y lo haremos con la función INSTR.

Ejemplo de texto de un hipervínculo: 

nombre completo del libro - 

nombre de la hoja 

!    (signo de exclamación)

celda donde se debe posicionar


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If InStr(1, Target.SubAddress, "!") > 0 Then

    'obtener la cadena con el nombre del libro, sin el signo !

    hojax = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)

   'se solicita clave de ingreso

    cla = InputBox("Ingresa tu clave")

    'si la clave no es la correcta se deja la hoja oculta

    If cla <> "cliente" Then

        Sheets(hojax).Visible = xlVeryHidden

    Else

     'si la clave es correcta, se muestra la hoja y se la activa

        Sheets(hojax).Visible = True

        Sheets(hojax).Activate

    End If

End If

End Sub



7 - BEFORE DELETE: Este evento se ejecuta cuando intentamos eliminar una hoja.

Ejemplo 1: se guarda una copia del libro antes de eliminar cierta hoja.

Private Sub Worksheet_BeforeDelete()

'ruta del libro activo

ruta = ThisWorkbook.Path & "\"

'el nombre será el de la hoja, guión bajo, nombre del libro activo

nbre = ActiveSheet.Name & "_" & ActiveWorkbook.Name

'guardado de copia

ActiveWorkbook.SaveCopyAs ruta & nbre

End Sub 


Ejemplo 2: crear copia de la hoja y luego eliminar la original.

Private Sub Worksheet_BeforeDelete()

'pasos a realizar antes de eliminar una hoja (idea original encontrada en la web)


Application.ScreenUpdating = False

'1- guardar en una variable el nombre de la hoja activa (por ej: CAJA) y el nombre con el que se renombrará.

hojax = ActiveSheet.Name

hojay = ActiveSheet.Name


'2- renombrar la hoja original (por ej: CAJA_x)

ActiveSheet.Name = hojay


'3- hacer una copia de la hoja activa (quedaría CAJA_x (2))

ActiveSheet.Copy before:=Sheets(ActiveSheet.Name)


'4- la hoja de la copia queda como la activa. Renombrarla con el nombre original=CAJA)

ActiveSheet.Name = hojax


'5- se vuelve a activar la hoja que se quiere eliminar.

Sheets(hojay).Select


End Sub


Ver VIDEO N° 47.        

Descargar libro de ejemplo desde: http://aplicaexcel.com/Blog/Libro47.xlsm


.

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í.



lunes, 21 de junio de 2021

43-44 - Tu primer programa: Planilla de turnos

En videos 43 y 44 del canal vimos cómo armar una Planilla de Turnos de cualquier índole (turnos médicos, alquiler de oficinas, salones, alquiler de vajilla, etc).

En el primero de los videos vimos que la hoja de turnos presenta 60 tablas iguales (5 semanas a derecha y 12 meses hacia abajo). La tarea es completar la de la primera semana de Enero y luego copiarla hacia derecha y hacia abajo para completar el año.


Utilizamos nombres de rango para celdas que guardan la hora inicial y el intervalo de tiempo entre turnos. Esto nos permite modificar estos valores al cambiar de año sin necesidad de modificar la hoja de Turnos. 

También utilizamos celdas auxiliares para obtener los primeros días de cada mes según el año elegido, y qué día de semana inicia cada mes.

Las celdas auxiliares se guardan en una hoja aparte, en este caso llamada 'Logs' con esta ubicación de datos:


En el segundo video vimos las fórmulas utilizadas en fila 4 para mostrar cada día de la semana del mes elegido, el formato condicional para marcar con otro color los días no disponibles y las macros para cada grupo de botones. 

Como en este modelo de planilla los datos de los turnos se guardan en la misma hoja (no se pasan a una hoja base o listado) contamos con botones programados que nos permiten mostrar la semana del mes elegido y ocultar el resto de las columnas. 

Estos botones están agrupados por tipo (meses o semanas) y tienen seleccionada la propiedad: No mover ni cambiar tamaño con celdas. Esto permite que se mantengan estáticos al seleccionar otro mes y/u otra semana.

Nombres de rango y fórmulas en hoja auxiliar:

H1 = hora inicial con nombre de rango Turno1    
H2 = intervalo entre turnos con nombre de rango Turno2

Q2 = año vigente
R1:R12:  del 1 al 12 (número de mes)
S1 = fórmula para obtener el primer día del mes
         =FECHA($Q$2;R1;1)
T1 = fórmula para obtener el día de semana del primer día del mes
         =DIASEM(S1;2)

Fórmulas en hoja Turnos:

C5 (lunes):  

=SI(Logs!$T$1=7;Logs!$S$1+C$3;SI(Logs!$T$1=C$3;Logs!$S$1;""))

F5 (martes) que se arrastrará hasta R5 (sábado):

=SI(Logs!$T$1 = 7;Logs!$S$1+F$3;SI(Logs!$T$1=F$3;Logs!$S$1;SI(Logs!$T$1<F$3;C5+1;"")))

Al replicar las tablas hacia abajo habrá que reemplazar la referencia con $1 a la de los meses correspondientes, así para Febrero sería con el número 2:

C34 (lunes):  

=SI(Logs!$T$2=7;Logs!$S$2+C$3;SI(Logs!$T$2=C$3;Logs!$S$2;""))

F34 (martes) que se arrastrará hasta R34 (sábado):

=SI(Logs!$T$2 = 7;Logs!$S$2+F$3;SI(Logs!$T$2=F$3;Logs!$S$2;SI(Logs!$T$2<F$3;C34+1;"")))


Videos con el desarrollo completo: 

Video 43: https://youtu.be/rdWpnZeefxA

Video 44https://youtu.be/sQwk6PLVrQ0


sábado, 29 de mayo de 2021

42- Macros para manejar el Administrador de Nombres (colección NAMES)

En entrada anterior vimos el tema del Administrador de Nombres: dónde se ubica, cómo crear nombres de rango y asociarlos a celdas. Tema desarrollado desde la herramienta misma.

Hoy vamos a ver unas cuántas macros que nos permitan trabajar con esta herramienta a nivel programación (VBA).

Nota: La colección de 'Nombres de rango' que presenta el Administrador se denomina Names. A partir de la segunda macro veremos el uso y recorrido de esta colección.

1- Macro para asignar nombres de rango a celdas validadas.   (00:02:00

Sub AsignarNames()   'macro asigna indicadores a desplegable

    'nropro = sheets(....).[L4]    'guardar en variable el número de proyecto

'L4 de la hoja activa contiene el número de proyecto

indicaO = "indica" & Format([L4], "0000") & "_O"       'x ej: indica0025_O

indicaF = "indica" & Format([L4], "0000") & "_F"

indicaP = "indica" & Format([L4], "0000") & "_P"

 

On Error Resume Next

'en cada rango de celdas validadas se elimina cualquier validación anterior

'y se asigna una nueva lista con su fórmula correspondiente

With Range("E23:E27").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=" & indicaO

End With

With Range("E30:E33").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=" & indicaF

End With

With Range("E36:E40").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=" & indicaP

End With

End Sub

2- Macro para listar todos los elementos que se encuentran en el Administrador de Nombres: sus nombres y referencias.  (00:05:11)

Sub ListaNombres()     'listar todos los nombres de rango

    'se indica la hoja y primer fila para el listado

Sheets("LISTAS").[A1].ListNames

    'ListNames devuelve en 2 col el nombre y la referencia.

End Sub

3- Macro para listar Nombres de rango según un criterio establecido.  (00:06:34)

Sub listaNames()

    'listar en una col de cierta hoja todos los nombres de rango del libro.

Set hojax = Sheets("LISTAS")

x = 2

For Each Nb In ActiveWorkbook.Names

    If Left(Nb.Name, 7) = "indica0" Then

        hojax.Range("A" & x) = Nb.Name

        hojax.Range("B" & x) = Nb

        x = x + 1

    End If

Next Nb

MsgBox "Fin"

End Sub

4- Macros para eliminar todos los elementos del Administrador de Nombres.    

    a- Método 1: recorriendo cada elemento. (00:09:50) 

    b- Método 2: contando la cantidad de elementos.   (00:10:40) 

Sub quitaNbres()      'eliminar todos los nombres de rango (sin criterio)

'Método 1:

For Each Nb In ActiveWorkbook.Names

    Nb.Delete

Next Nb

MsgBox "Fin"

 

'Método 2:

canti = ActiveWorkbook.Names.Count   'contar la cantidad de nbres de rango

For i = 1 To canti

    ActiveWorkbook.Names(i).Delete

Next i

End Sub

    c- Contar cantidad de elementos en el Administrador de Nombres. Uso de NAMES.COUNT 

canti = ActiveWorkbook.Names.Count   'contar la cantidad de nbres de rango

5- Macro para eliminar elementos del Administrador de Nombres según un criterio.  (00:11:50)

Sub quitaNbres_Criterio()      'eliminar todos los nombres de rango (según criterio)

For Each elemento In ActiveWorkbook.Names

    'si el nombre comienza con el texto 'indica0' se elimina

    If Left(elemento.Name, 7) = "indica0" Then elemento.Delete

Next elemento

'opcional

MsgBox "Fin"

End Sub


Ver Video 42

domingo, 11 de abril de 2021

41- El Administrador de nombres - Uso de la función DESREF

Cuando utilizamos desplegables para mostrar listas de datos, nos podemos encontrar con ciertas dificultades en cuanto a la ubicación y actualización de esas listas.

Pero la herramienta Administrador de Nombres del menú Fórmulas nos hará la tarea mucho más sencilla.

Caso 1: Cuando mostramos una lista de datos fijos en varias hojas como así también en controles del tipo 'Combobox' dibujados en algún Userform.

Es el caso de los meses, días de semana o algún otro tipo de listas fijas.


Luego en los desplegables, desde el menú Datos  se vinculará la lista con este nombre:


Caso 2: Cuando, a diferencia del ejemplo anterior donde teníamos un rango fijo, ahora tendremos un rango dinámico.
Esto es cuando la cantidad de elementos de la lista puede ir cambiando a lo largo del tiempo. Para evitar tener que ir actualizando el rango del nombre creado, utilizaremos la función DESREF de este modo:


Los argumentos de la función DESREF son:
- Nombre de la hoja y primer celda del rango.
- 0 filas, 0 columnas: es decir que no la estamos desviando hacia otra dirección
- CONTARA(col del dato): esta función devolverá el total de celdas no vacías de esa columna. Se restará 1 porque la tabla tiene un título que no lo vamos a mostrar en el desplegable.
- 1 para indicar que el rango abarca 1 sola columna.
En las listas validadas o en desplegables del tipo Combobox se utilizará el nombre asignado, tal como se mostró en la 2da imagen.

Caso 3: Corresponde al caso en donde el rango a mostrar en celdas con validación de datos o, por ejemplo, en controles de un Userform, corresponde a una columna de una Tabla.
En la imagen tenemos una base de Clientes y la columna Cuenta será la que se mostrará en el desplegable de la hoja Facturación.

En este caso no será necesario el uso de la función DESREF ya que el rango tomará el tamaño total de la tabla.
Para ello seleccionaremos las celdas de la columna CUENTA (sin el título). A continuación llamaremos al Administrador de nombres, le daremos un nombre al nuevo rango y en el campo 'Se refiere a' indicaremos el nombre de la tabla y de la columna de este modo:
=Tabla2[CUENTA]


Descargar ejemplo desde aquí.

Ver Video 41 desde aquí.