• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro

P

prasada2002

New Member
Dear Chandoo


I have a check sheet which is used for every day to day purpose for data collection. I have applied a formula using if condition which defines the notification on schedule, say on 10th  of every month.Now the problem is that the formula is prone to destruction and has to keep a track of it.Hence i want to implement a macro for the same which will display a pop up on the scheduled date.Can you please help me out as i am not well versed with macro.The for mual i use is given below.

thanks in advance.


=IF('Channel list'!C1="10","SHIFT ENGINEER`s TO CLEAN KPA BACK SIDE FILTER",IF('Channel list'!C1="20","A SHIFT ENGINEER TO CLEAN KPA BACK SIDE FILTER",IF('Channel list'!C1="30","A SHIFT ENGINEER TO CLEAN KPA BACK SIDE FILTER","")))


=IF('Channel list'!C1="15","A SHIFT ENGINEER TO CLEAN KPA FRONT,POWER SUPPLY FILTER",IF('Channel list'!C1="30","A SHIFT ENGINEER TO CLEAN KPA FRONT,POWER SUPPLY FILTER",""))


channel list C1 - =TEXT(TODAY(),"dd")


=IF(Weather!B4="Saturday","B Shift Engineer to Prepare weekly report","")


weather B4 -  =TEXT(WEEKDAY(TODAY()),"dddd")
 
Place this in the ThisWorkbook module. It will run whenever the workbook is opened.


Code: 
Private Sub Workbook_Open()

Dim Day As Integer

Dim Weekday As String

Dim dMessage As String

Dim xReport As String

Dim xDate As Date

xDate = Date


'What day of month is it?

Day = Format(xDate, "d")

'What weekday is it?

Weekday = Format(xDate, "dddd")


Select Case Day

Case 10

dMessage = "A SHIFT ENGINEER TO CLEAN KPA BACK SIDE FILTER"

Case 15

dMessage = "A SHIFT ENGINEER TO CLEAN KPA FRONT, POWER SUPPLY FILTER"

Case 20

dMessage = "A SHIFT ENGINEER TO CLEAN KPA BACK SIDE FILTER"

Case 30

dMessage = "A SHIFT ENGINEER TO CLEAN KPA BACK AND FRONT SIDE FILTERS"

Case Else

dMessage = ""

End Select


If Weekday = "Saturday" Then

xReport = "B Shift Engineer to prepare weekly report"

Else

xReport = ""

End If


dMessage = dMessage & vbNewLine & xReport

If dMessage = "" Then Exit Sub

MsgBox dMessage, vbOKOnly, "Tasks for the day"


End Sub
 
Correction, last 3 lines of code should be changed to this

Code: 
If dMessage = "" And xReport = "" Then Exit Sub

dMessage = dMessage & vbNewLine & xReport

MsgBox dMessage, vbOKOnly, "Tasks for the day"
 
You must log in or register to reply here.
Back
Top