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

     '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

Acceso al VIDEO Nº 42. 

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]

Acceso al VIDEO Nº 41 desde aquí.

40 - Tarjetas animadas

 Esta época del año nos motiva a realizar otro tipo de trabajos en Excel, en este caso la creación de tarjetas festivas.

Los gifs animados son especiales para esto. Y para ello necesitaremos utilizar el control llamado Microsoft Web Browser.

NOTA: Si una vez dibujado el control en la hoja aparece un mensaje de que no este control no se encuentra habilitado, ver notas con aclaraciones en esta entrada o en el video 20 de mi canal.

El formato de la hoja (colores, fuente, tamaño de la tarjeta) queda a gusto de cada uno. 

En el video 40 cuyo enlace queda al pie se muestran 2 modelos: con o sin texto personalizado y con 1 sola hoja o como portada de un libro de más hojas.

Las macros que se necesitan son las siguientes:

En el objeto ThisWorkbook o Este Libro:

Private Sub Workbook_Open()

'seleccionar hoja de apertura y asignarle rango scroll

Sheets("Hoja1").Select          'esta instrucción solo si el libro contará con más de una hoja

ActiveSheet.[K8].Select

ActiveSheet.ScrollArea = "$A$1:$Q$40"

[F2] = ""   'quitar mensaje para habilitar macros

End Sub

En el objeto Hoja donde se colocará la tarjeta:

Private Sub Worksheet_Activate()

  'ubicar la imagen en la misma carpeta que el libro

  ruta = ThisWorkbook.Path           

  archivo = "navidad2021-c"          

  ext = ".gif"

  WebBrowser1.Navigate _

  "about:<html><body scroll='no'><img src='" & _

  ruta & "\" & archivo & ext & _

  " '></img></body></html>"

End Sub

NOTA: las variables que componen el nombre del objeto imagen se pueden incluir en una cadena que luego se reemplazará por la instrucción de color azul en la macro anterior. Por ejemplo:

ruta = ThisWorkbook.Path  

archivo = "navidad2021-c.gif"    

o también: 

ruta = ThisWorkBook.Path\navidad2021-c.gif

Eventualmente, si contamos con un libro con más de una hoja, podremos dibujar un botón para que nos ingrese al resto de la aplicación. Para ello insertaremos un módulo donde colocaremos la instrucción que seleccionará la hoja de trabajo que corresponda.

Sub INGRESO()

Sheets("PORTADA").Select

End Sub

Descargar los 2 archivos del ejemplo: Libro.xlsm e imagen.

Acceso al VIDEO Nº 40 desde aquí.

39 - RowSource o cómo actualizar desplegables desde otro formulario.

 Ya hemos visto en el VIDEO N° 9 cómo utilizar macros o procesos desde diferentes ámbitos o llamadas.

Ahora veremos cómo actualizar un control desplegable que tenemos en nuestro Userform abierto cuando la lista se actualiza desde otro formulario. Sin necesidad de cerrarlo y volverlo a abrir.

En el VIDEO Nº 39 dejo 3 ejemplos: 

Ejemplo 1- solicitando el nuevo elemento de la lista desde un InputBox.
Nuestro desplegable tendrá asignado el rango a mostrar, desde la propiedad RowSource (no desde el código).

Una vez ingresado el texto en el InputBox se procede a volcarlo a la lista en modo mayúscula.

Private Sub CommandButton1_Click()             'nueva caja

'solicita nuevo nombre (en mayúsculas)

caja = UCase(InputBox("Ingresar nuevo Tipo."))

'si el InputBox queda vacío

If caja = "" Then

    ComboBox2.SetFocus

Else

'agrega a la hoja Listas       'verificar q no se duplique (*)

    With Sheets("Listas")

        .Range("E" & .Range("E" & Rows.Count).End(xlUp).Row + 1) = caja

    End With                                    

'actualizar el control         'ordenar previamente (**)

    ComboBox2.RowSource = "=cajas"

    ComboBox2.Text = caja

    'opcional: pasarle el foco a algún otro control

    ComboBox4.SetFocus

End If

End Sub

NOTA: las macros para evitar duplicados y ordenar la lista se encuentran en el libro de descarga, en formularios del modelo N° 2.

Ejemplo 2: solicitando el nuevo nombre desde otro Userform con un control TextBox:

En este caso dejamos vacía la propiedad RowSource del control desplegable. Se lo alimentará con el método AddIem desde el evento Initialize del formulario, recorriendo la columna de datos que se encuentra en una hoja (Listas).

Private Sub UserForm_Initialize()

x = Sheets("Listas").Range("A" & Rows.Count).End(xlUp).Row

If x > 2 Then

    For i = 3 To x

        ComboBox1.AddItem Sheets("Listas").Range("A" & i)

    Next i

End If

End Sub

Private Sub CommandButton3_Click()       'nueva marca

'llamar al segundo formulario

UF_ProdMarcas.Show

'al regresar ya se habrá dejado en el control Combobox el texto ingresado

