So although Excel not being made to create programs in, I'm completing one. Rest of the workbook works great but facing an issue with a sub which I'm yet to find anyone else having: (Images at the bottom)
https://i.stack.imgur.com/bWoEc.png https://i.stack.imgur.com/4pGQN.png
The sub will look at sheet 1 where it will fill sheet 2 with data accordingly, then sheet 3 will take that data and analyse it and present it nicely to the user who ran it. The error occurs mid macro where it pulls bits of each sheet referenced and graphically merges them. Once the macro finishes it remains broken until you go onto sheet 2 then back to 3, for the users this isn't really what I wanted.
Unsure if this is a limiation of Excel or if there is a method to force excel to dedicate some memory to keeping sheet 3 intact?
Macro is fully below: Please note where I am selecting sheets is where I was troubleshooting
'This Sub will help calculate the % of Role Recomeneded Training. It will decide if the training for selected user is complete
Sub CalculateTrainingAnaltics()
'-------------------------------------Role Based Training Analysis-------------------------------------
'Unprotect Sheets to allow for calculations and changes
Sheets("UserAnalysis").Unprotect
Sheets("TrainingMatrixRe-Work").Unprotect
Sheets("ManageUsers").Unprotect
'Declare global Variables
Dim userToCal As String
Dim rowToCal As String
Dim roleNeeded As String
Dim cellToCheckC As String
Dim holdCellNumber As String 'Temp Var meant to be over written
Dim calcol As String 'Column to place findings into
Dim progressBar As Integer
'Reset Progress Bar
Sheets("UserAnalysis").Range("B30") = progressBar
Sheets("UserAnalysis").Range("B31") = progressBar
'Go Back To orignal Sheet and reset graphical issues
Sheets("Dashboard").Select
MsgBox "Calculation now starting"
Sheets("ManageUsers").Select
userToCal = Range("D5")
'MsgBox userToCal & " Column to Calculate" 'Debug line
'Loops to compare all training
For i = 0 To 298
progressBar = i
Sheets("UserAnalysis").Range("B30") = progressBar
holdCellNumber = "" ' Clear before every use
cellToCheckC = "" ' Clear before every use
holdCellNumber = i + 9
cellToCheckC = "C" + holdCellNumber
'MsgBox cellToCheckC 'Debug Message
roleNeeded = Sheets("UserAnalysis").Range(cellToCheckC)
'Go Back To orignal Sheet and reset graphical issues
If i = 50 Then
Sheets("Dashboard").Select
Sheets("ManageUsers").Select
End If
If roleNeeded = 1 Then
holdCellNumber = "" ' Clear before every use
userToCal = Sheets("UserAnalysis").Range("D5") ' Re-applies before every use
holdCellNumber = i + 5
userToCal = userToCal + holdCellNumber
If Sheets("TrainingMatrixRe-Work").Range(userToCal) = "" Then
calcol = "" ' Clear before every use
holdCellNumber = "" ' Clear before every use
holdCellNumber = i + 9
calcol = "D" + holdCellNumber
Sheets("UserAnalysis").Range(calcol) = "Incomplete"
Else
calcol = "" ' Clear before every use
holdCellNumber = "" ' Clear before every use
holdCellNumber = i + 9
calcol = "D" + holdCellNumber
Sheets("UserAnalysis").Range(calcol) = "Completed"
End If
Else
calcol = "" ' Clear before every use
holdCellNumber = "" ' Clear before every use
holdCellNumber = i + 9
calcol = "D" + holdCellNumber
Sheets("UserAnalysis").Range(calcol) = "Not Needed"
End If
Next
'-------------------------------------Training Compliance-------------------------------------------------------------------
'Go Back To orignal Sheet and reset graphical issues
Sheets("Dashboard").Select
Sheets("ManageUsers").Select
Dim placeCalcu As String
Dim rowToPlace As String
For i = 0 To 298
progressBar = i
Sheets("UserAnalysis").Range("B31") = progressBar
'Go Back To orignal Sheet and reset graphical issues
If i = 50 Then
Sheets("Dashboard").Select
Sheets("ManageUsers").Select
End If
holdCellNumber = "" ' Clear before every use
userToCal = ""
userToCal = Sheets("UserAnalysis").Range("E5") ' Re-applies before every use
holdCellNumber = i + 603
userToCal = userToCal + holdCellNumber
'MsgBox userToCal
If Sheets("TM-TimeFormatting").Range(userToCal) = True Then
'MsgBox "Checking OUT OF DATE"
rowToPlace = ""
placeCalcu = "E"
rowToPlace = i + 9
placeCalcu = placeCalcu + rowToPlace
'MsgBox placeCalcu 'Debug Line
Sheets("UserAnalysis").Range(placeCalcu) = "Out Of Date"
Else
'Now we'll pull if the data has expired or near EOL or 3 Months or More Left
holdCellNumber = "" ' Clear before every use
userToCal = Sheets("UserAnalysis").Range("E5") ' Re-applies before every use
holdCellNumber = i + 304
userToCal = userToCal + holdCellNumber
'Test for if the training is out of date
If Sheets("TM-TimeFormatting").Range(userToCal) = True Then
placeCalcu = "E"
rowToPlace = i + 9
placeCalcu = placeCalcu + rowToPlace
'MsgBox ("Checking") 'Debug Line
Sheets("UserAnalysis").Range(placeCalcu) = "Near EOL"
Else
holdCellNumber = "" ' Clear before every use
userToCal = ""
userToCal = Sheets("UserAnalysis").Range("E5") ' Re-applies before every use
holdCellNumber = i + 902
userToCal = userToCal + holdCellNumber
If Sheets("TM-TimeFormatting").Range(userToCal) = True Then
rowToPlace = ""
placeCalcu = "E"
rowToPlace = i + 9
placeCalcu = placeCalcu + rowToPlace
'MsgBox placeCalcu 'Debug Line
Sheets("UserAnalysis").Range(placeCalcu) = "Compliant"
Else
If Sheets("TrainingMatrixRe-Work").Range(userToCal) = 0 Then
'Where to place not complete
placeCalcu = "E"
rowToPlace = i + 9
placeCalcu = placeCalcu + rowToPlace
'If its not then apply Not completed
Sheets("UserAnalysis").Range(placeCalcu) = "Not Completed"
End If
End If
End If
End If
Next
'-----------------------------------------------------------------------------------------------------------
'Refresh the pivot table and pie chart
Dim Table As PivotCache
For Each Table In ThisWorkbook.PivotCaches
Table.Refresh
Next Table
'Go Back To orignal Sheet and reset graphical issues
Sheets("Dashboard").Select
Sheets("ManageUsers").Select
'Sheets("Dashboard").Select
'Complete Msg
MsgBox "Calculation Complete - Please see information :)"
'Sheets("ManageUsers").Select
'Reprotect All of Working Sheets
Sheets("UserAnalysis").Protect
Sheets("TrainingMatrixRe-Work").Protect
Sheets("ManageUsers").Protect
End SUB
