Faults tracking system - VBA & Excel?

[si3po]

hi all,

bit of a vague task, i'm aware, however i'm hoping that you might be able to advise some direction for me to follow... before i start, we do not have the ability to use MS Access or a 3rd party specialised fleet management software due to a number of reasons, not least that this is an internal off-line system with specific high security restrictions.

In my office we currently use a basic Excel Spreadsheet track and manage faults/rectification works on vehicles. We manage records from across our fleet, and act on behalf of multiple departments that each own multiple vehicles that they can report a faults for; additionally, a single vehicle in the fleet can have nil or multiple faults raised against it. Each fault record has a number of priority states depending on how urgently it is needed, e.g. 'Immediate', 'Urgent', 'Priority', 'Mission Critical' etc. We also update each record status as and when it progresses, e.g. 'Open', 'Spares In Course Of Issue', 'Invest', 'Closed' etc.

With the way the system is built currently, there is much duplication of work and a high-probability of error when entering details, and i am trying to minimise this as much as possible and totally streamline and increase efficiencies across the board...

What I'm trying to do is use Excel, maybe User forms, VBA and such to create an in-house system whereby a set of forms/single form with multiple tabs can be used to input new, update existing and close down rectified faults with a progression. I would expect the form to use drop downs, radio selection buttons etc and retain previous combinations of Part Number/SKU numbers (i think a dictionary on a hidden sheet would do this??). Also, with any record created, they would need to be grouped by vehicle owner department and in alphabetical order of vehicle registration code (I think that would be easy enough though).

Now, I believe that this is all within the scope of Excel and VBA, but I'm just looking for:

1. reassurance that i'm right and that
2. any possible direction from others who might know of existing Excel/VBA solutions
3. where i would find further anything that would help me

That's a lot of info, and I can't apologise enough, but if anyone has ideas that might be useful - i'm all ears!

Regards,

Si3PO

 

[dmt32]

My personal view from your post is that you are using the wrong application.

Excel is a spreadsheet program and although you can create multiple tables, there is no relationship between them. As you need to track your company fleet data info in manner described, you are likely using Excel, to encounter problems maintaining all the required (data) tables needed in the application as all of these need to tie together and problems only increase if, as I suspect, you will want to share the workbook with more than one user.

An Excel application can be made more intuitive with inclusion of well-designed UserForms but do be aware any userform requires the developer to write all necessary code which for a complex application, will be very extensive.

Appreciate that you say that you cannot use access but there are other applications you might want to consider like Power Apps

Business Apps | Microsoft Power Apps

which need very little programming & may be of help to you. If though, you intend to pursue your project in Excel then I can only suggest that you search web for Excel based application & see what others have done / offer.

As I said, this is just my personal view, others here may offer alternative views.

Dave

 

[si3po]

My personal view from your post is that you are using the wrong application.

Excel is a spreadsheet program and although you can create multiple tables, there is no relationship between them. As you need to track your company fleet data info in manner described, you are likely using Excel, to encounter problems maintaining all the required (data) tables needed in the application as all of these need to tie together and problems only increase if, as I suspect, you will want to share the workbook with more than one user.

An Excel application can be made more intuitive with inclusion of well-designed UserForms but do be aware any userform requires the developer to write all necessary code which for a complex application, will be very extensive.

Appreciate that you say that you cannot use access but there are other applications you might want to consider like Power Apps

Business Apps | Microsoft Power Apps

which need very little programming & may be of help to you. If though, you intend to pursue your project in Excel then I can only suggest that you search web for Excel based application & see what others have done / offer.

As I said, this is just my personal view, others here may offer alternative views.

Dave

Thanks @dmt32 for the really informative reply.

I'll get the multiple users out of the way first off, as that's quite simple as far as I see it - Whilst there is the possibility of multiple users at anyone time, we generally only have one person who will be editing on any one day. Also, we use SharePoint and the Excel Workbook will be held within a limited permissions folder, and as Excel autosaves and updates with multiple users as a matter of course, only permitted users can view/edit it at any one time whilst changes are auto-saved with versioning history if it all goes wrong! However, as an added backup, I intend to lock everything down and have view-only enforced for all but the users with a login and edit permissions, so that should negate the majority of the issues I foresee from that side; unless you have more?

When you say relationship between the tables, if you are referring to the relationship as seen in a Database, then i do not expect that will be required as we do not intend to link between any of the tables for data. The solution as I see it will have multiple Excel Tables on a single Worksheet, each Excel Table assigned to one of the six departments owning the Vehicles. As a new fault is recorded, that fault is input as a new row within the table of the Owning Department of the corresponding Priority requirement - for example VehID #12AA12 could have records on the "Immediate" and "Mission Critical" Sheets, but they would not be specifically related to each other other than they are against the same vehicle ID. I hope that clears that up a little?

I'd be very interested in maybe employing PowerApps for this task as all of our users have access to these and we use them for other, less complex, tasks often but have yet to build one of my own so wouldn't know where to start...

Si