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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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
797
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1DateForkliftTechStatusTimestamp
29/03/2019Lift2Tech1Out of Service9/03/2019 10:17 AM
Service
Cell Formulas
RangeFormula
A2=TODAY()
E2=NOW()


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
797
Office Version
  1. 365
Platform
  1. Windows

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
797
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1DateForkliftTechStatusTime StampStatusTime Stamp
202-03-19Lift2Tech3Out of Service02-03-19 21:24Operational02-03-19 21:27
302-03-19Lift4Tech5Out of Service02-03-19 21:26Operational02-03-19 21:28
419-03-19Lift3Tech8Out of Service02-03-19 21:40Operational02-03-19 21:40
Database
 
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,431
Members
431,879
Latest member
KiwDaWabbit

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
Top