Alerta de aviso de vencimiento en Excel

EManuel Castillo

Entra al Editor, selecciona con doble clic el objeto ThisWorkbook y allí inicia una macro para el evento Open, para mi ejemplo lo puse en módulos.

Sub ALERTA_FECHA1()
If Hoja1.Range("AZ1").Value + 3 = Date Then 
MsgBox "Alerta", 64, ""
End If
End Sub

en az1 tengo la fecha 01/04/2017 +3 = 04/04/2017}, DATE es = ala fecha de hoy 04/04/2017

entonces el código se activa

en este otro tengo esta:

Sub ALERTA_FECHA()
If Not Hoja1.Range("AZ1").Value = Date Then '
MsgBox "Alerta Faltan " & Date - Hoja1.Range("AZ1").Value & " Para Vencer", 64, "" 'cambia DATE por la fecha limite
End If
End Sub

funciona así:

Si el valor de fecha de AZ1(01/04/2017) no es igual al DATE (fecha de hoy) entonces me sale los días que me faltan para llegar ala fecha limite

Cambia DATE por la fecha que requieras como limite o la celda donde lo pondrás

NO se te olvide valorar

Buenas tardes de antemano muchas gracias solo una pregunta lo intente pero me sale un error de compilación se esperaba end sub

Ponme tu ejemplo para verlo

¿Disculpe tendrá algún correo para enviárselo?

[email protected] enviamelo y lo veo :D

Le envíe el archivo muchas gracias de antemano. Actualmente le tengo puesto un aviso de verificar las fechas, pero no se si se podría que diga que producto esta por vencerse y cuantos días faltan para que llegue a su vencimiento

. 04.04.17 #Aviso de vencidos

Buenas tardes, Emanuel

Entiendo lo que solicitas y, desde luego, es absolutamente posible hacerlo.

Dicho esto, me gustaría compartirte que, desde mi experiencia laboral, un mensaje al abrir el archivo es poco útil porque, una vez que lo lees, debes cerrar el mensaje para poder seguir trabajando con el archivo que lo contiene.

Adicionalmente, si la cantidad de productos fuese grande, resulta difícil retener la información que diera ese cuadro de texto.

Te propongo una solución superadora que, eventualmente, puede mostrarte el mensaje al abrir el archivo pero tienes el registro de la situación en tu planilla.

Esta sólución constaría de tres partes:

1.- La fórmula que devuelve la situación de cada producto:

Puedes colocar la fecha de hoy en la celda -ejemplo en C7- por si algún día deseas un análisis con respecto a otra fecha (por ejemplo el lunes siguiente).

Para cada registro de productos tendrás, supongo, una celda donde está la fecha de vencimiento. Para el primer producto de tu base asumiré que esa fecha está en la celda D10

=SI(ESBLANCO(D10);"";SI(D10-$C$7>=30;"OK";SI(D10-$C$7>=15;"MENOS DE 30 DIAS";SI(D10-$C$7>=7;"MENOS DE 15 DIAS";SI(D10-$C$7>=1;"A PUNTO DE VENCER";"VENCIDO!")))))

[Considera si usas comas o punto y coma para separar argumentos de las funciones. Yo usé ";"]
Asígnale el formato deseado y, luego, copia esta celda y pégala en las restantes de esta misma columna. Aún allí donde aún no tengas fechas. La fórmula devolverá nada si no hay una fecha en la celda de la izquierda.

Esta otra fórmula, en cambio, avisa exactamente cuántos días faltan cuando fueran más de 30

=SI(ESBLANCO(D10);"";SI(D10-$C$7>=30;"OK";SI(D10-$C$7>=1;"Faltan "& D10-$C$7 & "dias para vencim.";"VENCIDO!")))

2.- El formato condicional de celdas

Ok, la fórmula devolverá el estado de cada producto en forma literal.

Para el tema de los colores, usaremos la función nativa de MS Excel Formato Condicional.

Selecciona la celda donde quieres que tome un determinado color, por ejemplo D12 o D12 y E12 para que ambas se coloreen. Luego presiona el botón Formato Condicional y elije Nueva Regla.

