Using excel vba macro to trigger for PM due date

H

huat

New Member
Joined
Jan 8, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
Currently we are using hard copy form when doing our PM (Preventive Maintenance). Pls refer to the attached file "PM hard copy form.jpg"

PM hard copy form.jpg

We have a total of 47 equipment and the PM frequencies has monthly and quarterly and this will result in a lot of paper printed to carry out the PM, which is not environmental friendly. This is what we are doing now:
1. Based on manual tracking on monthly due dates of the respective equipment, we will assign the technician to carry out the PM before the due date.
2. The technician will need to print out the form when he’s doing the PM and go through/record down the steps when doing, put down the date and name on the form.
3. The supervisor will review through the completed form and update the next due date manually
4. Before the next due date, steps 1 to 3 will repeat again.

I'm thinking of a system using 4 VBA macros to have some automation and triggering the next due date of the respective equipment, as follow:

1. First the supervisors will key in all the existing records that they have, as is in the worksheet "Master List" that will have the last PM done date and the next due date which is one month later.

2. 7 days before the due date, the system will prompt the supervisors the due date of the equipment based on the worksheet "Master List" through email in worksheet "drop down menu list". I'm thinking of using VBA macro (1st macro) to do this but don't know how, pls advise. Thanks.
3. When doing the PM, instead of using the hard copy form, the technician will use a tablet that contains such an e-form (the format is the same as the hardcopy form as shown in the attached file "PM hard copy form.jpg" and this e-form can be found in worksheets "Equipment Type 1 Template" to "Equipment Type 6 Template"). On the day of PM, first they will go to the worksheet "Welcome page" and select the equipment type then press the "Confirm?" button. This will execute a VBA macro (2nd macro) to go to the correct worksheet in either one of "Equipment Type 1 Template" to "Equipment Type 6 Template". Technician will go through the steps on the e-form when doing PM, select the relevant fields in the e-form then submit to the supervisors for review by clicking on the button "Submit for Review". This will trigger another VBA macro (3rd macro) to send another workbook "PM Form for Review.xlsx" with the relevant fields filled up worksheet "PM Form for Review" and the worksheet "drop down menu list" to the supervisors through email in worksheet "drop down menu list". As long as one of the 2 supervisors review through and clicked on the cell and select his name and clicked the button "Update", the system will trigger another VBA macro (4th macro) to update the next due date of the PM-ed equipment with another row right after the last row in the worksheet "Master List".
4. Before the next due date, Steps 2 and 3 will repeat again.
5. This system can allow us to retrieve the history record of any equipment that is tracked in the system.

Sorry, I don't know how to use XL2BB to display multiple worksheets so I capture the screen shots showing what worksheets are inside each workbook. There are 2 workbooks here, ie. Workbook 1 is "trying out PM scheduling on excel.xlsx" and Workbook 2 is "PM Form for Review.xlsx". Pls refer to the file "worksheets in workbook 1.jpg" and "worksheets in workbook 2.jpg " below.

worksheets in workbook 1.jpg


worksheets in workbook 2.jpg


The actual worksheets in Workbook 1 "trying out PM scheduling on excel.xlsx":

trying out PM scheduling on excel.xlsx
ABCDEFGH
1
2Hi, what type of equipment PM would you like to do today? Pls select and press the button
3
4Pls select below by clicking on the Green cell below
5
6
7
Welcome page
Cells with Data Validation
CellAllowCriteria
A5List='drop down menu list'!$A$3:$A$8


Not sure why the "Confirm?" button didn't come out in the above. It should look like that:

worksheets in workbook 1.jpg


trying out PM scheduling on excel.xlsx
BCDEFGHI
1
2Ok/Not OkYes/NoTech nameSup nameDayMonthYear
3OkYesTech 1Sup 11Jan2019
4Not OkNoTech 2Sup 22Feb2020
5Tech 33Mar2021
6Tech 44Apr2022
7Tech 55May2023
8Tech 66Jun2024
97Jul2025
108Aug2026
119Sep2027
1210Oct2028
1311Nov2029
1412Dec2030
1513
1614
1715
1816
1917
2018
2119
2220
2321
2422
2523
2624
2725
2826
2927
3028
3129
3230
3331
drop down menu list


