Solved: alert box on open

[samohtwerdna]

hello all!

I am trying to create an alert message that will pop-up when my worksheet is activated or opened with a list of all xrta late work.

I have a date processed column and a date signed column. when the "signed" column is filled in the job gets scheduled - but if the "signed" column does not get filled in - the job sits in lala land. I need to send an alert to the user with the jobs that are later than 2 months in the date processed column for the current date.

Any suggestions?

I am mostly stuck on the alert box when my wrksheet is opened.

Thanks...

[mvidas]

Hello, welcome back to vbax!

If you put the following code in your ThisWorkbook module of the workbook you want examined, it will run when the workbook is open. Currently this code checks every used cell from A2 and below, and if the date is 2+ months old then it puts column B's value in a msgbox at the end. Please let me know if you need any help customizing this![vba]Private Sub Workbook_Open()
Dim CLL As Range, AlertMsg As String
With Sheets("Jobs") 'sheet to look on
'change "A" in next line to date processed column
For Each CLL In .Range("A2", .Range("A65536").End(xlUp)).Cells
If DateAdd("m", 2, CLL.Value) <= Date Then 'if 2+ months old
'change CLL.Offset(0, 1).Value to the column you want shown to the user
AlertMsg = AlertMsg & IIf(AlertMsg = "", "", vbCrLf) & CLL.Offset(0, 1).Value
End If
Next
End With
If AlertMsg <> vbNullString Then MsgBox AlertMsg
End Sub[/vba]Matt

[samohtwerdna]

Matt,

Thanks for the help. The code works beautifully - but I would like to add one condition. I want to only include those that have not been "signed".

[VBA]Private Sub Workbook_Open()
Dim CLL As Range, AlertMsg As String, i As Variant, sR As String
sR = "G"
Range("H3").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
i = sR + ActiveCell.Count
With Sheets("Sheet1") 'sheet to look on
'change "A" in next line to date processed column
For Each CLL In .Range("i", .Range("G65536").End(xlUp)).Cells
If DateAdd("m", 2, CLL.Value) <= Date Then 'if 2+ months old
'change CLL.Offset(0, 1).Value to the column you want shown to the user
AlertMsg = AlertMsg & IIf(AlertMsg = "", "", vbCrLf) & CLL.Offset(0, -2).Value
End If
Next
End With
If AlertMsg <> vbNullString Then MsgBox AlertMsg
End Sub[/VBA]

I believe .Count is not what I want - but you can see that I'm trying to find the cell number of the first empty cell in the "signed" column. So that my range of cells to evaluate is only those jobs that have not been signed. I forgot to mention that I have a macro that sorts the worksheet when ever the "singed" column gets filled in so its nice and tidy.

[mvidas]

OK, I've changed this a little bit, should do what you need[vba]Private Sub Workbook_Open()
Dim CLL As Range, AlertMsg As String
Dim ProcCol As Range, SignCol As Range, JobCol As Range

Set ProcCol = Sheets("Sheet1").Columns("G") 'date processed column
Set SignCol = Sheets("Sheet1").Columns("H") 'date signed column
Set JobCol = Sheets("Sheet1").Columns("E") 'job column (to tell user)

For Each CLL In ProcCol.Parent.Range(ProcCol.Cells(2), ProcCol.Cells(65536).End(xlUp))
If DateAdd("m", 2, CLL.Value) <= Date And SignCol.Cells(CLL.Row).Value = "" Then
AlertMsg = AlertMsg & IIf(AlertMsg = "", "Jobs needing attention:", "") & vbCrLf & _
Intersect(CLL.EntireRow, JobCol)
End If
Next
If AlertMsg <> vbNullString Then MsgBox AlertMsg
End Sub[/vba]Let me know if its not!
Matt

[samohtwerdna]

Matt,

Sorry to be a bother, but the code has a type mismatch. I am using office 2000 maybe thats the prob - but

[VBA] Set ProcCol = Sheets("Sheet1").Columns("G") [/VBA]

doesn't seem to work?

[mvidas]

Hmmmmm...!!?
I'm using excel 2000 as well, and have no issues.. the ProcCol variable is a range, we're using the Set statement with it, the "Sheet1" has quotes, as does the "G" ... I don't know what the issue could be, do you have an open workbook?
You could try putting .Columns(7) instead of .Columns("G"), but that really shouldnt be a problem.. is there any way you can upload a sample file that its not working on?

[samohtwerdna]

here is an old test file I had for debugging before updating the actual file.

[mvidas]

OK, using that, I put the code into ThisWorkbook, changed 'Sheet1' to 'Short Order Schedule', and deleted the values of H5 and H6 on that sheet. I then changed "ProcCol.Cells(2)" to "ProcCol.Cells(3)" (since your values start on row 3 not 2). I closed the workbook, reopened it, and saw:

Jobs needing attention:
3265.103
3265.104

Could it be you had the mismatch problem due to the row 2/3 thing?

[samohtwerdna]

Yes that was it!

Sorry I missed that - Anyway thanks again for the help!! problem solved.

[Zack Barresse]

Hello, I would think it would be better to keep this routine in a Standard Module, then call this routine from the Workbook Open procedure. This gives you the distinct advantage of being able to call it anytime you want and being able to see it in your Macros Dialog box (Alt + F8).

[samohtwerdna]

Thanks Zach!

I did as suggested so you can call the routine anytime you want.

I may try teaking the MsgBox a little and have the items in the alert box selected on the sheet - but for now this is a good start.

Thanks again for all the help!!!

[mvidas]

Good point Zack!
Also, if you wanted to select those cells when the code is run, enter this in the ThisWorkbook and standard modules:[vba]'ThisWorkbook module
Private Sub Workbook_Open()
JobAlert
End Sub
'Standard module
Sub JobAlert()
Dim CLL As Range, AlertMsg As String, JobCells As Range
Dim ProcCol As Range, SignCol As Range, JobCol As Range

With Sheets("Short Order Schedule")
Set ProcCol = .Columns("G") 'date processed column
Set SignCol = .Columns("H") 'date signed column
Set JobCol = .Columns("E") 'job column (to tell user)
End With

For Each CLL In ProcCol.Parent.Range(ProcCol.Cells(3), ProcCol.Cells(65536).End(xlUp))
If DateAdd("m", 2, CLL.Value) <= Date And SignCol.Cells(CLL.Row).Value = "" Then
If JobCells Is Nothing Then
Set JobCells = JobCol.Cells(CLL.Row)
Else
Set JobCells = Union(JobCells, JobCol.Cells(CLL.Row))
End If
End If
Next
If Not JobCells Is Nothing Then
AlertMsg = "Jobs Needing Attention"
For Each CLL In JobCells.Cells
AlertMsg = AlertMsg & vbCrLf & CLL.Text
Next
JobCells.Parent.Select
JobCells.Select
MsgBox AlertMsg
End If
End Sub[/vba]Should do what you need!
Also, if this is solved, then you can denote it as such by going to Thread Tools at the top of the thread and choosing "Mark Thread Solved"

Let us know if you need anything else
Matt