Maintenance Downtime Tracker

mlee1

New Member
Joined
Feb 26, 2019
Messages
14
Hello,

First off I would like to say I am very new to macros and VBA in excel. I am trying to create a database that will log and track when our equipment is taken out of service and back in service. I would like for when my techs take a forklift out of service they click on the lift number then themselves and out of service. this in turn will record that lift number who worked on it and a time stamp of when it was taken out of service on sheet2 of the work book. Then when they click in service it adds that time stamp to that same lift. I just have no idea how to create that.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mlee1

New Member
Joined
Feb 26, 2019
Messages
14
Re: Maintenance Downtime Tracker help

Thank you this looks exactly like i wanted, how exactly does the data go from sheet 1 to sheet 2? Are the techs supposed to select the lift then hit log service the tech name log service out of service log service? I'm sorry if this is a dumb question i am still very new to this. Thank you for the assistance.
 

mlee1

New Member
Joined
Feb 26, 2019
Messages
14
Re: Maintenance Downtime Tracker help

The log service button is not functional when i download this to excel. When i go to step into the macro for Shape3_Click() there is no code.
 

mlee1

New Member
Joined
Feb 26, 2019
Messages
14

ADVERTISEMENT

Re: Maintenance Downtime Tracker help

Hello again, so i took the sheet from above and modified a little bit to simplify the form. However, now i need for when each combobox is filled in and I hit the Log Service button that the information from the three comboboxes get recorded under the database tab with a time stamp. Can anyone help?

https://docs.google.com/spreadsheets/d/1YGW1ZrRp3hPgO4fwJfTzvEd1Hi9mVZaR4QXPNJPloUw/edit?usp=sharing
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Re: Maintenance Downtime Tracker help

The log service button is not functional when i download this to excel. When i go to step into the macro for Shape3_Click() there is no code.

Looks like google sheets removes the macros and user-forms when you upload them.

You could setup your Service sheet like the following and data validate your Forklift ID and Tech names;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Forklift</td><td style="font-weight: bold;;">Tech</td><td style="font-weight: bold;;">Status</td><td style="font-weight: bold;;">Timestamp</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">9/03/2019</td><td style=";">Lift2</td><td style=";">Tech1</td><td style=";">Out of Service</td><td style=";">9/03/2019 10:17 AM</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Service</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=TODAY(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=NOW(<font color="Blue"></font>)</td></tr></tbody></table></td></tr></table><br />

Right Click on your log service shape and add this code;

Code:
Sub RectangleRoundedCorners1_Click()Dim I As Long
Dim tdate As Date


Application.ScreenUpdating = False
Sheets("Database").Activate
    ActiveSheet.Range("A2").Activate


    Do While IsEmpty(ActiveCell.Offset(I, 0)) = False
        I = I + 1
Loop
ActiveCell.Offset(I, 0).Value = Sheets("Service").Range("a2").Value
ActiveCell.Offset(I, 1).Value = Sheets("Service").Range("b2").Value
ActiveCell.Offset(I, 2).Value = Sheets("Service").Range("c2").Value
ActiveCell.Offset(I, 3).Value = Sheets("Service").Range("d2").Value
ActiveCell.Offset(I, 4).Value = Sheets("Service").Range("e2").Value
       
   ThisWorkbook.RefreshAll
    Sheets("Service").Activate
   
 Application.ScreenUpdating = True


End Sub
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609

ADVERTISEMENT

Re: Maintenance Downtime Tracker help

Hi Mlee,

Did you try my original file from the Dropbox link?
 

mlee1

New Member
Joined
Feb 26, 2019
Messages
14
Re: Maintenance Downtime Tracker help

Hi Ras,

I thought i responded to this but i guess i did not. The sheet is functional and has been working great for the last week, so thank you for that. I do have a question now that its been used. I am wondering if it is possible to change the way the data is logging (for ease of reporting). Is it possible for when a lift is logged out of service it record a time stamp in column d (labeled out of service) then when logged in service it log a time stamp in column e (labeled in service)? I believe i could then filter so the lifts are next to each other and merge. The other idea was to see if i could get them to log the same way as above but on the same line?
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Re: Maintenance Downtime Tracker help

Hi mlee,

So just to clarify you are using "Forklift Service Log" with the grey Userform for data entry?

Also is the following example what you want to achieve for your service log? So when a tech has completed a service and the Forkilft is operational it will add Operational & timestamp to the same row?


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Forklift</td><td style="font-weight: bold;;">Tech</td><td style="font-weight: bold;;">Status</td><td style="font-weight: bold;;">Time Stamp</td><td style="font-weight: bold;;">Status</td><td style="font-weight: bold;;">Time Stamp</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">02-03-19</td><td style=";">Lift2</td><td style=";">Tech3</td><td style=";">Out of Service</td><td style="text-align: right;;">02-03-19 21:24</td><td style=";">Operational</td><td style="text-align: right;;">02-03-19 21:27</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">02-03-19</td><td style=";">Lift4</td><td style=";">Tech5</td><td style=";">Out of Service</td><td style="text-align: right;;">02-03-19 21:26</td><td style=";">Operational</td><td style="text-align: right;;">02-03-19 21:28</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">19-03-19</td><td style=";">Lift3</td><td style=";">Tech8</td><td style=";">Out of Service</td><td style="text-align: right;;">02-03-19 21:40</td><td style=";">Operational</td><td style="text-align: right;;">02-03-19 21:40</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Database</p><br /><br />
 
Last edited:

mlee1

New Member
Joined
Feb 26, 2019
Messages
14
Re: Maintenance Downtime Tracker help

Hi Ras,

Yes i am currently using the service log with the original setup, the example that you posted is what i would like for it to be where when the lift is put back into service it logs a time stamp on the same line as when it went out of service. Your example above is exactly right.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,412
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top