73 - TEXTBOX : Con formatos y control de contenidos.

Considerando que los controles TEXTBOX son controles de 'textos', para trabajarlos con valores numéricos tendremos que controlar su ingreso y además luego darle un formato apto para utilizarlos en cálculos numéricos.

Los principales eventos que utilizaremos en este primer ejemplo son: 

- KEYPRESS : nos permite controlar cada caracter (KeyAscii) introducido.  

- EXIT : nos permite asignar un formato al salir del control, y también controlar contenidos o aplicar  restricciones.

En el Userform1 tenemos 3 controles TextBox. 

                                  

En el primer control, TextBox1, no hay restricciones. Se reciben números, letras y caracteres alfanuméricos. Solo se indica que en caso de ser un contenido numérico, le aplique formato moneda corriente (ver NOTAS al pie).

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)  

'al salir se aplica formato

   If VBA.IsNumeric(TextBox1.Value) And TextBox1 <> "" Then

       TextBox1 = Format(TextBox1.Value, "$ #,###,##0.00")

   End If

End Sub

En el segundo control, TextBox2, solo se permiten números, coma decimal y signo -

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   If (KeyAscii < 48 And KeyAscii <> 44 And KeyAscii <> 45) Or KeyAscii > 57 Then

       KeyAscii = 0

       MsgBox "Solo ingresa números, signo menos y coma decimal"

   End If

End Sub

En este evento, el cursor permanecerá en el control TextBox2 hasta ingresar los valores correctos.

Y al salir del objeto, se aplicará formato moneda:

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    TextBox2 = Format(TextBox2.Value, "$ #,###,##0.00")

End Sub

En el tercer objeto, TextBox3, solo se permiten números. 

Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   If (KeyAscii < 48 Or KeyAscii > 57) Then

       KeyAscii = 0

       MsgBox "Solo ingresa números."

   End If

End Sub

La restricción que le aplicamos a este control, y que se evalúa en el evento EXIT, es decir al momento de salir de él, es que tenga un máximo de 6 caracteres. Y se le aplica un formato especial del tipo 000-000.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Len(TextBox3) > 6 Then

         MsgBox "Máximo 6 dígitos"

         Cancel = True

   Else

        TextBox3 = Format(TextBox3.Value, "000-000")

   End If

End Sub

NOTAS: 

La función LEN nos devuelve el total de caracteres que presenta el control TextBox.

La instrucción Cancel = True hará que el cursor permanezca en el control hasta cumplir con la condición de los 6 caracteres.  Esto no sucede si utilizamos el evento AfterUpdate.

El formato 'moneda' puede ser indicado también de este modo, para que se tome la moneda corriente del usuario.

    TextBox2 = Format(TextBox2.Value, "Currency")

Descargar ejemplo del Userform1 desde aquí o solicitarlo al correo:  cibersoft.arg@gmail.com

Acceso al VIDEO Nº 73 desde aquí.

72 - Modificar hipervínculos masivamente.

 Cuando tenemos en un libro Excel vínculos hacia otras hojas u otras referencias de fila/columna, nos encontramos con el problema de que al moverlos siguen conectados al origen. 

Así, por ejemplo, si tenemos una hoja como en la siguiente imagen, donde cada vínculo nos lleva a un cuadro dentro de la Hoja1, al copiar esa hoja y asignarle otro nombre el vínculo siempre nos dirige a la hoja de origen, o sea a la Hoja1.

Para resolver esta situación, utilizaremos una macro de pocas instrucciones, donde vamos a cambiar el argumento SubAddress, o sea la dirección del vínculo. 

Y además, en este ejemplo, modificaremos otros 2 argumentos: 

. ScreenTip (el texto que se muestra al pasar el mousse por encima del vínculo) y 

. TextToDisplay (el texto o valor que se muestra en la celda).

En un módulo copiaremos esta macro:

Sub ModificarHipervinculos()       

'modificar los hipervínculos dirigiéndolos a otra hoja

Dim anterior As String, nuevo As String, nvaDire As String, cadena As String

Dim x As Integer, y As Integer

Application.ScreenUpdating = False

'nombres de hojas

anterior = "Hoja1": nuevo = ActiveSheet.Name     

'limpia col auxiliar

Range("O:O").Clear                             

'recorre la col A que tiene los hipervínculos a modificar

y = Range("A" & Rows.Count).End(xlUp).Row    

For x = 4 To y

    'coloca en col auxiliar el hipervínculo

    Range("O" & x) = Range("A" & x).Hyperlinks(1).SubAddress        

    'reemplaza el texto anterior por el nuevo

    Range("O" & x).Replace What:=anterior, Replacement:=nuevo       

    'vuelve a crear el hipervínculo en la col donde se encontraba

    Range("A" & x).Select                                           

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _

    SubAddress:=nvaDire, _

    ScreenTip:=cadena, _

    TextToDisplay:=ActiveCell.Text

