jueves, 11 de enero de 2018

VBA: Limitar elementos de un ComboBox a la lista cargada

Se trata hoy de limitar qué elementos podemos insertar en un ComboBox dentro del contexto de formulario de usuario, para evitar incluir elementos fuera de la lista permitida.

Para mostrar estas propiedades ejercitaremos un ejemplo de búsqueda de datos desde un Userform.
Tenemos un listado de datos en A1:D19, con campos ID, Fecha, Concepto e Importe.
A partir del ID en un ComboBox rellenaremos tres TextBox.
Lo importante es que queremos controlar que el ComboBox asociado al ID se rellena solo con los datos habilitados.


Nuestros datos en la hoja de cálculo:

VBA: Limitar elementos de un ComboBox a la lista cargada



Por otro lado insertaremos un UserForm en nuestro proyecto, con
1- cuatro etiquetas - label,
2- tres TextBox:
txtFecha
txtConcepto
txtImporte
3- un CommandButton
cmdSalir
4- un ComboBox
cmbID

VBA: Limitar elementos de un ComboBox a la lista cargada



Lo importante será las propiedades del ComboBox a tocar para conseguir nuestro objetivo:
.MatchRequired = True
.Style = 2-fmStyleDropDownList
Modificando estas dos propiedades conjuntamente (recomendado) conseguimos la meta, y nos será imposible introducir un elemento fuera de los listados...

Estas propiedades del Combobox las podemos ajustar desde la ventana de propiedades del Control, o bien desde la programación, como se puede ver a continuación

En la ventana de código del UserForm incluimos:

Private Sub UserForm_Initialize()
'ajustamos propiedades para evitar introducir elementos fuera de la lista mostrada
Me.cmbID.MatchRequired = True
Me.cmbID.Style = fmStyleDropDownList
'cargamos los elementos del rango A2:A19
Me.cmbID.RowSource = "A2:A19"
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmbID_Change()
'limpia los campos informados
Me.txtFecha.Value = ""
Me.txtConcepto.Value = ""
Me.txtImporte.Value = ""

'gestiona la búsqueda del ID deseado
Dim fila As Long
fila = Range("A2:A19").Find(what:=Me.cmbID.Value).Row

'carga los TextBox con valores de la lista...
Me.txtFecha.Value = CDate(Cells(fila, "B").Value)
Me.txtConcepto.Value = CStr(Cells(fila, "C").Value)
Me.txtImporte.Value = Format(CDbl(Cells(fila, "D").Value), "#,##0")

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmdSalir_Click()
'descarga y cierra el UserForm
Unload Me
End Sub



El resultado es el esperado... se nos hace imposible meter valores en el ComboBox distintos de los listados...

martes, 9 de enero de 2018

Replicar información de filas en columnas

Hoy haremos uso de las funciones matriciales para cruzar información insertada en filas en las columnas correspondientes, esto es, replicar los datos de filas en columnas.
Veamos en la imagen siguiente la idea:

Replicar información de filas en columnas



Se observa que al introducir un dato en C5, la formulación devuelve el dato en su correspondiente cruce, en la celda F4.
El tema de esta formulación es cruzar compras-ventas entre empresas, y que obviamente, una compra de A a B representa en el mismo instante una venta de B hacia A...


Nuestra fórmula matricial insertada en D5 será:
=INDICE(SI($C$3:$J$3="compra";DESREF($B$3;COINCIDIR(C$2;$B$4:$B$7;0);1;1;8));2*FILAS($B$4:$B5)-1)
recuerda validarla presionando Ctrl+Mayusc+Enter, en vez de solo Enter.

Esta formula la copiaremos al resto de celdas de las columnas que responde al concepto 'venta'.


Expliquemos algo más de esta fórmula.



Comprobamos el hecho que:
La empresa A ha comprado a B por 500
La empresa C ha comprado a B por 600
La empresa D ha comprado a B por 100
lo que provoca inmediatamente y en sentido contrario que:
La empresa B ha vendido a A por 500
La empresa B ha vendido a C por 600
La empresa B ha vendido a D por 100.

En nuestra fórmula empleamos la función DESREF que nos habilita un rango virtual que corresponde a la fila de valores correspondiente a la empresa que vende.
Por ejemplo, en la celda F4
DESREF($B$3;COINCIDIR(E$2;$B$4:$B$7;0);1;1;8)
se convierte en
{500\0\0\0\600\0\100\0}
al aplicar sobre esta matriz un condicional (no sería estrictamente necesario) para quedarnos con los valores de 'compra' tendríamos la siguiente matriz de valores:
{500\FALSO\0\FALSO\600\FALSO\100\FALSO}

Como solo queremos recuperar los valores que responden a las posiciones impares, para recuperarlos con la función INDICE añadiremos como argumento la serie de los impares:
2n-1
o en nuestro caso:
2*FILAS($B$4:$B5)-1


Como se comentaba más arriba, se podría optar por esta forma igualmente válida y algo más sencilla en la celda D5:
=INDICE(DESREF($B$3;COINCIDIR(C$2;$B$4:$B$7;0);1;1;8);2*FILAS($B$4:$B5)-1)
matricialmente ejecutada.

Pero he preferido quedarme con la fórmula descrita por ser más restrictiva y quedarnos únicamente con los valores que respondían a las compras...

jueves, 4 de enero de 2018

Marcar coincidencias con formato condicional

Resolveremos hoy un problema de búsqueda de coincidencias múltiples empleando el formato condicional.
La meta del problema de hoy es marcar con algún formato los nombres coincidentes de un listado con una segunda tabla:

Marcar coincidencias con formato condicional



Como se ve en la imagen, en nuestro listado situado en A2:B23, se marcan con un formato establecido aquellos nombres que aparecen en la tabla situada en G1:G3.
Para facilitar el trabajo, la tabla de G1:G3 la hemos renombrado como 'TblNombres', a la cual, ademas le hemos asignado un nombre definido, que responde a:
nombres =TblNombres[Nombres]


Llega el momento de configurar nuestro formato condicional.
Desde la ficha Inicio > grupo Estilos > botón Formato condicional > Nueva regla > Utilice una fórmula que determine las celdas para aplicar un formato, asegurándonos que la celda activa es A2, insertaremos la siguiente fórmula:
=NO(ESERROR(BUSCARV($A2;nombres;1;0)))

y a continuación le daremos algún formato...

Marcar coincidencias con formato condicional



Conseguimos lo esperado... tenemos marcado con algún formato todas aquellas coincidencias con nuestro listado de nombres buscados...

Algo similar aplicando una macro, donde usamos el método .Find sería:

Sub Coincidencia()
'recorremos cada nombre buscado en la tabla
For Each nombre In Range("nombres")
    'buscamos coincidencias en el rango A2:A23
    With Range("A2:A23")
    'localizamos el nombre buscado
    'exigimos la coincidencia en toda la celda...
    Set c = .Find(nombre.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            'si ha habido coincidencia
            'cambiamos el color de las celdas
            Range(c, c.Offset(0, 1)).Interior.Color = vbYellow
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
Next nombre
End Sub