Reminders if a cell matches a date in Excel VBA [closed]

Question

Reminders if a cell matches a date in Excel VBA [closed]

Navigation

#1 by (1 votes)

1

I am trying to develop an alert that reminds people to send a weekly report when certain dates (which are selected in column BA) are matched with the current day.

The problem is that my message box is not showing, and even though the code seems to be very simple, I am not finding a solution to the problem. I changed the day on my computer to try and nothing happened.

The code is as follows:

Sub ReportReminder()

    If Date = Sheets(1).Cell("BA15").Value Then
        MsgBox ("Enviar reporte semanal")
    ElseIf Date = Sheets(1).Cell("BA21").Value Then
        MsgBox ("Enviar reporte semanal")
    ElseIf Date = Sheets(1).Cell("BA27").Value Then
        MsgBox ("Enviar reporte semanal")
    ElseIf Date = Sheets(1).Cell("BA33").Value Then
        MsgBox ("Enviar reporte semanal")
    End If

End Sub

Any other information you need, let me know.

vba macros excel

asked by Cristian Sánchez 14.03.2017 в 16:05

source

1 answer

Access external files in Angular 2 Problem with select to show records

score 1 · Answer 1

Do not indicate the version of Office / Excel you are using; however I put a solution for Excel 2010 or higher.

As someone asked in the comments, you are also not indicating how you fire the code you used; that's important, because if the method is simply defined in some VBA module, Excel will never execute the code: you have to associate it with some event (change) to make it work.

The following code does the checking you need when a change is made to the specified sheet; it is a variation of your own code, adding error validation and using an array with the values of the cells you need to check (this would allow adding more cells to the validation in a simple way):

Dim celdas(1 To 4) As Variant        'matriz o arreglo de celdas a comprobar
                                     '(podría incrementarse su tamaño para incluir otras)
Dim ws As Worksheet                  'hoja de trabajo donde se realizará la comprobación

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Salir

    ' Inicializar datos para comprobación
    Set ws = Worksheets(1)
    celdas(1) = ws.Range("BA15").Value
    celdas(2) = ws.Range("BA21").Value
    celdas(3) = ws.Range("BA27").Value
    celdas(4) = ws.Range("BA33").Value

    Dim fecha As Date
    fecha = Date

    ' Determinar si se muestra el mensaje de reporte, buscando en todas las celdas
    For x = 1 To UBound(celdas)
        If celdas(x) = fecha Then
            ' Mostrar mensaje y salir del ciclo
            MsgBox "Enviar reporte semanal."
            Exit For
        End If
    Next

    Exit Sub

Salir:
    ' Se generó un error, simplemente salir (aquí podría mostrarse un mensaje)
    Exit Sub
End Sub

The only problem with the code is that it will be running every time any is changed on the sheet (which is basically what you indicate in your question); if there is a lot of data in it and / or formulas, then this could generate a bottleneck; therefore you must be careful with that. An alternative would be to integrate a button for users to launch verification manually.

The code must be included in the Excel sheet where you will do the validation; if the sheet is not the first, then you must also change the line Set ws = Worksheets(1) to indicate the specific sheet where your information is.

The VBA window of your code should look similar to the image I put below:

I hope the solution will work for you. Greetings.