Vba with three click button help

poem · #1 09-19-2015, 03:34 AM

GOOD AFTER NOON,
PLEASE MY DEAR

I am looking for Excel VBA three code counter that can count the number of expiry, EXPIRE WITHIN THE NEXT 30 DAYS list of date obtained by the name in an ROP expiry and msg box appearance if not word informed fill it at status when the name under expiry within next 30 days .
Date is now 19/09/2015
FRIST BUTTON Expiry---------------- fill back ground cell red
SECOND BUTTON EXPIRE WITHIN THE NEXT 30 DAYS----------fill back ground cell yellow
THREE SCECOND NOTICICATION …….. MsgBox("The rop licence for " & Name & " will expire in " & Diff & appearance if not word informed fill it at status when the name under expiry within next " days.", vbCritical, "Warning")

charlesdh · #2 09-19-2015, 12:49 PM

Hi,

It may help if you attached a file for us to work with.

poem · #3 09-20-2015, 12:29 AM

Quote:

Originally Posted by charlesdh

Hi,

It may help if you attached a file for us to work with.

please help us .

charlesdh · #4 09-20-2015, 11:20 AM

Hi,

For one of the buttons you have

Quote:

SECOND BUTTON EXPIRE WITHIN THE NEXT 30 DAYS----------fill back ground cell yellow

Should this not be "60" days instead of "30"?.
You already have a button fro "30".

NoSparks · #5 09-20-2015, 12:45 PM

I suspect the first button is supposed to be "Expired" rop licenses.

My question is about the third button.... "THREE SCECOND NOTICICATION"

Does the OP want a message box that automatically closes after 3 seconds?

charlesdh · #6 09-20-2015, 04:06 PM

NoSparks,

Good question. Perhaps the OP can give a better explanation of what they want.

poem · #7 09-20-2015, 11:41 PM

it is right needs message box when click at notification button . ROP can give a better explanation of what they want.

remarks :- if sheet user fill the word "informed" at status column if case expiry with next 30 days massage box not give explanation name & and rop license otherwise do it.

charlesdh · #8 09-21-2015, 10:27 AM

Hi,

I have code for "Expired" and 30 day to expire.
No code for now on "Notification".

poem · #9 09-22-2015, 04:45 AM

i appericate you too much you have done .
i mean you code on "Notification".
please browse attachment snap shot .
if you write like this code
Sub pop message _Click() '' notification
Dim i As Long
Dim expdate As String
Dim Edate As String
Edate = Format(Now(), "mm/dd/yyyy")
'' look for expired dates ''
For i = 2 To 9
If DateDiff("d", Cells(i, 2).Value, Edate) <= 30 Then
AND
ForEachcell In C2:C9
IfIsEmpty(cell) Then
cell.Value = MSGBOX
and fill cell with black ground white and word informed

End If
Next i
End Sub

NoSparks · #10 09-22-2015, 09:22 AM

If I understand what you want, and I'm not sure I do....
Perhaps something like this ?

Code:

Sub pop_message_Click()  ' notification
    Dim rng As Range
    Dim cel As Range
    Dim lastrow As Long
    Dim Licensee As String
    Dim msg1 As String, msg2 As String, msg3 As String

Application.ScreenUpdating = False

With Sheets("Sheet1")        '<~~ change to suit
    lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = .Range("B2:B" & lastrow)
    msg1 = "THE ROP LINCENCE FOR MR" & vbCrLf & vbCrLf
    msg3 = vbCrLf & "EXPIRE WITHIN NEXT 30 DAYS"
    
    For Each cel In rng
        If cel.Value >= Date And cel.Value <= Date + 30 And cel.Offset(0, 1).Value <> "informed" Then
            Licensee = cel.Offset(0, -1).Value
            msg2 = msg2 & "      " & UCase(Licensee) & vbCrLf
            cel.Interior.ColorIndex = 0
            cel.Offset(0, 1).Value = "informed"
        End If
    Next cel
End With

If Len(msg2) > 0 Then MsgBox msg1 & msg2 & msg3, vbCritical, "Warning"
   
Application.ScreenUpdating = True

End Sub

poem · #11 09-22-2015, 09:51 AM

May you attche me the all code include notifation the same file

NoSparks · #12 09-22-2015, 10:11 AM

I use different regional settings than you so hope this works.

poem · #13 09-22-2015, 11:00 AM

It is really professional job. That is else what I am looking for exactly.
You open my mind to other idea. No need to type write word “ informed” which become old fashion .
but you are deed which automatic come after click to pop message okay and use vba on purpose it .

You can say solved .

poem · #14 09-22-2015, 11:02 AM

many thanks

NoSparks · #15 09-22-2015, 11:20 AM

Thanks for reporting back.

As you can see, with Excel, there is almost always more than one way to do anything.

Please mark this thread as solved. Thanks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to import excel calender data into outlook - with a click of a button	levrone	Excel	24	10-01-2021 05:33 AM
ActiveX Command Button disappears after click	Bengt	Word VBA	1	09-18-2013 12:42 PM
Animations on Button Click	dvogler	PowerPoint	1	03-01-2012 12:46 AM
Word doc bug when closing from userform command button click macro	Joe Patrick	Word	1	07-05-2011 08:53 PM
Button click event from a noob	jsampson	Outlook	0	10-12-2010 01:48 PM