trying out PM scheduling on excel.xlsx
ABCDEFG
1ItemMachine No.Equipment TypeSerial No.Cap. DateLast PM DateNextPM Date
21Equipment 11Equipment Type 11111111.Aug.200415/12/202014/1/2021
32Equipment 12Equipment Type 11111212.Jan.199916/12/202015/1/2021
43Equipment 13Equipment Type 11111319.Mar.200517/12/202016/1/2021
54Equipment 14Equipment Type 11111404.Jan.200418/12/202017/1/2021
65Equipment 15Equipment Type 11111531.Mar.200519/12/202018/1/2021
76Equipment 16Equipment Type 11111601.Sep.200620/12/202019/1/2021
87Equipment 17Equipment Type 11111701.Sep.200721/12/202020/1/2021
98Equipment 21Equipment Type 21111801.Sep.200822/12/202021/1/2021
109Equipment 22Equipment Type 21111901.Sep.200923/12/202022/1/2021
1110Equipment 23Equipment Type 21112031.Apr.201524/12/202023/1/2021
1211Equipment 24Equipment Type 21112131.Sep.201525/12/202024/1/2021
1312Equipment 25Equipment Type 21112230.9.201926/12/202025/1/2021
1413Equipment 26Equipment Type 21112312.Nov.200227/12/202026/1/2021
1514Equipment 27Equipment Type 21112412.Nov.200228/12/202027/1/2021
1615Equipment 28Equipment Type 21112512.Nov.200229/12/202028/1/2021
1716Equipment 29Equipment Type 21112612.Nov.200230/12/202029/1/2021
1817Equipment 31Equipment Type 31112712.Nov.200231/12/202030/1/2021
1918Equipment 32Equipment Type 31112830.Sep.200215/12/202014/1/2021
2019Equipment 33Equipment Type 31112922.Sep.200416/12/202015/1/2021
2120Equipment 34Equipment Type 31113026.Jan.200517/12/202016/1/2021
2221Equipment 41Equipment Type 41113127.Jan.200518/12/202017/1/2021
2322Equipment 42Equipment Type 41113227.Jan.200519/12/202018/1/2021
2423Equipment 43Equipment Type 41113330.May.200520/12/202019/1/2021
2524Equipment 44Equipment Type 41113412.Apr.200521/12/202020/1/2021
2625Equipment 45Equipment Type 41113512.Apr.200522/12/202021/1/2021
2726Equipment 46Equipment Type 41113630.Sep.200523/12/202022/1/2021
2827Equipment 51Equipment Type 51113701.Sep.200624/12/202023/1/2021
2928Equipment 52Equipment Type 51113830.Dec.200925/12/202024/1/2021
3029Equipment 53Equipment Type 51113930.Dec.200926/12/202025/1/2021
3130Equipment 54Equipment Type 51114031.Mar.201027/12/202026/1/2021
3231Equipment 55Equipment Type 51114130.Jun.201028/12/202027/1/2021
3332Equipment 56Equipment Type 51114230.Jun.201029/12/202028/1/2021
3433Equipment 57Equipment Type 51114330.Jun.201030/12/202029/1/2021
3534Equipment 58Equipment Type 51114430.Jun.201031/12/202030/1/2021
3635Equipment 59Equipment Type 51114531.Aug.200415/12/202014/1/2021
3736Equipment 60Equipment Type 61114620.May.200916/12/202015/1/2021
3837Equipment 61Equipment Type 61114721.Aug.200917/12/202016/1/2021
3938Equipment 62Equipment Type 61114829.Apr.201118/12/202017/1/2021
4039Equipment 63Equipment Type 61114930.Jun.200519/12/202018/1/2021
4140Equipment 61Equipment Type 61115031.Oct.201520/12/202019/1/2021
4241Equipment 62Equipment Type 61115130.Nov.200521/12/202020/1/2021
4342Equipment 63Equipment Type 61115231.Dec.200522/12/202021/1/2021
4443Equipment 64Equipment Type 61115330.Nov.200523/12/202022/1/2021
4544Equipment 65Equipment Type 61115430.Nov.200524/12/202023/1/2021
4645Equipment 66Equipment Type 61115530.Nov.200525/12/202024/1/2021
4746Equipment 67Equipment Type 61115630.Nov.200526/12/202025/1/2021
4847Equipment 68Equipment Type 61115730.Nov.200527/12/202026/1/2021
Master List
Cell Formulas
RangeFormula
G2:G48G2=F2+30


trying out PM scheduling on excel.xlsx
ABCDEFG
1M/C Description :Equipment Type 1Year :
2
3M/C No : M/C Serial No : Month :
4
5NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
61Inspect AOk/Not Ok
72Inspect BOk/Not Ok
83Inspect COk/Not Ok
94Inspect DOk/Not Ok
10Done By :Tech name
11Date :DayMonthYear
12Reviewed By :
13
14
15
16
Equipment Type 1 Template
Cell Formulas
RangeFormula
D3D3=IF(ISNA(VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE)),"",VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE))
Cells with Data Validation
CellAllowCriteria
B3List='drop down menu list'!$B$14:$B$20
G3List='drop down menu list'!$O$3:$O$14
G1List='drop down menu list'!$P$3:$P$14
C6:G9List='drop down menu list'!$J$2:$J$4
C10:G10List='drop down menu list'!$L$2:$L$8
C11List='drop down menu list'!$N$2:$N$33
D11List='drop down menu list'!$O$2:$O$14
E11:G11List='drop down menu list'!$P$2:$P$14
C12:G12List='drop down menu list'!$M$3:$M$4


