Maintenance Downtime Tracker

[mlee1]

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.

 

[RasGhul]

Re: Maintenance Downtime Tracker help

Maybe try this;

https://docs.google.com/spreadsheets/d/15ogLy-3d9Sk2aSMhzXwo_ag22TCE7JSheq_xD0gqplw/edit?usp=sharing

Change the Forklift names to unique forklift Id's and Techs to names as required. Basic Logging to Sheet 2

 

[mlee1]

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]

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]

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]

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;

Book1
	A	B	C	D	E
1	Date	Forklift	Tech	Status	Timestamp
2	9/03/2019	Lift2	Tech1	Out of Service	9/03/2019 10:17 AM
Service

Cell Formulas
Range	Formula
A2	=TODAY()
E2	=NOW()

Right Click on your log service shape and add this 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]

Re: Maintenance Downtime Tracker help

Hi Mlee,

Did you try my original file from the Dropbox link?

 

[mlee1]

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]

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?

Book1
	A	B	C	D	E	F	G
1	Date	Forklift	Tech	Status	Time Stamp	Status	Time Stamp
2	02-03-19	Lift2	Tech3	Out of Service	02-03-19 21:24	Operational	02-03-19 21:27
3	02-03-19	Lift4	Tech5	Out of Service	02-03-19 21:26	Operational	02-03-19 21:28
4	19-03-19	Lift3	Tech8	Out of Service	02-03-19 21:40	Operational	02-03-19 21:40
Database

 

[mlee1]

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.