En la ventana que aparece marca la última opción: Utilice una fórmula que determine...

Luego aparece otra ventana donde le indicarás la condición:

Como notarás, esa fórmula que coloqué en la casilla evalúa que falte más de 30 días (o igual) para el vencimiento indicado en la celda (como fuera que se la hayas indicado: por fórmula o valor).

Luego con el botón Formato de ese cuadro, podrás indicarle el color y otros atributos de las celdas del día anterior.

Al dar Aceptar volverás a una pantalla como esta:

Allí podrás hacer un click en el botón Nueva regla para agregarle otra condición.

Por ejemplo, que se pinte de NARANJA cuando falten más de 15 pero menos de 30 días para el vencimiento.

De este modo:

Notarás que se agrega arriba de la regla anterior. Tendrás que moverla abajo con los botones que te marco en el óvalo. También es importante que marques Detener si es verdad para que no siga evaluando la condición siguiente.

Así puedes seguir agregando condiciones, si lo necesitaras.

Una vez que hayas terminado de configuración de esa celda, puedes copiarla y luego -seleccionando las celdas donde quieres que haga lo mismo- haces Pegado Especial - Formatos

(Puedes acceder a ese menú con Botón derecho del mouse)

De esta manera tendrás un registro claro de la situación de cada producto y podrás filtrar la base por aquellos que digan algo distinto de OK.

3.- Aviso al ingreso

De todos modos, como lo que solicitaste era una alerta al abrir el libro, te complemento con el siguiente código de VBA.