Not sure why the "Submit for Review" button never come out above. It should look like that for all the worksheets "Equipment Type 1 Template" to "Equipment Type 6 Template".

1610175640349.png


trying out PM scheduling on excel.xlsx
ABCDEFG
1M/C Description :Equipment Type 2Year :
2
3M/C No : M/C Serial No : Month :
4
5NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
61Inspect AOk/Not Ok
72Inspect BOk/Not Ok
83Inspect COk/Not Ok
94Inspect DOk/Not Ok
105Inspect EOk/Not Ok
11Done By :Tech name
12Date :DayMonthYear
13Reviewed By :
14
15
16
17
18
Equipment Type 2 Template
Cell Formulas
RangeFormula
D3D3=IF(ISNA(VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE)),"",VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE))
Cells with Data Validation
CellAllowCriteria
B3List='drop down menu list'!$B$21:$B$29
G1List='drop down menu list'!$P$3:$P$14
G3List='drop down menu list'!$O$3:$O$14
C6:G10List='drop down menu list'!$J$2:$J$4
C11:G11List='drop down menu list'!$L$2:$L$8
C12List='drop down menu list'!$N$2:$N$33
D12List='drop down menu list'!$O$2:$O$14
E12:G12List='drop down menu list'!$P$2:$P$14
C13:G13List='drop down menu list'!$M$3:$M$4
C14:G14List='drop down menu list'!$J$2:$J$4


trying out PM scheduling on excel.xlsx
ABCDEFG
1M/C Description :Equipment Type 3Year :
2
3M/C No : M/C Serial No : Month :
4
5NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
61Inspect AOk/Not Ok
72Inspect BOk/Not Ok
83Inspect COk/Not Ok
94Inspect DOk/Not Ok
105Inspect EOk/Not Ok
116Inspect FOk/Not Ok
12Done By :Tech name
13Date :DayMonthYear
14Reviewed By :
15
16
17
18
19
Equipment Type 3 Template
Cell Formulas
RangeFormula
D3D3=IF(ISNA(VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE)),"",VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE))
Cells with Data Validation
CellAllowCriteria
C6:G11List='drop down menu list'!$J$2:$J$4
C12:G12List='drop down menu list'!$L$2:$L$8
C13List='drop down menu list'!$N$2:$N$33
D13List='drop down menu list'!$O$2:$O$14
E13:G13List='drop down menu list'!$P$2:$P$14
C14:G14List='drop down menu list'!$M$3:$M$4
C15:G15List='drop down menu list'!$J$2:$J$4
G3List='drop down menu list'!$O$3:$O$14
G1List='drop down menu list'!$P$3:$P$14
B3List='drop down menu list'!$B$30:$B$33


trying out PM scheduling on excel.xlsx
ABCDEFG
1M/C Description :Equipment Type 4Year :
2
3M/C No : M/C Serial No : Month :
4
5NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
61Inspect AOk/Not Ok
72Inspect BOk/Not Ok
83Inspect COk/Not Ok
94Inspect DOk/Not Ok
105Inspect EOk/Not Ok
116Inspect FOk/Not Ok
127Inspect GOk/Not Ok
138Inspect HOk/Not Ok
149Inspect IOk/Not Ok
15Done By :Tech name
16Date :DayMonthYear
17Reviewed By :
18
19
20
21
22
Equipment Type 4 Template
Cell Formulas
RangeFormula
D3D3=IF(ISNA(VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE)),"",VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE))
Cells with Data Validation
CellAllowCriteria
C6:G14List='drop down menu list'!$J$2:$J$4
C15:G15List='drop down menu list'!$L$2:$L$8
C16List='drop down menu list'!$N$2:$N$33
D16List='drop down menu list'!$O$2:$O$14
E16:G16List='drop down menu list'!$P$2:$P$14
C17:G17List='drop down menu list'!$M$3:$M$4
C18:G18List='drop down menu list'!$J$2:$J$4
G3List='drop down menu list'!$O$3:$O$14
G1List='drop down menu list'!$P$3:$P$14
B3List='drop down menu list'!$B$34:$B$39


