need basic code for a message

[comptechbranden]

I am entering code into a module in excel. What code can I use that will for instance, say in all of column A, if it reads "OVERDUE" in any box because of some IF logic that is already in place, then I want an error message pops up and says, "There is a project that is now overdue."

 

[Vbark]

Here try this, you can call it from 'Worksheet_Activate' if you want it to run on open....

Sub LoopThroughCells()
    Dim cl       As Excel.Range

For Each cl In Worksheets("Sheet1").Range("F1:G500").Cells
      If UCase(cl.Value) = "OVERDUE" Then
        MsgBox "There is a project that is now overdue."
        Exit For
        End If
    Next cl

End Sub

 

[comptechbranden]

Thanks for the code. It puts me in the right direction but the only way it works is when I run the macro manually. Any suggestions? Am I doing it right?

 

[Moniker]

So long as you put it in the Worksheet_Activate event, just remove the Sub stuff he suggested, like this:

Private Sub Worksheet_Activate()

    Dim cl As Excel.Range

    For Each cl In Worksheets("Sheet1").Range("F1:G500").Cells
        If UCase(cl.Value) = "OVERDUE" Then
            MsgBox "There is a project that is now overdue."
            Exit For
        End If
    Next cl

End Sub

If you have 50 OVERDUE values, though, you'll only know about the first one. If you want to know how many are overdue, change the code to this:

Private Sub Worksheet_Activate()

    Dim cl As Excel.Range
    Dim ctr As Integer

    For Each cl In Worksheets("Sheet1").Range("F1:G500").Cells
        If UCase(cl.Value) = "OVERDUE" Then
            ctr=ctr+1
        End If
    Next cl

    If ctr > 0 Then
        Msgbox "You have " & ctr & " project(s) that are overdue."
    End If

End Sub

 

[comptechbranden]

I use that those two last pieces of code seperately and none of them work for me. What could I be doing wrong? I am entering it into the VBA Code box, not the excel box...this is right...

 

[Vbark]

Are you in the vba editor?

From excel right click the tab where it says "Sheet1" or what ever it is named, and select 'View Code' then paste which ever code you want into this part, if you have a function called Worksheet_Activate then it will run when the spreadsheet is opened.

Private Sub Worksheet_Activate()

Call LoopThroughCells

End Sub

Sub LoopThroughCells()
    Dim cl       As Excel.Range

For Each cl In Worksheets("Sheet1").Range("F1:G500").Cells
      If UCase(cl.Value) = "OVERDUE" Then
        MsgBox "There is a project that is now overdue."
        Exit For
        End If
    Next cl

End Sub

 

[Vbark]

Actually if you want it on open its Workbook_Open you need to add it all to under (Thisworkbook)

 

[comptechbranden]

I did what was suggested to a "T". Adding it all under Workbook_Open() does not do anything when I open the worksheet. All the suggested similar scripts in this thread only work if I manually run the script as a macro using the green play button.

Are you getting it to work in your excel when you type in "OVERDUE" and re-open the file it shows the message? If so, email me the excel so I can learn what you did by looking at the code. My email is comptechbranden@gmail.com

Thank you so much for the help!

 

[Vbark]

Emailed.....

 

[comptechbranden]

The example showed me what I was doing wrong. I didnt realize workbook had its on code box there. Thanks a lot.