Next x

MsgBox "Fin del proceso."

End Sub

Luego se podrá ejecutar desde el menú Programador/Desarrollador estando en la hoja activa, es decir, donde se encuentra la lista de vínculos que deseamos actualizar.

NOTA: podemos incluir el valor de las variables directamente en la instrucción del Hyperlink, quedándonos así:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _

    SubAddress:=Range("O" & x), _

    ScreenTip:=ActiveSheet.Name & "-" & Range("A" & x), _

    TextToDisplay:=ActiveCell.Text

El libro de ejemplo se puede descargar desde este enlace o solicitarlo al correo: cibersoft_arg@gmail.com

Acceso al VIDEO Nº 72 con el paso a paso desde aquí.

71 - Copiar y Pegar con VBA

CTRL C y CTRL V deben ser los atajos de teclado utilizados con mayor frecuencia, en Excel.

Pero a la hora de necesitar pegados especiales, con VBA, no siempre es tan fácil conocer o recordar las instrucciones.

En el libro que se adjunta, encontrarán una guía completa con los resultados obtenidos en cada situación y según las instrucciones utilizadas.

Se programaron las siguientes situaciones:

- Copiado de Rangos Filtrados.

- Copiar un rango .de una Tabla en otro destino. 

- Copiar un rango de celdas (no Tabla) en otro destino.

- Copiar rangos de Tabla o de celdas según los siguientes criterios:

• Solo valores
• Valores y Formatos de números.
• Valores y Formatos de origen
• Solo fórmulas (sin formatos)
• Fórmulas y Formatos de números

- Copiado de Rangos Filtrados.

Tablas filtradas, columnas totales o parciales.

Rangos filtrados, columnas totales o parciales.

Rango pegado sin formatos, solo fórmulas.

Descargar libro guía desde aquí.

* Si la descarga no se puede realizar solicitar los libros de ejemplos al correo: 

cibersoft.arg@gmail.com

Acceso al VIDEO Nº 71 desde aquí.

69- Control WebBrowser para mostrar documentos o páginas Web

 El control WebBrowser nos permite mostrar en una hoja o en un formulario Userform, el contenido de documentos, páginas Web, como así también gifs o imágenes animadas.

En video N° 20 ya vimos cómo trabajar con Userforms que presentaban una tarjeta animada, con referencia a las fiestas de fin de año.

Y en video N° 40, vimos otros ejemplos donde colocamos tarjetas animadas ya sea en la apertura del libro o al activar una hoja de inicio.

En esta entrada veremos 2 usos más.

1 - Mostrar un documento (Pdf, Doc u otro tipo de archivo)

Este ejercicio continúa al tema anterior donde mostrábamos una lista de documentos según la carpeta y subcarpeta que elegimos desde un par de desplegables. 

Al seleccionar un elemento de la lista, se nos mostrará el contenido de ese PDF. Con todas las herramientas propias de Adobe que nos permite guardar, imprimir o cambiar de tamaño al PDF.

El libro, que se puede descargar desde el enlace al pie, presenta toda la programación ya vista en tema anterior. Aquí solamente agregaré las instrucciones correspondientes al evento Click del control ListBox.

Private Sub ListBox1_Click()

If ListBox1.ListIndex < 0 Then Exit Sub

Dim rutaPDF As String, archivo As String, ext As String

'la ruta del pdf es la del libro activo + carpeta+subcarpeta = Dire

    rutaPDF = Dire & "\"

'nombre del Pdf

    archivo = ListBox1.List(ListBox1.ListIndex, 0)

'extensión

    'ext = ".pdf"

WebBrowser1.Navigate rutaPDF & archivo    'mis archivos incluyen la extensión

End Sub

Previamente, habrá que agregar una instrucción al inicio del módulo Userform1, para declarar la variable Dire que es una variable compartida con varios de los procesos.

Dim Dire As String

2 - Mostrar una página Web dentro de un control WebBrowser.

En este ejercicio, al activar el Userform ya se mostrará el contenido de alguna página Web. De allí podremos tomar información, ya sea números, textos, completar tablas, etc.

Private Sub UserForm_Activate()    

'ruta a la pág de Wikipedia, Población mundial.

Dim rutaWeb As String

rutaWeb = "https://es.wikipedia.org/wiki/Poblaci%C3%B3n_mundial"

WebBrowser1.Navigate rutaWeb

End Sub

IMPORTANTE: si la página elegida presenta mucha publicidad nos aparecerán mensajes de aceptar o cancelar los Scrip, lo que puede hacer muy poco práctico el uso de este método para capturar información de allí

Descargar libro de ejemplo desde aquí.

