try this macro
Sub t()
Dim i, j, k As Long
For ww = 1 To Worksheets.Count
Sheets(ww).Activate
For j = 2 To Sheets(ww).Range("a1").End(xlDown).Row
If Sheets(ww).Cells(j, 4).Value - DateSerial(Year(Now), Month(Now),
Day(Now)) >= 1 Then
MsgBox Sheets(ww).Cells(j, 1).Value & " Will Expire in " &
Abs(DateSerial(Year(Now), Month(Now), Day(Now)) - Sheets(ww).Cells(j,
4).Value) & "days"
End If
Next j
Next ww
End Sub
On Fri, Apr 29, 2011 at 9:04 AM, syed aliya raza hashim <hashim...@gmail.com
> wrote:
> Hi Andred,
>
> see for example in sheet1 UPS 28-Apr-12
>
> Battery 28-Jul-11
> SMPS 2-Jan-11
>
>
> then i want the meassage box should display message when we open the file
> --like "ur UPS product will expire in 1 year 0r 365 days" because it will
> calculate from today date so today is 28 april-2011 but for UPS date entry
> is 28-Apr-2012 so that means it will expire in 365 days
>
> same for Battery "ur Battery product will expire after 3 month" because
> today date is 28 april 2011 and battery entry date is 28 jul-2011 so that
> means it will expire after 3 month
>
> but for SMPS product no need to display because the date mention for SMS is
> 02-jan-2011 which is already expired on
>
>
> so if the above condition is there for all
> product in all sheet then i want to display the message box for all sheet if
> remaining date is there to expire from current date
>
>
>
>
>
> 2011/4/28 André D'Avila <C>
>
> Hello Syed,
>>
>> Let me understand...
>>
>> You have in Sheet1 - UPS, Battery and SMPS
>> Sheet2 - Phone, Data Card, Lenser
>> Sheet3 - Keyboard, Mouse and Monitor
>>
>> You want when the file open, a message like:
>>
>> Ur product UPS will expire in X days....
>> Ur product Battery will expire in X days....
>> Ur product SMPS will expire in X days....
>> Ur product Phone will expire in X days....
>> Ur product Data Card will expire in X days....
>> Ur product Lenser will expire in X days....
>> Ur product Keyboard will expire in X days....
>> Ur product Mouse will expire in X days....
>> Ur product Monitor will expire in X days....
>>
>> Or
>> Each time you open a sheet, for exemple sheet3
>>
>> That automaticaly a message shows and display:
>>
>> Ur product Keyboard will expire in X days....
>> Ur product Mouse will expire in X days....
>> Ur product Monitor will expire in X days....
>>
>>
>> Best Regards
>>
>> André Luiz D´Avila
>>
>>
>>
>> 2011/4/28 syed aliya raza hashim <hashim...@gmail.com>
>>
>>> Hi,
>>> Problem is stated in file itself ,
>>> the problem is how to displaly the meassage box suppose date column is
>>> there is all sheets i want to display the meaasage like this product will be
>>> expired on one month or 30 days or more
>>> *when we open the file "*
>>>
>>> see in the attachment
>>>
>>> --
>>> syed aliya
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> syed aliya
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