If ComboBox1 <> "" Then

    'guardarlo provisoriamente en una variable

    dato = ComboBox1.Text

    'ordenar la lista

    Call ordenaLISTAS

    'vaciar el combo para actualizarlo nuevamente con la lista actualizada

    ComboBox1.Clear

    For i = 3 To Sheets("Listas").Range("A" & Rows.Count).End(xlUp).Row

        ComboBox1.AddItem Sheets("Listas").Range("A" & i)

    Next i

    'volver a mostrar el dato guardado

    ComboBox1 = dato

End If

End Sub

En el segundo formulario, el botón GUARDAR tendrá el siguiente código:

Private Sub CommandButton2_Click()       

UF_Prod.ComboBox1 = TextBox1     'lo muestra en el control

Unload UF_ProdMarcas 

End Sub 

NOTA: el código completo, con el control para evitar duplicados y macro de ordenamiento se encuentra en el libro de descarga.

Ejemplo 3: llamando a un segundo formulario común a otros procesos.

En este tercer ejemplo, el desplegable se alimenta con la propiedad RowSource declarada en el evento Initialize del formulario principal.

Como en el segundo formulario es posible actualizar la lista con más de un elemento, al cerrarlo se deja vacío el desplegable para realizar la selección.

Private Sub UserForm_Initialize()

filx = Sheets("Listas").Range("C1").CurrentRegion.Rows.Count

ComboBox2.RowSource = "=Listas!C3:C" & filx

End Sub

 

Private Sub CommandButton4_Click()

'se selecciona la primer opción del 2do formulario, dándole el foco al control de texto

UF_VerificaListas.OptionButton1.Value = True

UF_VerificaListas.TextBox1.SetFocus

'se llama al 2do formulario

UF_VerificaListas.Show

'al regresar se actualiza el control combobox con el nuevo rango

ComboBox2.RowSource = "=Listas!C3:C" & Sheets("Listas").Range("C1").CurrentRegion.Rows.Count

           End Sub 

NOTA: el botón de guardado del segundo formulario debe realizar el control de duplicados. Es opcional ordenar la lista desde ese formulario o al regresar al formulario principal (códigos que se encuentran en formulario del modelo N° 2)

Descarga libro completo desde aquí.

Acceso al Video N° 39 desde aquí.

38 - Funciones de Conversión para controles TextBox.

Frecuentemente se encuentran consultas acerca de 'errores' al trabajar con controles del tipo TextBox. Errores que no son tales si recordamos que esos controles guardan su información en formato 'Texto'.

Así por ejemplo, en el VIDEO 38 se muestra la comparación entre 2 controles:

TextBox3 = "537"   TextBox5 = "1032"

Si TextBox3 < TexBox5 -------> pinte el control de rojo .... pero veremos que no se pinta. Porque entre textos el primero es mayor.

Por lo tanto, ya sea para realizar cálculos entre estos controles y datos de la hoja o para realizar comparaciones se necesitará convertirlos al formato número según se requiera.

1 - Función VAL: convierte un texto en un valor numérico:

      "537"  ------->  537

      "1432" ------>  1432

Pero si enviamos un valor numérico con decimales a un control TextBox, la función VAL truncará la parte decimal mostrando solamente la parte entera.

Por ejemplo: TextBox3 = 537,40      celda = 1430,50

Al realizar una suma:  suma = VAL(TextBox3) + celda = 537 + 1430,50 =  1957,50 (lo correcto: 1967,90)

2 - Función CDBL: convierte 'texto' con decimales a valor numérico con decimales.

Por ejemplo: TextBox3 = 537,40      TextBox5= 1430,50

Al realizar una suma:  suma = CDBL(TextBox3) + CDBL(TextBox5)

                                     suma = 537,40 + 1430,50 =  1967,90)

IMPORTANTE: La función CDBL dará error si se intenta convertir el contenido de un control vacío. Por lo tanto habrá que contemplar ese posible error, siendo los siguientes ejemplos algunos de los modos posibles.

Ejemplo 1: colocando una instrucción On Error antes de manipular ese control vacío:

'colorear según condición del resultado

On Error Resume Next

If CDbl(TextBox3) < TextBox5.Value Then

    TextBox5.BackColor = &HFF&                                        '< rojo

ElseIf CDbl(TextBox3) = Range("G" & filx).Value Then

    TextBox5.BackColor = &H80FF&                                   '=naranja

Else

    TextBox5.BackColor = &HFFFF&                                 ' > amarillo

End If

Ejemplo 2: evaluar si el control está vacío y en ese caso tomar otra decisión. En este ejemplo se coloca 1 en la celda, sino el valor convertido del control TextBox16:

If TextBox16 <> "" Then

    hox.Range("K" & fily) = CDbl(TextBox16)

Else

    hox.Range("K" & fily) = 1

End If

Acceso al VIDEO N° 38

37 - Protección en Excel: libros, hojas y estructura.

En el siguiente video se muestran las diferentes opciones de protección que nos ofrece Excel:  VIDEO 37

A continuación algunas instrucciones de protección:

1 - Apertura y guardado de Libros:

Sub abreProtegido()

'para abrir libro con contraseña de apertura