Acceso al VIDEO N° 69 desde aquí.

68 - Listar Carpetas, Subcarpetas y Archivos.

 Cuando necesitamos trabajar con directorios que pueden ser ubicados (en paquete) en otra ubicación, nos será útil contar con un programita que utilice el objeto FileSystemObject. 

Veremos el uso del método GetFolder para obtener el contenido de una carpeta, las propiedades SubFolder y Files para hacer referencia a la colección de subcarpetas y archivos respectivamente.

En el ejemplo, contaré con un control desplegable (ComboBox1) para presentar las primeras carpetas a recorrer. Asumo que estarán en la misma ubicación del libro activo.

Luego tendremos un segundo control desplegable (ComboBox2)  que nos mostrará las subcarpetas de la carpeta seleccionada en el primer control.

Y así podemos seguir con otras ubicaciones hasta encontrar la carpeta que contenga los archivos que buscamos. En este caso, la selección del segundo control ya nos devolverá en una lista (ListBox1) el total de archivos allí encontrados.

A continuación solo resta desarrollar la acción que realizaremos sobre esa lista. Imprimir todos, seleccionar 2 o más archivos para eliminarlos, moverlos a otra ubicación, etc.

Los códigos desarrollados en el libro que se adjunta en esta entrada (ver descarga al pie) son los siguientes.

Dim ruta As String     'ruta de las carpetas Entradas y Salidas

Private Sub UserForm_Initialize()

ComboBox1.AddItem "ENTRADAS"

ComboBox1.AddItem "SALIDAS"

End Sub

 

Private Sub ComboBox1_Change()      'listar subcarpetas en el 2do combobox

Dim fs As Object, carpeta As Object, subcarpeta As Object

'la ruta de la carpeta principal es la del libro activo

ruta = ThisWorkbook.Path & "\" & ComboBox1.Text

'contempla posible error de ruta no hallada

On Error GoTo sinRuta

'se crea la referencia al objeto Filesystem

Set fs = CreateObject("Scripting.FileSystemObject")

Set carpeta = fs.GetFolder(ruta)

'se agregan las subcarpetas al 2do combobox

ComboBox2.Clear

    For Each subcarpeta In carpeta.SubFolders

        ComboBox2.AddItem subcarpeta.Name

    Next

Exit Sub

sinRuta:

    MsgBox "No se encontraron carpetas en la ruta indicada."

End Sub

 

Private Sub ComboBox2_Change()     'listar los archivos de la subcarpeta seleccionada

Dim Archi                   'guarda el nombre de cada archivo encontrado

Dim Dire  As String         'guarda el directorio a revisar

'la ruta de la subcarpeta

 Dire = ruta & "\" & ComboBox2.Text

'con el objeto Filesystem y con los objetos encontrados en la subcarpeta

On Error GoTo sinRuta

With CreateObject("Scripting.FileSystemObject")

    With .GetFolder(Dire)

    'se recorre el conjunto de archivos encontrados

    ListBox1.Clear

    For Each Archi In .Files

        ListBox1.AddItem Archi.Name

    Next

    End With

End With

Exit Sub

sinRuta:

    MsgBox "No se encontró la ruta de la subcarpeta."

End Sub

 

Private Sub ListBox1_Click()

If ListBox1.ListIndex < 0 Then Exit Sub

MsgBox "Has seleccionado el archivo " & ListBox1.List(ListBox1.ListIndex)

End Sub

 

Private Sub CommandButton1_Click()   'botón para limpiar controles y volver a empezar

ComboBox1.ListIndex = -1: ComboBox1.SetFocus

ListBox1.Clear: ComboBox2.Clear

End Sub

Descargar libro de ejemplo desde aquí.

Acceso al VIDEO N° 68 desde aquí.

67 - Uso de INPUTBOX para seleccionar un rango

 En la mayoría de las macros, trabajamos con un rango ya previamente seleccionado o  armamos la referencia al rango como una combinación de fila y columna. 

Por ejemplo: dire = Range(Cells(1,1), Cells(20,colx))

A continuación veremos el uso de la función INPUTBOX que nos permitirá seleccionar un rango para continuar con un proceso ya iniciado.

Para este ejemplo tendremos:

1 hoja principal y 1 hoja que se agrega para recibir la copia del rango seleccionado:

Se filtra la columna 2 (Fecha) de la tabla principal, por el año que se obtiene de la celda E9.

En la macro del ejemplo, que colocamos en el Editor de Macros, insertando un módulo, tenemos:

Sub procesoContinuo()

'hoja activa.

    Set hojaTabla = ActiveSheet

'crear una hoja nueva

    Set nvaHoja = Sheets.Add

'volver a la hoja principal

    hojaTabla.Activate