trying out PM scheduling on excel.xlsx
ABCDEFG
1M/C Description :Equipment Type 5Year :
2
3M/C No : M/C Serial No : Month :
4
5NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
61Inspect AOk/Not Ok
72Inspect BOk/Not Ok
83Inspect COk/Not Ok
94Inspect DOk/Not Ok
105Inspect EOk/Not Ok
116Inspect FOk/Not Ok
12Done By :Tech name
13Date :DayMonthYear
14Reviewed By :
15
16
17
18
19
Equipment Type 5 Template
Cell Formulas
RangeFormula
D3D3=IF(ISNA(VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE)),"",VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE))
Cells with Data Validation
CellAllowCriteria
C6:G11List='drop down menu list'!$J$2:$J$4
C12:G12List='drop down menu list'!$L$2:$L$8
C13List='drop down menu list'!$N$2:$N$33
D13List='drop down menu list'!$O$2:$O$14
E13:G13List='drop down menu list'!$P$2:$P$14
C14:G14List='drop down menu list'!$M$3:$M$4
C15:G15List='drop down menu list'!$J$2:$J$4
G3List='drop down menu list'!$O$3:$O$14
G1List='drop down menu list'!$P$3:$P$14
B3List='drop down menu list'!$B$40:$B$48


trying out PM scheduling on excel.xlsx
ABCDEFG
1M/C Description :Equipment Type 6Year :
2
3M/C No : M/C Serial No : Month :
4
5NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
61Inspect AOk/Not Ok
72Inspect BOk/Not Ok
83Inspect COk/Not Ok
94Inspect DOk/Not Ok
105Inspect EOk/Not Ok
116Inspect FOk/Not Ok
127Inspect GOk/Not Ok
13Done By :Tech name
14Date :DayMonthYear
15Reviewed By :
16
17
18
19
20
Equipment Type 6 Template
Cell Formulas
RangeFormula
D3D3=IF(ISNA(VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE)),"",VLOOKUP(B3,'Master List'!$B$2:$G$48,3,FALSE))
Cells with Data Validation
CellAllowCriteria
C6:G12List='drop down menu list'!$J$2:$J$4
C13:G13List='drop down menu list'!$L$2:$L$8
C14List='drop down menu list'!$N$2:$N$33
D14List='drop down menu list'!$O$2:$O$14
E14:G14List='drop down menu list'!$P$2:$P$14
C15:G15List='drop down menu list'!$M$3:$M$4
C16:G16List='drop down menu list'!$J$2:$J$4
G3List='drop down menu list'!$O$3:$O$14
G1List='drop down menu list'!$P$3:$P$14
B3List='drop down menu list'!$B$49:$B$60




The actual worksheets in Workbook 2 "PM Form for Review.xlsx":

PM Form for Review.xlsx
ABCDEFGHI
1
2Ok/Not OkYes/NoTech nameSup nameEmailDayMonthYear
3OkYesTech 1Sup 1sup1@test.com1Jan2019
4Not OkNoTech 2Sup 2sup2@test.com2Feb2020
5Tech 33Mar2021
6Tech 44Apr2022
7Tech 55May2023
8Tech 66Jun2024
97Jul2025
108Aug2026
119Sep2027
1210Oct2028
1311Nov2029
1412Dec2030
1513
1614
1715
1816
1917
2018
2119
2220
2321
2422
2523
2624
2725
2826
2927
3028
3129
3230
3331
drop down menu list


PM Form for Review.xlsx
ABCDEFGH
1
2M/C Description :Equipment Type 1Year :2021
3
4M/C No : Equipment 11M/C Serial No :11111Month :Jan
5
6NO.MONTHLY CHECKLISTREMARKS (pls select fom the dropdown menu)
71Inspect AOk
82Inspect BOk
93Inspect COk
104Inspect DOk
11Done By :Ok
12Date :6Jan2021
13Reviewed By :Sup name
14
15
16
17
PM Form for Review
Cells with Data Validation
CellAllowCriteria
D13:H13List='drop down menu list'!$E$2:$E$4


Sorry for the long post. Hope experts can help me. Thanks.
 

Attachments

  • 1610175125404.png
    1610175125404.png
    13.9 KB · Views: 22

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
.
It would be best to post your workbook (no confidential information within) to a Cloud website so it could be downloaded. This Forum does not provide for
posting workbooks.
 
Upvote 0
Logit said:
.
It would be best to post your workbook (no confidential information within) to a Cloud website so it could be downloaded. This Forum does not provide for
posting workbooks.
Click to expand...
Hi Logit, can you help? Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Data Validation using VBA and Excel function
Replies
0
Views
429
ManUBlueJay
M
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
175
helpcharity
H
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
318
etaf
etaf
S
  • Question Question
Worksheet Change Event
Replies
2
Views
721
sspatriots
S
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
774
sspatriots
S

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top