excel pop up message

[chankish]

hello awesome people
i have an excel worksheet named Transit time who has integers as it counts days.
i want to have a pop up window in excel with a message like " you still have 7 days" or anything else if the transit time is less than 7 days per example , but i also want it to do two things :
- Color the cells who are less than 7 days
- Show the cells number in the message box


for now i have been able to set a small macro with "you still have 7 days" - changing the color (red per example )should be straightforward but i have something wrong in my writing ( BX is the column in excel)


please tell me what is wrong if possible .. and how to add the cells number ?!

Private Sub Workbook_Open()
    If BX <= 7 Then
        MsgBox "Adjustment is Below the Total"
        End If
For Each cell In BX
If cell.Value <= 7 Then
cell.Interior.ColorIndex = 3
cell.Font.ColorIndex = 2
cell.Font.Bold = True
End If
Next
End Sub

 

[Joe4]

i got type mismatch on this line : If cell <= 7 Then

That seems to imply that you have some non-numeric entries in column BX (other than row 1, which it is ignoring).

You can try changing it to:

[COLOR=#333333]If cell.Value <= 7 Then[/COLOR]

but I still suspect you may have some non-numeric data there.
Can you confirm that?

 

[Joe4]

If you are having trouble locating the error, this variation should tell you what line it is having issues with:

Private Sub Workbook_Open()

    Dim lr As Long
    Dim cell As Range
    Dim ct As Long
    Dim msg As String
    
    Application.ScreenUpdating = False
    
    Sheets("Data").Activate
    
    msg = "Adjustment is Below the Total in rows "
    
'   Find last row with data in column BX
    lr = Cells(Rows.Count, "BX").End(xlUp).Row
    
'   Loop through all rows
    On Error GoTo err_chk
    For Each cell In Range("BX2:BX" & lr)
        If cell.Value <= 7 Then
            cell.Interior.ColorIndex = 3
            cell.Font.ColorIndex = 2
            cell.Font.Bold = True
            ct = ct + 1
            msg = msg & cell.Row & ","
        End If
    Next cell
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    
'   Return message if found any cells
    If ct > 0 Then MsgBox msg


    Exit Sub
    
    
err_chk:
    MsgBox "Error in cell " & cell.Address(0, 0)
    Application.ScreenUpdating = True

End Sub

Three things to check with the cell it is having issues with:
- What is in that cell?
- Is it protected?
- Is it part of a merged cell?

 

[chankish]

you are right joe .. some numbers are showing #VALUE ! instead ..maybe it is an issue with a prior formula

 

[Joe4]

Yep, sounds like you may have an issue to correct.
If those values in column BX are being returned by formulas, you can update your formula to handle that, using an IFERROR formula.

 

[chankish]

the error is some of the cells referenced by the formulas are empty .. but the user used the formula on the rows ..
can we workaround this ?

 

[Joe4]

What do you want it to return in that cell in the event of an error?

If you wanted a zero, you would replace the current formula with this:
=IFERROR(current formula,0)

If you wanted a blank, it would look like this:
=IFERROR(current formula,"")

Anything else, just change the last argument of the formula above to whatever you want to return instead of the error.

Here is a good write-up on the IFERROR formula: https://exceljet.net/excel-functions/excel-iferror-function

 

[chankish]

i did not understand .. where should i add this exactly : =IFERROR(current formula,"")

i want the macro to ignore the error and continue working

 

[Joe4]

i want the macro to ignore the error and continue working

I am saying it is better to correct the formula returning the error. If IFERROR formula will return the result of your original formula, but if it encounters an error, it will return whatever you tell it to in that case.

For example, let's say that your current formula is something simple like:

=A1/B1

You would just change that formula to:

=IFERROR([COLOR=#0000ff]A1/B1[/COLOR],"")

So if it can evaluate A1/B1 without any errors, it will do that and return the value.
But if evaluating the formula would result in errors, it will return whatever you designate instead of the error (the empty string, in this case).

It is typically better to address errors at their source, than it is to try to ignore them down the road.
Otherwise, you would need to add another IF block in your VBA code and nest the other one under it. You can do that, but it could affect performance, as you are adding another check it has to do for every row of data you have, i.e.

Private Sub Workbook_Open()

    Dim lr As Long
    Dim cell As Range
    Dim ct As Long
    Dim msg As String
    
    Application.ScreenUpdating = False
    
    Sheets("Data").Activate
    
    msg = "Adjustment is Below the Total in rows "
    
'   Find last row with data in column BX
    lr = Cells(Rows.Count, "BX").End(xlUp).Row
    
'   Loop through all rows
    On Error GoTo err_chk
    For Each cell In Range("BX2:BX" & lr)
        If IsError(cell.Value) Then
        Else
            If cell.Value <= 7 Then
                cell.Interior.ColorIndex = 3
                cell.Font.ColorIndex = 2
                cell.Font.Bold = True
                ct = ct + 1
                msg = msg & cell.Row & ","
            End If
        End If
    Next cell
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    
'   Return message if found any cells
    If ct > 0 Then MsgBox msg

    Exit Sub
    
    
err_chk:
    MsgBox "Error in cell " & cell.Address(0, 0)
    Application.ScreenUpdating = True

End Sub

 

[chankish]

this code worked i think , but i am not able to run it from inside excel .. i think i am doing something wrong
1-go into developper tab -> visual basic
2-insert module
3-add the code
4-click on save
5-close the visual basic
6-click on macros in developer tab : it is empty ( even after restarting excel )
7-i wanted the macro to start each time i open the page

 

[Joe4]

The issue is here:

2-insert module

There are two kinds of Procedures in VBA:
1. Event Procedures, which run automatically in Excel upon some event happening (like the opening of a file, updating of a cell, etc).
2. All other procedures, which run when called by the user. Typically, these are placed in a Module which you insert yourself.

The "Workbook_Open" procedure is an Event Procedure. There are very specific requirements for Event Procedures:
1. They must be named a certain way. There is no leeway in how you name the procedure (you cannot change it).
2. They MUST be placed in the proper module. If placed in a General Module that you insert, they will NOT work.
"Workbook_Open" Event Procedures MUST be placed in the "ThisWorkbook" Module, which is automatically included in every workbook. That is where you need to place this code.

So if you move your code to the "ThisWorkbook" module, then as long as VBA/Macros are enabled, the code will run when the workbook is opened.