Ok... so this code ran perfectly fine before i moved part of the code over to a separate module (prior to this all the code was on the main sheet.... except for some code that formatted it upon opening which is on the workbook part.)
Here is the old code that ran normally and not slow at all
(skip all this if you want... scroll down to the new code to see if something jumps out at you that would be slowing it down.)
Information on the spreadsheet and what it does: There are a total of 19 command buttons with the same code except for formatting differences and ranges that list the data relevant to that department (which each button represents.)
The spreadsheet looks like upon opening and before selecting one of the departments (commandbuttons) in the top left corner:
After selecting one of the departments, it drills down to only display the employees listed in that department, and the corresponding SOPs required for that department (and an 'X' to represent if they have been trained for that particular SOP)
Here is what it displays after selecting the 'Oil Field' button
I've been working on updating the spreadsheet so that it can differentiate between the employees that work at 2 separate facilities (prior to this I lsited the 2 different facilities on 2 different tabs on the workbook).
The first thing I did was add a button that showed only employees from one location or the other location, or all combined. (I needed to do this so that employees from one location do not get mixed in with the employees from the other location when selecting a department.)
So with the help of several of the members here figuring out the code for me, it now works just the way I need it to.... BUT... its UNGODLY slow. Today I have been moving things around and eliminating this or that in order to identify where the section is that is responsible for slowing it down, but I haven't had any luck.... (so here i am here once again.
)
Here is the new code that is running S - U - P - E - R S - L - O - W
First here is the code for the button that drills down to only show either employees from one facility (rosenberg) or the other facility (el campo) (hitting top 'reset' button shows all employees)
Based on the selection of the button, either Macro3 or Macro4 is exectued (which both macros are essentially the same except for names and ranges changed)... here is Macro4 which narrows down the employees to show only that work at el campo (identified by having an "E" in row 5):
Next after the employees are narrowed down to one facility (El campo; "E" in this case), the user can select any of the 19 department buttons to show the details for the employees shown for that department (I have only done 1 department so far just to demonstrate that it works the way that I need it to... which it does)
here is the code for the 'Powders' department. This is on the main worksheet page. Here it is looking to see what employees are VISIBLE on the worksheet (based on what was shown or hidden on the previous selection/steps) In this case only the El Campo employees are shown. Based on what it sees its either going to directed to 1 of 3 macros. One for Powders for El Campo, one for Powders for Rosenberg and another one for all employees:
And finally the code for Macro10, Powders for El Campo employees:
If you've managed to read all of this, thank you very much... lol... now what can i do to speed this up??
Here is the old code that ran normally and not slow at all
Code:
Private Sub CommandButton4_Click()
[COLOR=#008000] ' button for BUILDING 8[/COLOR]
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveWindow.ScrollColumn = 1
Dim lCol As Long
lCol = ActiveSheet.UsedRange.Columns.Count
rCol = ActiveSheet.UsedRange.Rows.Count
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next
Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(10, 3), Cells(rCol, 3)).Font.Color = vbWhite
With Rows("2:5")
.Hidden = False
End With
With Columns("B")
.Hidden = False
End With
With Columns("A")
.Hidden = True
End With
For Each Cell In Range(Cells(3, 6), Cells(3, lCol))
If Cell Like "*B8*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
Next
For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
If Cell Like "*B8*" Then
.Interior.Color = 13382655
.Font.Color = 6750207
.Font.Bold = True
.Borders.Weight = xlMedium
Else
.Font.Color = 10213316
Rows(Cell.Row).RowHeight = 14
.Borders.Weight = xlHairline
End If
End With
Next
Range("B6").Font.Color = &HFF&
Range("B6").Font.Bold = True
Range("B6").Borders.Weight = xlMedium
Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin
For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Mura, Mark" Then Cell.Interior.Color = 13382655
If Cell = "Mura, Mark" Then Cell.Font.Color = 6750207
If Cell = "Mura, Mark" Then Cell.Font.Bold = True
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub(skip all this if you want... scroll down to the new code to see if something jumps out at you that would be slowing it down.)
Information on the spreadsheet and what it does: There are a total of 19 command buttons with the same code except for formatting differences and ranges that list the data relevant to that department (which each button represents.)
The spreadsheet looks like upon opening and before selecting one of the departments (commandbuttons) in the top left corner:
After selecting one of the departments, it drills down to only display the employees listed in that department, and the corresponding SOPs required for that department (and an 'X' to represent if they have been trained for that particular SOP)
Here is what it displays after selecting the 'Oil Field' button
I've been working on updating the spreadsheet so that it can differentiate between the employees that work at 2 separate facilities (prior to this I lsited the 2 different facilities on 2 different tabs on the workbook).
The first thing I did was add a button that showed only employees from one location or the other location, or all combined. (I needed to do this so that employees from one location do not get mixed in with the employees from the other location when selecting a department.)
So with the help of several of the members here figuring out the code for me, it now works just the way I need it to.... BUT... its UNGODLY slow. Today I have been moving things around and eliminating this or that in order to identify where the section is that is responsible for slowing it down, but I haven't had any luck.... (so here i am here once again.
)Here is the new code that is running S - U - P - E - R S - L - O - W
First here is the code for the button that drills down to only show either employees from one facility (rosenberg) or the other facility (el campo) (hitting top 'reset' button shows all employees)
Rich (BB code):
Private Sub cmdRosenbergElCampo_Click()
' button for showing(toggleing) between employees from Rosenberg or El Campo
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
If cmdRosenbergElCampo.Caption = "EL-CAMPO" Then
Call Macro3
Else
Call Macro4
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubBased on the selection of the button, either Macro3 or Macro4 is exectued (which both macros are essentially the same except for names and ranges changed)... here is Macro4 which narrows down the employees to show only that work at el campo (identified by having an "E" in row 5):
Rich (BB code):
Sub Macro4()
' button for EL CAMPO EMPLOYEES
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveWindow.ScrollColumn = 1
Dim lCol As Long
lCol = ActiveSheet.UsedRange.Columns.Count
rCol = ActiveSheet.UsedRange.Rows.Count
For Each Cell In Range(Cells(5, 6), Cells(5, lCol))
If Cell = "E" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
Next
Range(Cells(1, 2), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 2), Cells(rCol, lCol)).Borders.Weight = xlThin
Range("B6").Font.Color = &HFF&
Range("B6").Font.Bold = True
Range("B6").Borders.Weight = xlMedium
Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubNext after the employees are narrowed down to one facility (El campo; "E" in this case), the user can select any of the 19 department buttons to show the details for the employees shown for that department (I have only done 1 department so far just to demonstrate that it works the way that I need it to... which it does)
here is the code for the 'Powders' department. This is on the main worksheet page. Here it is looking to see what employees are VISIBLE on the worksheet (based on what was shown or hidden on the previous selection/steps) In this case only the El Campo employees are shown. Based on what it sees its either going to directed to 1 of 3 macros. One for Powders for El Campo, one for Powders for Rosenberg and another one for all employees:
Rich (BB code):
Private Sub CommandButton3_Click()
' Powders Department
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveWindow.ScrollColumn = 1
Dim cP As Range
Dim lCol As Long
lCol = ActiveSheet.UsedRange.Columns.Count
E = 0
r = 0
For Each cP In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)
If cP.Value = "E" Then
Call Macro10 ' EL CAMPO Macro10()
E = 1
ElseIf cP.Value = "R" Then
Call Macro11 ' ROSENBERG Macro11()
r = 1
End If
Next cP
If E + r = 2 Then Call Macro12 'BOTH FACILITIES Macro12()
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubAnd finally the code for Macro10, Powders for El Campo employees:
Rich (BB code):
Sub Macro10()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
' button for El Campo employees and the POWDERS department
Worksheets("Current").Activate
Dim lCol As Long
lCol = ActiveSheet.UsedRange.Columns.Count
rCol = ActiveSheet.UsedRange.Rows.Count
For Each Cell In Range(Cells(4, 6), Cells(4, lCol)).SpecialCells(xlCellTypeVisible)
Columns(Cell.Column).Hidden = IIf(Cell.Value = "a" And Cell.Offset(1).Value = "E", True, False)
Next
With Rows("2:5")
.Hidden = False
End With
ActiveSheet.Rows("2:5").RowHeight = 1
ActiveSheet.Rows("2:5").Font.Size = 1
With Columns("B")
.Hidden = False
End With
With Columns("A")
.Hidden = True
End With
For Each Cell In Range(Cells(3, 6), Cells(3, lCol)).SpecialCells(xlCellTypeVisible)
If Cell Like "*Po*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
Next
For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
If Cell Like "*Po*" Then
.Interior.Color = 12648447
.Font.Bold = True
.Borders.Weight = xlMedium
Else
.Font.Color = 10213316
Rows(Cell.Row).RowHeight = 14
.Borders.Weight = xlHairline
End If
End With
Next
Range("B6").Font.Color = &HFF&
Range("B6").Font.Bold = True
Range("B6").Borders.Weight = xlMedium
Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin
For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Boone, Dan" Then Cell.Interior.Color = 12648447
If Cell = "Boone, Dan" Then Cell.Font.Bold = True
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubIf you've managed to read all of this, thank you very much... lol... now what can i do to speed this up??