'en este ejemplo se filtra la tabla x algún criterio

    crit = "12/27/" & Year(Range("E9"))

    ActiveSheet.ListObjects("PaymentSchedule3").Range.AutoFilter Field:=2, _

        Operator:=xlFilterValues, Criteria2:=Array(0, crit)

'se selecciona un rango de la tabla filtrada

On Error Resume Next

Set rgox = Application.InputBox("Seleccione una celda o rango", Type:=8)

'si el rango no está vacío lo copiamos en la nueva hoja

If Not IsEmpty(rgox) Then

    rgox.Copy

    nvaHoja.Activate

'se pegan solo valores y formatos de número

    Range("B3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

        xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False

    MsgBox "El rango ya fue copiado. El proceso continúa....", , "Información"

End If

'otras instrucciones. Por ej:

'1- volver a hoja principal y quitar los filtros,

    hojaTabla.Activate

    ActiveSheet.ListObjects("PaymentSchedule3").Range.AutoFilter Field:=2

'2- renombrar la hoja creada,

 '----------

'3- dar formato al rango copiado, etc

 '--------

End Sub

 

NOTA: observar que se pueden seleccionar tanto rangos continuos como discontinuos, desde la ventana del InputBox.

Descargar libro de ejemplo desde aquí.

Acceso al VIDEO N° 67 con el desarrollo de 2 ejemplos: macro con selección de rango previo y macro con selección de rango durante un proceso.

66 - Nombrando hojas Excel desde VBA.

 Todos los usuarios de Excel seguramente renombran las pestañas que vienen de modo predeterminado al abrir un libro (Hoja1, Hoja2, etc) a su gusto y necesidad.  Así, encontramos nombres como 'Inicio', "Enero', 'Clientes', etc. Esos son los nombres de las 'pestañas'.

¿Pero cómo identificarlas correctamente desde algún código, macro o formulario?

Veremos a continuación las diferentes modos de llamarlas según la tarea que vayamos a realizar.

Modo N° 1:  Por su nombre de pestaña, entre comillas. Algunos ejemplos:

Sheets("PROVEEDORES").Select

Sheets("PROVEEDORES").Unprotect

Sheets("PROVEEDORES").Range(“A:B”).Copy

Selection.Copy Destination:= Sheets("PROVEEDORES").[C5]

Modo N° 2:  Por su código de nombre. Es el texto que antecede al nombre en la lista de hojas del libro. Así por ejemplo Hoja3 será la hoja Presupuesto.

Hoja3.Select

Hoja3.Unprotect

Hoja3.Range("A:B").Copy

Selection.Copy Destination:=Hoja3.[C5]

NOTA: Este método requiere que recordemos a qué hoja corresponde el código Hoja3. Lo que es una dificultad al copiar códigos con esta expresión, ya que podemos no tener los mismos textos.

Modo N° 3: Al inicio de un módulo, se declara una variable del tipo 'hoja' para guardar el nombre. En el resto del módulo se hará mención a la variable en lugar del nombre o código de nombre.

Dim hop As Worksheet      'tipo de variable hoja

Set hop = Sheets("PROVEEDORES")

    hop.Select

    hop.Unprotect

    hop.Range("A:B").Copy

    Selection.Copy Destination:=hop.[C5]

NOTA: Este método es muy práctico a la hora de trabajar con Userforms, donde asignamos la hoja a la variable desde el evento Initialize del Userform. Y en todos los procesos se hará mención a esa variable. Si por alguna razón, más adelante cambiamos de nombre a nuestra hoja, solo habrá que modificar esa línea inicial.

Modo N°4: Llamar a la hoja cuyo nombre se guardó en una variable del tipo String o texto.

Dim hojax As String      'tipo de variable texto

'nombre contenido de una celda de hoja LISTAS

hojax = Sheets("LISTAS").Range("B1")

'o también obtenido con un InputBox. Utilizar solo 1 de las 2 instrucciones

hojax = InputBox("Ingrese el nombre de la hoja a utilizar.")

    Sheets(hojax).Select

    Sheets(hojax).Unprotect

    Sheets(hojax).Range("A:B").Copy

    Selection.Copy Destination:=Sheets(hojax).[C5]

NOTA: Observar que en este caso, se llama a la hoja con Sheets(variable), sin uso de comillas.

Modo N° 5: Llamar a la hoja haciendo referencia a su índice o ubicación entre las pestañas.

Sheets(1).Select                        ‘primera pestaña

Sheets(Sheets.Count).Select    ‘última pestaña. 

               'Sheets.Count nos devolverá el total de hojas.

A continuación, algunas instrucciones para obtener información de la hoja activa:

nombre = ActiveSheet.Name

codi = ActiveSheet.CodeName

indi = ActiveSheet.Index

Acceder al VIDEO N° 66 desde aquí.