nbreLibro = ThisWorkbook.Path & "\Gestoria01.xlsm"

Application.Workbooks.Open nbreLibro, Password:="elsa"

'otro modo de escribir la misma instrucción

Application.Workbooks.Open nbreLibro, , , , "elsa"

End Sub

 

Sub abreEscritura()

'para abrir libro con contraseña de escritura

nbreLibro = ThisWorkbook.Path & "\Gestoria02.xlsm"

Application.Workbooks.Open nbreLibro, WriteResPassword:="yo", Password:="elsa"

'otro modo de escribir la misma instrucción

Application.Workbooks.Open nbreLibro, , , , "elsa", "yo"

End Sub

 

Sub abreSoloLectura()

'para abrir libro como solo lectura

nbreLibro = ThisWorkbook.Path & "\Gestoria03.xlsm"

Application.Workbooks.Open nbreLibro, ReadOnly:=True

'otro modo de escribir la misma instrucción

Application.Workbooks.Open nbreLibro, , True

End Sub

 

Sub guardaProtegido()

'para guardar libro con un nombre distinto al libro activo

nbreLibro = ThisWorkbook.Path & "\Gestoria03.xlsm"

ActiveWorkbook.SaveAs Filename:=nbreLibro, Password:="elsa"

'para guardar libro con el mismo nombre, pero con clave

ActiveWorkbook.SaveAs Password:="miclave"

End Sub

2 - Protección de Hojas:

Sub protegeHoja()

'para Desproteger una hoja con clave

Sheets(1).Unprotect "clave"

'para proteger con clave y algunos permisos

Sheets(1).Protect "clave", DrawingObjects:=True, Contents:=True, Scenarios:=True _

        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _

        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _

        AllowUsingPivotTables:=True

End Sub

3 - Protección de Estructura del libro. Esto permite inhabilitar la opción de crear, quitar o renombrar hojas de un libro.

Sub protegeLibro()

'desprotege la estructura del libro

ActiveWorkbook.Unprotect "tu"

'protege estructura con clave

 ActiveWorkbook.Protect "tu", Structure:=True, Windows:=False

End Sub

Acceso al VIDEO 37

36 - Filtros dinámicos con VBA, utilizando Userform

En el video anterior (N° 34) vimos cómo realizar una búsqueda dinámica directamente desde la hoja, utilizando Filtros Avanzados.

Hoy vamos a seguir con el tema, pero en esta ocasión utilizando un formulario o Userform.

En esta entrega encontrarán 2 ejemplos y en ambos el resultado de la búsqueda se muestra en un control ListBox.

Ejemplo 1: Desde un control TextBox se ingresará el texto a buscar en TODA la hoja, no importando en qué columna se encuentre. El resultado se irá mostrando en el control Lista.  La búsqueda se realiza de modo parcial, es decir que las celdas deben contener el texto buscado.  

Además este modelo presenta un control Combobox para realizar búsqueda por Nombre completo. A medida que se van registrando las letras se filtrará la lista.

En este formulario programamos 3 eventos:  Initialize (o sea al iniciar el formulario), TextBox_Change y ComboBox_Change en el caso de que además del control TextBox se cuenta con un desplegable también.

Ejemplo 2: En este modelo se utilizarán tantos controles TextBox como criterios tengamos para la búsqueda. Cada control corresponderá a una columna de la tabla. El resultado se irá mostrando en el control Listbox de modo filtrado. Los criterios se solicitan como: contenido en celdas de col A y C y coincidencia total con la columna B. 

Tanto en la apertura del formulario (evento Initialize) como desde el botón 'Limpiar' (CommandButton1_Click), se mostrará la lista completa en el control ListBox. 

El resto del código será para los 3 controles TextBox (evento Change) o la cantidad que tenga el formulario. Cada uno de estos eventos, luego de filtrar el rango correspondiente según su columna, llama a una macro común para el llenado del ListBox.

Los criterios para las columnas A y C se buscan de modo parcial, como parte del contenido de las celdas. En cambio para la columna B la coincidencia debe ser total.

Por ejemplo, para el primer TextBox:

Private Sub TextBox11_Change()
If TextBox11 <> "" Then

dato1 = "*" & TextBox11.Value & "*"         

Range("$A$3:$G$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=dato1, Operator:=xlAnd

Else

ActiveSheet.Range("$A$3:$G$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1

End If
Call llenalista
End Sub

Sub llenalista()
ListBox1.Clear
x = Range("A" & Rows.Count).End(xlUp).Row
If x < 4 Then Exit Sub
For Each cd In Range("A4:A" & x).SpecialCells(xlCellTypeVisible)

ListBox1.AddItem cd.Value

i = ListBox1.ListCount - 1

ListBox1.List(i, 1) = Range("B" & cd.Row)

ListBox1.List(i, 2) = Range("C" & cd.Row)

Next cd
End Sub

 

Acceso al VIDEO 36.

Desde el siguiente enlace se puede descargar el libro con la programación de los ejemplos, tanto del video 35 como del 36:

http://aplicaexcel.com/Blog/FiltroAvanzado.xlsm