Para que funcione, activa el editor de Visual Basic (presiona Alt+F11) y la hoja que dice "ThisWorkbook" (o "EsteLibro" según la versión"), copia el código siguiente y pégalo en el panel desplegado a la derecha de su Editor de Visual Basic:

Private Sub Workbook_Open()
'---- Variables modificables ----
'=== EMANUEL, modifica estos datos de acuerdo a tu proyecto:
    IniColVENC = "D13" 'celda donde inician las fechas de vencimiento
    IniColSTAT = "E13" 'celda donde inician los estados de vencimiento de cada producto
    MaxLineas = 10000 'máxima cantidad aproximada de productos en tu base
    TextControl = "OK" ' texto de productos NO vencidos ni por vencer
    TextVENC = "VENCIDO!" ' texto para productos vencidos
'---- fin Variables
'
' VBA coding by FeJoAl
'
'---- inicio de rutina:
ContOK = Application.WorksheetFunction.CountIf(Range(Range(IniColSTAT), Range(IniColSTAT).Offset(MaxLineas)), TextControl)
ContVENC = Application.WorksheetFunction.CountIf(Range(Range(IniColSTAT), Range(IniColSTAT).Offset(MaxLineas)), TextVENC)
ContTOT = Application.WorksheetFunction.Count(Range(Range(IniColVENC), Range(IniColVENC).Offset(MaxLineas)))
Cont = ContTOT - ContOK
ElMensaje = IIf(Cont = 0, "NO hay productos a vencer o vencidos" & Chr(10) & " en la base de " & ContTOT & " productos", "Se detectaron: " & Cont - ContVENC & " producto" & IIf(Cont - ContVENC > 1, "s", "") & " por vencer" & Chr(10) & "y " & IIf(ContVENC = 0, "ningún producto", ContVENC & " producto/s")) & " vencidos"
TipoMens = IIf(Cont = 0, vbInformation, vbCritical)
ElTitulo = IIf(Cont = 0, "TODO BIEN!", "PRODUCTOS A REVISAR")
Application.ScreenUpdating = True
MsgBox ElMensaje, TipoMens, ElTitulo
End Sub

Al inicio del código hay unas variables que podrás modificar a tu parecer de acuerdo a tu planilla.

Espero haber sido suficientemente claro (aunque extenso), que te haya resuelto el problema -y, en tal caso, agradeceré que califiques mi contribución- o escribeme de nuevo aquí, si necesitas más apoyo con esto.

Disculpe las molestias primeramente le agradezco por la información proporcionada. Lo que pasa ingrese el código que usted me proporciono pero siempre me salen que 8 productos están por vencer. De echo modifique las fechas para verificar pero me siguen saliendo lo mismo.

.

Buenas, Emanuel

Verifica los resultados en tu planilla y revisa que las direcciones iniciales sean las correctas.

Básicamente, con los valores de los parámetros que te pasé, la rutina cuenta cuantos OK hay entre la celda E13 y la E10013 y compara con cuántas fechas hay entre D13 y D10013, Controla que sean esas las direcciones correctas en tu planilla. Pueden ser otras y deberías indicarle cuáles son las que realmente debe considerar. La diferencia entre el total de fechas y las que estén OK te dá cuántas están por vencer o vencidas.

Tal vez sea que abre en otra hoja, así que forzaremos que vaya a la hoja donde tienes esa base de datos. Utiliza esta variante indicándole el nombre de la hoja donde están los productos:

Private Sub Workbook_Open()
'---- Variables modificables ----
'=== EMANUEL, modifica estos datos de acuerdo a tu proyecto:
    HojaBase = "base de datos" 'hoja donde están los controles de vencimiento
    IniColVENC = "D13" 'celda donde inician las fechas de vencimiento
    IniColSTAT = "E13" 'celda donde inician los estados de vencimiento de cada producto
    MaxLineas = 10000 'máxima cantidad aproximada de productos en tu base
    TextControl = "OK" ' texto de productos NO vencidos ni por vencer
    TextVENC = "VENCIDO!" ' texto para productos vencidos
'---- fin Variables
'
' VBA coding by FeJoAl
'
'---- inicio de rutina:
'
Sheets(HojaBase).Activate
'
'conteos:
'
ContOK = Application.WorksheetFunction.CountIf(Range(Range(IniColSTAT), Range(IniColSTAT).Offset(MaxLineas)), TextControl)
ContVENC = Application.WorksheetFunction.CountIf(Range(Range(IniColSTAT), Range(IniColSTAT).Offset(MaxLineas)), TextVENC)
ContTOT = Application.WorksheetFunction.Count(Range(Range(IniColVENC), Range(IniColVENC).Offset(MaxLineas)))
Cont = ContTOT - ContOK
'
'Construcción del mensaje a dar:
ElMensaje = IIf(Cont = 0, "NO hay productos a vencer o vencidos" & Chr(10) & " en la base de " & ContTOT & " productos", "Se detectaron: " & Cont - ContVENC & " producto" & IIf(Cont - ContVENC > 1, "s", "") & " por vencer" & Chr(10) & "y " & IIf(ContVENC = 0, "ningún producto", ContVENC & " producto/s")) & " vencidos"
TipoMens = IIf(Cont = 0, vbInformation, vbCritical)
ElTitulo = IIf(Cont = 0, "TODO BIEN!", "PRODUCTOS A REVISAR")
Application.ScreenUpdating = True
MsgBox ElMensaje, TipoMens, ElTitulo
End Sub

Si aún así tienes problema en obtener los resultados correctos, vuelve a escribirme.

Abrazo

Fer

.

Lo verifique y esta bien como usted dice sumamente correcto. disculpe sera que se pueda agregar que producto es el que esta apunto de vencer? o en caso de que este vencido cual es ese producto vencido? ya que normalmente solo es un producto cada determinado tiempo que saldra como vencido o apunto de vencer

.

Pues precisamente la ventaja de tenerlos en una base de datos es que puedes filtrarla por aquellos casos que digan "VENCIDO!" o cuyo status sea distinto de OK.

Por otra parte, tendrás visible los datos de fechas y otros relativos al producto.

Me parece más práctico que tenerlo en un msgbox inicial que, adicionalmente, sólo se vé cuando abres el archivo, no mientras lo estás usando.

Por caso, si agregas un producto o cambias alguna fecha tendrás que abrir de nuevo el archivo para ver las novedades.

Saludos

Fer

.

claro muchas gracias