Need some help with excel 2013 and due dates pop-ups on opening

[Sam_Manerker]

Hello everyone !

basically need some help figuring out why this is not working like it should. will try and give a brief overview first.
want to check if a company renewal date is due and alert accordingly on opening of workbook. have put down code that i'm using as under :

Sub Workbook_Open() '(company name-b2:b17 and date-u2:u17 (range working with right now))
Dim bottomD As Integer
bottomD = Range("u" & Rows.Count).End(xlUp).Row
Dim c As Range
For Each c In Range("u2:u" & bottomD) ' i think this is the problematic line of code. (??)
If c >= Date And c <= Date + 3 Then
MsgBox c.Offset(0, -19) & " is due in " & c - Date & " days." '
End If
Next c
End Sub


It seems to work only on a few rows but others are neglected. I cant figure out why that's happening....
I got this code off the net to be honest and tweaked it a bit ! am not an expert in this by any means..
Any help will be greatly appreciated !! Thanks in advance guys !

 

[Fluff]

If you step through the code using F8, when you get to

Dim c As Range

Hover your mouse over bottomD in the line above & what value does that give?

 

[Sam_Manerker]

hey Fluff !

thanks fr replying first of all !! tried following ur instructions, and stepping thru the code....it doesnt highlight the line i.e dim c as range at all ! it goes to the " for each c in range" line. (at that line bottomD value is shown as 16 on mouse over. Hope this makes sense... Thanks !

 

[Fluff]

Picked the wrong line of code!
If bottomD is 16 then your code will check from U2 to U16
If there are dates in that range that do not show the message box they may not be dates.
Change this line

If c[COLOR=#0000ff].Value[/COLOR] >= Date And c[COLOR=#0000ff].Value[/COLOR] <= Date + 3 Then

by adding the bits in blue & then try stepping through the code again & look at each value of c as you run it.
That may show where the problems are

 

[Sam_Manerker]

hey fluff !

thanks again fr ur quick reply ! i checked the file once again with regards to ur comment abt " values may not be dates" and it was prob that !! i think i copied values from cells above to take a shortcut of sorts (as i had copied all data into a temp file so as to not mess up the official one!) and it wasn't taking it as a date...(hope this makes sense) . Any which way, it seems to be working fine at the moment, when i manually entered all dates in that column again ! will have to fiddle around with it some more tomorrow and check out diff combinations to make sure its on point.
p.s This is without the c.value part u suggested in ur last post...

I wont close this thread if thats ok with u at this point in time...just want to be sure u know...Will check again tomorrow and confirm everythings working AOk. (its nighttime here in India, hence the tomorrow part!)

Also, it does not seem to be running the vb stuff on opening the workbook...have to manually run it...what could be wrong ??

Thanks so much for your time and patience Fluff ! really do appreciate this...

Regards,
Sam

 

[Fluff]

Also, it does not seem to be running the vb stuff on opening the workbook...have to manually run it...what could be wrong ??

Do you have the code in the ThisWorkbook module rather than a normal module?

 

[Sam_Manerker]

it shows up under "modules" folder and Module 1 (where actual code is) in project explorer view...i'm sorry , not too proficient with this... Also now, its not doing anything when i run it for some reason !! i dont know what to do !!

 

[Fluff]

The code needs to be moved to the ThisWorkbook module.
In The project window double click ThisWorkbook and place the code into the code window that opens up.
Not sure why it's no longer working, but one thing to try is in the intermediate window (which is below the code window, Ctrl+G will open it if needed) type application.EnableEvents=True and see if that helps

 

[Sam_Manerker]

Hey fluff,

Sorry fr the late reply. Good news !! it seems to be working now. Followed ur advice about placing the code in "ThisWorkbook" and it runs it now on opening the file. Also, the code seems to be running now with the trial data !! (dunno how or why, but it is working now !! lol). Thanks fr that info....

i have another query that you can maybe help with. What i would like to do (since actual file has lot more rows), is if the alert comes up, i would like the cell corresponding to the alert to be highlighted in red(for eg) . Is this possible ?? tried figuring out from net if i could find anything, but all tries failed. Cos it needs to run only when the condition is met (as in alert is displayed).

for eg. in my current file, due dates are under column u. so if possible i would like column v to have the highlighted cells in red if alert was displayed for that row.... hope this makes sense....this is just to make it more visible and keep track better if u know what i mean.... or even the due dates(with the dates) cell highlighted corresponding to the alert (that would also be fine!). Sorry, i'm stretching it a little bit, but i rather check if this can be done (with u) then to open a new thread and go through the whole process fr a small thing.

Please advise if u think thats possible, cos i guess u will have to call a separate function within the current code or something!?

Thanks in advance and sorry to keep bothering you.

Regards,
Sam

 

[Fluff]

Try this

Sub Workbook_Open() '(company name-b2:b17 and date-u2:u17 (range working with right now))

    Dim bottomD As Integer
    Dim c As Range
    Dim Cnt As Integer
    
    bottomD = Range("u" & Rows.Count).End(xlUp).Row
    For Each c In Range("u2:u" & bottomD)
        If c >= Date And c <= Date + 3 Then
            c.Offset(, 1).Interior.ColorIndex = 3
            Cnt = Cnt + 1
'            MsgBox c.Offset(0, -19) & " is due in " & c - Date & " days."
        End If
    Next c
    MsgBox "There are " & Cnt & " due"
    
    
End Sub