Operations Dashboard in Excel
Ok, so I have been tasked to build an operational dashboard for an airline maintenance planning and tracking. I have a dataset downloaded from our ERP system that lists down the aircraft tails, the workorder number, tasks in each workorder, manhours for each task, city, site(hangar or line) and the start date and end dates. There are codes that are assigned for each category of workorder, whether it is a C-Check, Line or transit. In the current scenario, we use a flight operations tracking software that gives us a hangar forecast, but then we have to get the dataset (as mentioned above) and then build a report daily to show the tails assigned for each port and then the total manhours. The report looks something similar to what you see below. Now, instead of doing it daily manually, I want to automate the process. So far, I have been able to sum the total manhours for the day, get the tails assigned for each port and location, and achieve some sort of conditional formatting to distinguish between different types of checks - green for heavy, yellow for transit and so on. What I have been unable to achieve is the aircraft is scheduled for two days grounding in the hangar, then the cells on both days should align together. As you can see in the image above, VH-AA6 has maintenance on the 8th and 9th of March, but the cells are not aligned. I tried to find a difference between the start and end date and create a helper column to assign a priority, but it didn't work. I have spent countless hours on Chatgpt to come up with a solution, but all efforts went in vague. I have seen a similar excel sheet elsewhere, but I couldn't extract the formulas or the logic since it was heavily protected. In the end I want to add a search bar and a to find a Rego/ Tail by typing in the search field and highlight in the sheet quickly. The main aim is the cell alignment for the consecutive dates. So lets say AA6 is occupying D2 on Day 8, then on the Day9 AA6 should pop up in E2. Any other aircraft on the Day9 with a day's grounding may appear in E1, or next available empty cell."Method 'Calculation' of object '_Application' failed" error occurs on unpredictable attempts
At random times, this save code decides to spit the "Method 'Calculation' of object '_Application' failed" error. It doesn't happen on any other userforms. Any idea why? Private Sub CommandButton2_Click() 'Save 'Initial: On Error Resume Next Dim rng As Range, cell As Range Dim first_DB_avail_row As Range Dim Highest_Version_Row As Long Dim existingVersions() As String Dim ver_find As Variant Dim ver_list As Object: Set ver_list = CreateObject("System.Collections.ArrayList") 'Use an ArrayList for version sorting Dim padded_list As Object: Set padded_list = CreateObject("System.Collections.ArrayList") 'Create a temporary list for padded versions to ensure order (e.g., 5.1.28 > 5.1.2) Dim v As Variant, parts As Variant Dim padded_v As String, leadChar As String, all_vers As String Dim i As Integer 'Validate entries: If Me.Caption = "First Version - Business Manager" Then 'Adding product - first version If Me.ComboBox1.Value = "" Or Me.TextBox1.Value = "" Or _ Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Then 'Check if version is not inputted MsgBox "You must complete all fields.", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If Insert_Product.ver_val = stage_entry & Major & Minor & Patch Unload Me Insert_Product.new_product_ver_cancel = False 'Set back to false from the terminate sub setting GoTo MEM_CLEAN End If Call Find_Latest_Ver 'Get the current latest version If stage_entry & Major & Minor & Patch = Highest_Version Then 'Check if version already exists MsgBox "This version already exists (as the newest version).", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If existingVersions = Split(Replace(Me.TextBox4.Value, vbCrLf, ""), "• ") For Each ver_find In existingVersions If Trim(ver_find) = Trim(stage_entry & Major & Minor & Patch) Then MsgBox "This version already exists.", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If Next ver_find If Me.ComboBox1.Value = "" Or Me.TextBox1.Value = "" Or _ Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Then 'Check if version is not inputted MsgBox "You must complete all fields.", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If Me.Hide 'This will preserve public variables, keeping the form loaded, while still allowing the PLZ_WAIT userForm to display (no modal error) 'Macro Enhancement - Start: Application.Calculation = xlCalculationManual ActiveWorkbook.UpdateRemoteReferences = False Application.EnableEvents = False 'This must be false Application.ScreenUpdating = False Application.Interactive = False Application.DisplayAlerts = False Application.DisplayStatusBar = False PLZ_WAIT.Show PLZ_WAIT.Label2.Caption = "Setting new version" DoEvents 'Allows the PLZ_WAIT userForm to display If Err.Number <> 0 Then 'For some unknown reason, the Excel error "Method 'Calculation' of object '_Application' failed" occurs on unpredictable/unrepeatable attempts to save (sub runs) - cause unknown MsgBox "An Excel error occured (""" & Err.Description & """: " & Err.Number & "). Please try again (until it works).", vbExclamation, "Business Manager" GoTo MEM_CLEAN End If 'Pull data from the latest version: ThisWorkbook.Sheets("Products").Unprotect Password:=ThisWorkbook.Sheets("Background Data").Range("CY39").Value For Each cell In ThisWorkbook.Sheets("Background Data").Range("E4:E7503") If cell.Value = ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value Then If cell.Offset(0, -2).Value = Highest_Version Then ThisWorkbook.Sheets("Products").Range("B" & Selection.Row).Value = cell.Offset(0, -3).Value 'Name ThisWorkbook.Sheets("Products").Range("C" & Selection.Row).Value = stage_entry & Major & Minor & Patch 'Product Version ThisWorkbook.Sheets("Products").Range("D" & Selection.Row).Value = cell.Offset(0, -1).Value 'File ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value = cell.Value 'ID Number ThisWorkbook.Sheets("Products").Range("F" & Selection.Row).Value = cell.Offset(0, 1).Value 'Category ThisWorkbook.Sheets("Products").Range("G" & Selection.Row).Value = cell.Offset(0, 2).Value 'Details (Description) ThisWorkbook.Sheets("Products").Range("K" & Selection.Row).Value = cell.Offset(0, 6).Value 'Release Date ThisWorkbook.Sheets("Products").Range("L" & Selection.Row).Value = cell.Offset(0, 7).Value 'Copyright Y/N button ThisWorkbook.Sheets("Products").Range("M" & Selection.Row).Value = cell.Offset(0, 8).Value 'Copyright Status ThisWorkbook.Sheets("Products").Range("N" & Selection.Row).Value = cell.Offset(0, 9).Value 'Year ThisWorkbook.Sheets("Products").Range("O" & Selection.Row).Value = cell.Offset(0, 10).Value 'Copyright Statement ThisWorkbook.Sheets("Products").Range("P" & Selection.Row).Value = cell.Offset(0, 11).Value 'Published Y/N button ThisWorkbook.Sheets("Products").Range("Q" & Selection.Row).Value = cell.Offset(0, 12).Value 'Publish Status (Date) ThisWorkbook.Sheets("Products").Range("R" & Selection.Row).Value = cell.Offset(0, 13).Value 'Web Link ThisWorkbook.Sheets("Products").Range("S" & Selection.Row).Value = cell.Offset(0, 14).Value 'Withdraw Date Highest_Version_Row = cell.Row Exit For End If End If Next cell 'Save new version to version database: Set first_DB_avail_row = ThisWorkbook.Sheets("Background Data").Range(ThisWorkbook.Sheets("Background Data").Range("C7506").End(xlUp).Offset(1, 0).Address) first_DB_avail_row.Offset(0, -1).Value = ThisWorkbook.Sheets("Products").Range("B" & Selection.Row).Value 'Name first_DB_avail_row.Value = ThisWorkbook.Sheets("Products").Range("C" & Selection.Row).Value 'Product Version first_DB_avail_row.Offset(0, 1).Value = ThisWorkbook.Sheets("Products").Range("D" & Selection.Row).Value 'File first_DB_avail_row.Offset(0, 2).Value = ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value 'ID Number first_DB_avail_row.Offset(0, 3).Value = ThisWorkbook.Sheets("Products").Range("F" & Selection.Row).Value 'Category first_DB_avail_row.Offset(0, 4).Value = ThisWorkbook.Sheets("Products").Range("G" & Selection.Row).Value 'Details (Description) first_DB_avail_row.Offset(0, 8).Value = ThisWorkbook.Sheets("Products").Range("K" & Selection.Row).Value 'Release Date first_DB_avail_row.Offset(0, 9).Value = ThisWorkbook.Sheets("Products").Range("L" & Selection.Row).Value 'Copyright Y/N button first_DB_avail_row.Offset(0, 10).Value = ThisWorkbook.Sheets("Products").Range("M" & Selection.Row).Value 'Copyright Status first_DB_avail_row.Offset(0, 11).Value = ThisWorkbook.Sheets("Products").Range("N" & Selection.Row).Value 'Year first_DB_avail_row.Offset(0, 12).Value = ThisWorkbook.Sheets("Products").Range("O" & Selection.Row).Value 'Copyright Statement first_DB_avail_row.Offset(0, 13).Value = ThisWorkbook.Sheets("Products").Range("P" & Selection.Row).Value 'Published Y/N button first_DB_avail_row.Offset(0, 14).Value = ThisWorkbook.Sheets("Products").Range("Q" & Selection.Row).Value 'Publish Status (Date) first_DB_avail_row.Offset(0, 15).Value = ThisWorkbook.Sheets("Products").Range("R" & Selection.Row).Value 'Web Link first_DB_avail_row.Offset(0, 16).Value = ThisWorkbook.Sheets("Products").Range("S" & Selection.Row).Value 'Withdraw Date 'Save Development Status Data to new version from latest version (copy over): first_DB_avail_row.Offset(0, 17).Value = ThisWorkbook.Sheets("Background Data").Range("T" & Highest_Version_Row).Value 'Title first_DB_avail_row.Offset(0, 18).Value = ThisWorkbook.Sheets("Background Data").Range("U" & Highest_Version_Row).Value 'Tags first_DB_avail_row.Offset(0, 19).Value = ThisWorkbook.Sheets("Background Data").Range("V" & Highest_Version_Row).Value 'Content first_DB_avail_row.Offset(0, 20).Value = ThisWorkbook.Sheets("Background Data").Range("W" & Highest_Version_Row).Value 'Total Tasks first_DB_avail_row.Offset(0, 21).Value = ThisWorkbook.Sheets("Background Data").Range("X" & Highest_Version_Row).Value 'Complete Tasks first_DB_avail_row.Offset(0, 22).Value = ThisWorkbook.Sheets("Background Data").Range("Y" & Highest_Version_Row).Value 'Platform first_DB_avail_row.Offset(0, 23).Value = ThisWorkbook.Sheets("Background Data").Range("Z" & Highest_Version_Row).Value 'Medium first_DB_avail_row.Offset(0, 24).Value = ThisWorkbook.Sheets("Background Data").Range("AA" & Highest_Version_Row).Value 'Framework first_DB_avail_row.Offset(0, 25).Value = ThisWorkbook.Sheets("Background Data").Range("AB" & Highest_Version_Row).Value 'Stage first_DB_avail_row.Offset(0, 26).Value = ThisWorkbook.Sheets("Background Data").Range("AC" & Highest_Version_Row).Value 'Dev Log (1) first_DB_avail_row.Offset(0, 102).Value = ThisWorkbook.Sheets("Background Data").Range("DA" & Highest_Version_Row).Value 'Dev Log (2) first_DB_avail_row.Offset(0, 103).Value = ThisWorkbook.Sheets("Background Data").Range("DB" & Highest_Version_Row).Value 'Dev Log (3) first_DB_avail_row.Offset(0, 104).Value = ThisWorkbook.Sheets("Background Data").Range("DC" & Highest_Version_Row).Value 'Dev Log (4) first_DB_avail_row.Offset(0, 105).Value = ThisWorkbook.Sheets("Background Data").Range("DD" & Highest_Version_Row).Value 'Dev Log (5) first_DB_avail_row.Offset(0, 106).Value = ThisWorkbook.Sheets("Background Data").Range("DE" & Highest_Version_Row).Value 'Dev Log (6) first_DB_avail_row.Offset(0, 107).Value = ThisWorkbook.Sheets("Background Data").Range("DF" & Highest_Version_Row).Value 'Dev Log (7) first_DB_avail_row.Offset(0, 27).Value = ThisWorkbook.Sheets("Background Data").Range("AD" & Highest_Version_Row).Value 'Total Bugs first_DB_avail_row.Offset(0, 28).Value = ThisWorkbook.Sheets("Background Data").Range("AE" & Highest_Version_Row).Value 'Resolved Bugs first_DB_avail_row.Offset(0, 29).Value = ThisWorkbook.Sheets("Background Data").Range("AF" & Highest_Version_Row).Value 'Total Requests first_DB_avail_row.Offset(0, 30).Value = ThisWorkbook.Sheets("Background Data").Range("AG" & Highest_Version_Row).Value 'Complete Requests first_DB_avail_row.Offset(0, 31).Value = ThisWorkbook.Sheets("Background Data").Range("AH" & Highest_Version_Row).Value 'Start Date first_DB_avail_row.Offset(0, 32).Value = ThisWorkbook.Sheets("Background Data").Range("AI" & Highest_Version_Row).Value 'End Date first_DB_avail_row.Offset(0, 33).Value = ThisWorkbook.Sheets("Background Data").Range("AJ" & Highest_Version_Row).Value 'Total Work Days first_DB_avail_row.Offset(0, 34).Value = ThisWorkbook.Sheets("Background Data").Range("AK" & Highest_Version_Row).Value 'Lines of Code first_DB_avail_row.Offset(0, 35).Value = ThisWorkbook.Sheets("Background Data").Range("AL" & Highest_Version_Row).Value 'Number of Features/Amenities first_DB_avail_row.Offset(0, 36).Value = ThisWorkbook.Sheets("Background Data").Range("AM" & Highest_Version_Row).Value 'Ease of Use first_DB_avail_row.Offset(0, 37).Value = ThisWorkbook.Sheets("Background Data").Range("AN" & Highest_Version_Row).Value 'Innovation/Uniqueness first_DB_avail_row.Offset(0, 38).Value = ThisWorkbook.Sheets("Background Data").Range("AO" & Highest_Version_Row).Value 'Complexity first_DB_avail_row.Offset(0, 39).Value = ThisWorkbook.Sheets("Background Data").Range("AP" & Highest_Version_Row).Value 'Optimization first_DB_avail_row.Offset(0, 40).Value = ThisWorkbook.Sheets("Background Data").Range("AQ" & Highest_Version_Row).Value 'Customer Request/Cater 'Set version list: Set rng = ThisWorkbook.Sheets("Background Data").Range("E4:E7503") ver_list.Add stage_entry & Major & Minor & Patch 'Add initial version For Each cell In rng 'Loop to add matches - Collect all versions If cell.Value = ThisWorkbook.Sheets("Products").Range("E" & Selection.Row).Value Then ver_list.Add cell.Offset(0, -2).Value End If Next cell 'Temporarily convert each version into sortable key (000.000.000) For i = 0 To ver_list.Count - 1 v = ver_list(i) leadChar = Left(v, 1) parts = Split(Mid(v, 2), ".") padded_v = leadChar padded_v = padded_v & Right("000" & parts(0), 3) padded_v = padded_v & Right("000" & parts(1), 3) padded_v = padded_v & Right("000" & parts(2), 3) ver_list(i) = padded_v & "|" & v 'Store padded key + original version 'Note: This converts, for example, "V54.17.44" to "V054017044" in order to sort, for each version (i) Next i 'Sort (descending) then strip padded key: ver_list.Sort: ver_list.Reverse For i = 0 To ver_list.Count - 1 ver_list(i) = Split(ver_list(i), "|")(1) Next i 'Note: This sorts then reverses the sort for highest version to be on top. Since sorting is left-to-right, major number will sort first, then minor, _ then patch, in that order. For the release, order will be A then B then V, since that's the alphabet's order, then it's reversed causing the order to be V then B then A. _ Basically, it is sorted lexicographically (V > B > A) then numerically (000000000), then reversed for descending order, then converted back to versioning format. 'Set validation: all_vers = " ," & Join(ver_list.ToArray, ",") 'Join all in array into one string and add initial blank option (for adding new when selected), for setting validation With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=all_vers .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = False .ShowError = False End With ThisWorkbook.Sheets("Products").Protect Password:=ThisWorkbook.Sheets("Background Data").Range("CY39").Value 'Macro Enhancement - End: Application.Calculation = xlCalculationAutomatic ActiveWorkbook.UpdateRemoteReferences = True Application.EnableEvents = True Application.ScreenUpdating = True Application.Interactive = True Application.DisplayAlerts = True Application.DisplayStatusBar = True 'Final: Unload Me 'This clears "Highest_Version" and all public variables ?....... Sheet2.UPDATE_DB_FORCE = True Application.Run "Sheet2.Worksheet_Change", Selection 'Necessary in order to update Pricing and CUS_PRO_RATINGS sheets with new version Sheet2.UPDATE_DB_FORCE = False 'Release variables/objects from memory: MEM_CLEAN: Unload PLZ_WAIT: Set PLZ_WAIT = Nothing Set rng = Nothing Set cell = Nothing Set first_DB_avail_row = Nothing ver_list.Clear: Set ver_list = Nothing Set padded_list = Nothing End Sub
VBA events stop working for one user when another user opens the shared workbook
Hi, I’m experiencing a strange issue with VBA in Excel. We use several Excel files as a CRM system, all with VBA (mainly worksheet events). The files are shared so that my employee and I can work on them. Recently, VBA events stopped working only on my computer in some of the files. Important observations: For my employee, everything works normally. Manual macros run correctly on my machine. Worksheet events (like Worksheet_Change) do not fire. Application.EnableEvents returns True. Macro security and Trusted Locations are already configured. The files are almost identical (same structure and VBA code). What makes it even stranger: A file that worked for me yesterday stopped working today immediately after my employee opened it. Since then, the VBA events no longer trigger on my computer in that file, while they still work perfectly for him. So it seems that when another user opens the shared workbook, VBA events stop working only on my machine. Has anyone seen something like this before? Thanks.
Issue printing Multiple spreadsheets at once
I have a file with 7 separate spreadsheets (tabs) and the 1st and last of which use the 2nd tray of my printer (different color paper). the 5 other tabs print off tray 1. I can print each individual tab no issue , but when I try to print all tabs at the same time they come out all out of order, and out of the wrong paper source. any ideas?
Excel Macros Issues
Hi all, A few years ago, a friend wrote a Macros for an excel sheet we use to populate an action plan. The concept is very simple. The Macros goes through the tab and pulls relevent cells through to an another tab creating an action plan. Each item goes under a heading and creates a new line. This has worked like a dream for the last 5 years. Just recently, the macros has started throwing up errors when i run it. Its having issues with Row Height, and doesn't finish running the script, and Excel just hangs. I've replicated this issue on a number of computers, new and old, and tried to swap between Mac and PC. This issue remains the same. The only commonality is the latest version of Excel. Unfortunately rolling back a version is not an option. Have MS updated something that would affect the way this Macros works? Can anyone suggest a solution? Any assistance is appreciated. Many thanks D.Macros open server file hyperlinks in 2nd instance
Macros open server file hyperlinks in 2nd instance, preventing macros from seeing and interacting with the newly opened file. Manual clicking of hyperlink opens the files in the same instance as expected. Users with fresh login have no trouble with the macros opening the files in the same instance. Esoteric macro and/or Microsoft 365 Active Directory problem. Macros using hyperlink in a cell to .follow them to open. Then the next line is a sheet selection of a sheet in the new workbook. Error thrown because the new workbook is not visible to the macro and does not see the sheet name. This works for everyone everywhere. Including on fresh logins. Recently User1 started having the 2nd instance problem. I thought it was isolated and fixed it by removing and recreating his profile. Worked fine for a week. Then it came back. Then User2 logged in on the same machine had the issue. Then the next day User3 on a separate machine had the issue. All 3 users have no issues if they just use a clean login on a different machine. If you manually open all the necessary files, THEN run the macro, it still errors because when it tries to open the already-open-file, it gives the standard Read only Notify me Cancel dialogue. It says [self user] is using it and the file is locked. So is it even a macro problem or is it a server problem? Our server admin says it's not his problem and he has no idea what's happening and it's probably our crappy macros. Yes, our macros are crappy, recorded decades ago. But they work as expected except in these rare but spreading cases, seemingly due to some background environment development.
