so this is on a worksheet, and there are two comboboxes. one of them (the top one) loads its entries from a separate worksheet and within a named range that contains the selections. my problem/question does not pertain to this first box.
the second box, the box directly below the first one, grabs its selections directly from the worksheet that it is on and from a named range titled "Events". This all works fine right now with how it currently exists with no issues.
However, and ideally, I would like the second box's selections to grabbed after the items in the named range have been narrowed down by what was previously selected in the first box...
example:
Here is the worksheet before making any selections (and showing the named range "Events" that the 2nd combo box grabs its selections from):
As an example to explain what I am wanting the 2nd combobox to do, here is the worksheet before selecting the option for "tool box talk monthly training" from the FIRST combobox (the top one.) :
And then here is how it looks after making that particular selection and showing only the 'tool box talk' items and hiding all the other rows: (and the remaining rows is what I need to be populated in the SECOND combobox when it is selected...)
This is where my question/problem comes into play...
Here is the code for the second combobox... but what its doing is loading the selections when the worksheet is activated and from the named range "Events". What I need it to do instead is load the selections when the combobox is clicked (via the drop down button) and only grabbing the visible rows in the named range "Events". how exactly do I do that? Thanks
here is the code for both comboboxes that is executed when the worksheet is activated:
Next, here is my code for the FIRST combobox (the top one)... as I mentioned, this code works fine and no changes are needed:
(pls feel free just scroll down to the bottom if this post to read the code for the next combobox which is what my question concerns... I debated about even including the code for the first combobox in my post since my issue isn't with this first box... but since what I am requesting involves this first box, I decided to include it in my post.)
And lastly, here is my code for the SECOND combobox, which I am needing to modify so that instead of populating the list when the worksheet is first activated (like it does now) and with all the rows in the named range, I need it instead to grab only the VISIBLE rows within the named range and right at the time that the combobox is selected/clicked on.
I should also clarify that I cant have the second combobox to update/populate its list upon whenever a selection is made in the first combobox (dependent upon that selection) because there will be many cases where the first combobox isnt even selected at all. (hence why I need it to grab its list rows whenever its 'clicked'. this will ensure that either possibility- a selection is previously made with the first combobox, or, if the first combobox is ignored entirely and the user goes right to making a selection in only the second comboxbox. Thanks for any help/suggestions!
the second box, the box directly below the first one, grabs its selections directly from the worksheet that it is on and from a named range titled "Events". This all works fine right now with how it currently exists with no issues.
However, and ideally, I would like the second box's selections to grabbed after the items in the named range have been narrowed down by what was previously selected in the first box...
example:
Here is the worksheet before making any selections (and showing the named range "Events" that the 2nd combo box grabs its selections from):
As an example to explain what I am wanting the 2nd combobox to do, here is the worksheet before selecting the option for "tool box talk monthly training" from the FIRST combobox (the top one.) :
And then here is how it looks after making that particular selection and showing only the 'tool box talk' items and hiding all the other rows: (and the remaining rows is what I need to be populated in the SECOND combobox when it is selected...)
This is where my question/problem comes into play...
Here is the code for the second combobox... but what its doing is loading the selections when the worksheet is activated and from the named range "Events". What I need it to do instead is load the selections when the combobox is clicked (via the drop down button) and only grabbing the visible rows in the named range "Events". how exactly do I do that? Thanks
here is the code for both comboboxes that is executed when the worksheet is activated:
VBA Code:
'
Private Sub Worksheet_Activate()
'
Dim cTyp As Range
Dim TE As Worksheet
Set TE = Worksheets("LISTS")
For Each cTyp In TE.Range("TRAIN_TYPE")
With Me.cboTraining
.AddItem cTyp.value
End With
Next cTyp
'
Dim cEve As Range
Dim EE As Worksheet
Set EE = Worksheets("Training Tracker")
For Each cEve In EE.Range("EVENTS")
With Me.cboFN
.AddItem cEve.value
End With
Next cEve
'
End Sub
Next, here is my code for the FIRST combobox (the top one)... as I mentioned, this code works fine and no changes are needed:
(pls feel free just scroll down to the bottom if this post to read the code for the next combobox which is what my question concerns... I debated about even including the code for the first combobox in my post since my issue isn't with this first box... but since what I am requesting involves this first box, I decided to include it in my post.)
VBA Code:
'
Private Sub cboTraining_Change()
'
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
'
ActiveWindow.ScrollColumn = 1
'
Dim rCol As Long
rCol = Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
Dim cCus As Range
Dim DataCriteria As String
'
' CHANGE ALL THE FONT COLORS OF THE NAMES IN THE DUPLICATE NAME COLUMN TO WHITE(to keep them invisible)
'For Each Cell In Range(Cells(1, 12), Cells(1, lCol))
' If Cell.Column Mod 2 = 1 Then
' Cell.Font.Color = &HFFFFFF
' Else
' End If
'Next Cell
'
If cboTraining.value = "Unsort Training Type List" Or cboTraining.value = "Select a Training Type to view ONLY those specific types of Training Events:" Then
lblTR.BackColor = &H800000
Else
lblTR.BackColor = &HC000&
End If
' HIDE ALL THE DATE COLUMN AND ONLY LIST THE NAME COLUMNS
For Each Cell In Range(Cells(12, 2), Cells(rCol, 2))
If Cell = "" Then Columns(Cell.Row).Hidden = True Else Columns(Cell.Column).Hidden = False
Next
'
ActiveWindow.ScrollColumn = 1
Range("E3") = ""
'
' CODE BELOW THIS POINT DEALS THE SPECIFIC ACTIONS FOR THE BUTTON THAT WAS SELECTED:
'
'**********************************************************************
'**********************************************************************
'
If cboTraining.value = "Audit Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*AUDT*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Confined Spaces" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*CONS*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Customer Service Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*CUST*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "DHS Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*DHS*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "DOT Closure Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*DOT*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
'lblReset2.BackColor = &HC0FFC0
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "FDA Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*FDA*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Fire/Emergency Response" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*FIRE*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Fork Lift & Mobile Equipment" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*FORK*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
'lblReset2.BackColor = &H8080FF
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "GMP Awareness" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*GMP*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
'lblReset2.BackColor = 13382655
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "HAZMAT & UN Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*HAZ*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Labeling & Placards" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*LABP*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Laboratory Functions" Then
'
' SHOW ONLY THE EMPLOYEES CONNECTED TO THE DEPARTMENT THAT WAS CLICKED
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*TECH*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Lean 5S & Kaizen Events" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*LEAN*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
'lblReset2.BackColor = &HC0BFF7
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Monthly Safety Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*SAFE*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "OSHA or OSHA related" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*OSHA*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
'lblReset2.BackColor = &HFFFF&
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "PPE/Respirator" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*PPE*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
'lblReset2.BackColor = &H4D4D4D
'lblReset2.BorderStyle = 1
'lblReset2.BorderColor = &HC0FFC0
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Quality Management" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*QMS*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Railcar Tolling & Railcar Processes" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*RAIL*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Sales Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*SALE*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Slip, Trip & Falls" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*FALL*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Spills & Chemical Containment" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*SPIL*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Tool-Box Talk Monthly Training" Then
'
For Each Cell In Range(Cells(12, 1), Cells(rCol, 1))
If Cell Like "*TOOL*" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
Next
'
Range("E3") = "i"
Range("E3") = ""
'
On Error Resume Next
'
End If
'**********************************************************************
'**********************************************************************
'**********************************************************************
If cboTraining.value = "Unsort Training Type List" Then
'
Columns.EntireRow.Hidden = False
'
Range("E3") = ""
'
Dim NCol As Long
NCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
rCol = ActiveSheet.UsedRange.Rows.Count
Dim MCol As Long
MCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
'
'
Dim eCnt As Long
Dim tCnt As Long
tCnt = ActiveSheet.Range(Cells(1, 14), Cells(1, NCol)).SpecialCells(xlCellTypeVisible).Cells.Count
'eCnt = ActiveSheet.Range(Cells(7, 1), Cells(qCol, 1)).Rows.Count / 2
'tCnt = eCnt - 1
lblCount.Caption = tCnt
lblCount.ForeColor = &HFFFFFF
lblCount.Font.Bold = True
lblCountBASE1.BackColor = &H800000
'
lblTraining.Caption = "HIDING"
lblTraining.Font.Size = 9
lblTRAINING_1.Caption = "SHOW"
lblTRAINING_1.Font.Size = 9
'cmdUnhide2_5.Caption = "A"
'cmdShowCodes.Caption = "Show Codes"
'
' UNFORMAT ALL REMAINING FORMATTING TYPES IN BOTH THE COMMANDBUTTONS AND LABELS ON THE WORKSHEET:
'
lblTraining.Caption = "HIDING"
lblTraining.Font.Size = 9
lblTRAINING_1.Caption = "SHOW"
lblTRAINING_1.Font.Size = 9
'
Range(Cells(1, 1), Cells(9, NCol)).Interior.Color = xlNone
Range(Cells(12, 1), Cells(rCol, NCol)).Interior.Color = xlNone
'
Range(Cells(1, 1), Cells(9, NCol)).Font.Color = vbBlack
Range(Cells(12, 1), Cells(rCol, NCol)).Font.Color = vbBlack
'
Range(Cells(1, 1), Cells(9, NCol)).Font.Bold = False
Range(Cells(12, 1), Cells(rCol, NCol)).Font.Bold = False
'
Range(Cells(1, 1), Cells(9, NCol)).Font.Bold = False
Range(Cells(12, 1), Cells(rCol, NCol)).Font.Bold = False
'
Range(Cells(1, 1), Cells(rCol, NCol)).Borders.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, NCol)).Borders.Weight = xlThin
'
With Columns("A:B")
.ColumnWidth = 0.05
End With
With Columns("D")
.ColumnWidth = 0.01
End With
With Columns("E")
.ColumnWidth = 7.57
.Font.Color = &H80&
End With
With Columns("F:M")
.ColumnWidth = 0.01
End With
With Columns("C")
.ColumnWidth = 78.14
End With
'
Range("E3") = ""
'
For Each Cell In Range(Cells(1, 12), Cells(1, MCol))
If Cell.Column Mod 2 = 1 Then
Cell.Font.Color = &HFFFFFF
Else
End If
Next Cell
'
For Each Cell In Range(Cells(4, 14), Cells(4, NCol))
'If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
If Cell = "b" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next
'
For Each Cell In Range(Cells(10, 2), Cells(rCol, 2))
If Cell = "" Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = False
Next
'
Application.CutCopyMode = False
'
ActiveSheet.Rows("2:4").Font.Name = "Lucida Sans Unicode"
ActiveSheet.Rows("2:4").Font.Size = 7
ActiveSheet.Rows("2:7").RowHeight = 0.5
ActiveSheet.Rows("8").RowHeight = 23.75
ActiveSheet.Rows("9").RowHeight = 0.75
'***********************************************************************************************************************************************
ActiveWindow.Zoom = 130
'
lblSH.Caption = "."
lblHH.Caption = "."
Range("E3") = ""
'lblReset2.BackColor = &H8000000F
'lblReset2.BorderStyle = None
'
cboTraining.value = "Unsort Training Type List"
'
With Columns("K:L")
.ColumnWidth = 0.01
End With
'
On Error Resume Next
'
End If
'
'**********************************************************************
' END OF CODE FOR THE SELECTED NAMES; now code is for taking a tally of the names currently shown on the screen.
'**********************************************************************
'
' CALCULATE THE NUMBER OF EMPLOYEES SHOWN ON THE SCREEN AND CHANGE THE LABEL TO REFLECT THE #
'tCnt = ActiveSheet.Range(Cells(1, 14), Cells(1, lCol)).SpecialCells(xlCellTypeVisible).Cells.Count
'lblCount.Caption = tCnt
'lblCount.ForeColor = 5395158
'lblCountBase1.BorderColor = 5395158
'
With Columns("K:L")
.ColumnWidth = 0.01
End With
'
Cells(1, 1).Select
'
ActiveWindow.ScrollColumn = 1
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
'
End Sub
And lastly, here is my code for the SECOND combobox, which I am needing to modify so that instead of populating the list when the worksheet is first activated (like it does now) and with all the rows in the named range, I need it instead to grab only the VISIBLE rows within the named range and right at the time that the combobox is selected/clicked on.
I should also clarify that I cant have the second combobox to update/populate its list upon whenever a selection is made in the first combobox (dependent upon that selection) because there will be many cases where the first combobox isnt even selected at all. (hence why I need it to grab its list rows whenever its 'clicked'. this will ensure that either possibility- a selection is previously made with the first combobox, or, if the first combobox is ignored entirely and the user goes right to making a selection in only the second comboxbox. Thanks for any help/suggestions!
VBA Code:
'
Private Sub cboFN_Change()
'
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Range("E3") = ""
'
If cboFN.value = "Select a Training Event to View the Participants:" Or cboFN.value = "Sort By: Training Event" Then
lblFN.BackColor = &H800000
Else
lblFN.BackColor = &HC000&
End If
'
Dim OCol As Long
OCol = Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
QCol = ActiveSheet.UsedRange.Rows.Count
Dim PCol As Long
PCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
'
lblTraining.Caption = "HIDING"
lblTraining.Font.Size = 9
lblTRAINING_1.Caption = "SHOW"
lblTRAINING_1.Font.Size = 9
'
' UNFORMAT ALL REMAINING FORMATTING TYPES IN BOTH THE COMMANDBUTTONS AND LABELS ON THE WORKSHEET:
lblTraining.Caption = "HIDING"
lblTraining.Font.Size = 9
lblTRAINING_1.Caption = "SHOW"
lblTRAINING_1.Font.Size = 9
'
Range(Cells(1, 1), Cells(9, OCol)).Interior.Color = xlNone
Range(Cells(12, 1), Cells(QCol, OCol)).Interior.Color = xlNone
'
Range(Cells(1, 1), Cells(9, OCol)).Font.Color = vbBlack
Range(Cells(12, 1), Cells(QCol, OCol)).Font.Color = vbBlack
'
Range(Cells(1, 1), Cells(9, OCol)).Font.Bold = False
Range(Cells(12, 1), Cells(QCol, OCol)).Font.Bold = False
'
Range(Cells(1, 1), Cells(9, OCol)).Font.Bold = False
Range(Cells(12, 1), Cells(QCol, OCol)).Font.Bold = False
'
Range(Cells(1, 1), Cells(QCol, OCol)).Borders.Color = vbBlack
Range(Cells(1, 1), Cells(QCol, OCol)).Borders.Weight = xlThin
'
With Columns("A:B")
.ColumnWidth = 0.05
End With
With Columns("D")
.ColumnWidth = 0.01
End With
With Columns("E")
.ColumnWidth = 7.57
.Font.Color = &H80&
End With
With Columns("F:M")
.ColumnWidth = 0.01
End With
With Columns("C")
.ColumnWidth = 78.14
End With
'
Range("E3") = ""
'
For Each Cell In Range(Cells(1, 12), Cells(1, PCol))
If Cell.Column Mod 2 = 1 Then
Cell.Font.Color = &HFFFFFF
Else
End If
Next Cell
'
For Each Cell In Range(Cells(12, 3), Cells(QCol, 3))
If Cell Like cboFN.value Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True
If Cell Like cboFN.value Then Range(Cells(Cell.Row, 1), Cells(Cell.Row, PCol)).Interior.Color = 6566400
If Cell Like cboFN.value Then Range(Cells(Cell.Row, 1), Cells(Cell.Row, PCol)).Font.Color = vbWhite
If Cell Like cboFN.value Then Range(Cells(Cell.Row, 1), Cells(Cell.Row, PCol)).Font.Bold = True
If Cell Like cboFN.value Then Range(Cells(Cell.Row, 1), Cells(Cell.Row, PCol)).Borders.Weight = xlThin
If Cell Like cboFN.value Then Range(Cells(Cell.Row, 1), Cells(Cell.Row, PCol)).Borders.Color = vbWhite
Next
'
If cboFN.value = "Select a Training Event to View the Participants:" Then lblMissedAttendOVERLAY.Visible = True Else lblMissedAttendOVERLAY.Visible = False
If cboFN.value = "Select a Training Event to View the Participants:" Then lblMissedOVERLAY.Visible = True Else lblMissedOVERLAY.Visible = False
If cboFN.value = "Select a Training Event to View the Participants:" Then lblAttendOVERLAY.Visible = True Else lblAttendOVERLAY.Visible = False
'
On Error Resume Next
'
Dim eCnt As Long
Dim tCnt As Long
Dim kKol As Long
kKol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
' CALCULATE THE NUMBER OF EMPLOYEES SHOWN ON THE SCREEN AND CHANGE THE LABEL TO REFLECT THE #
tCnt = ActiveSheet.Range(Cells(1, 14), Cells(1, kKol)).SpecialCells(xlCellTypeVisible).Cells.Count
lblCount.Caption = tCnt
lblCount.ForeColor = &HFFFFFF
lblCount.Font.Bold = True
lblCountBASE1.BackColor = &H800000
'
Application.CutCopyMode = False
'
ActiveSheet.Rows("2:4").Font.Name = "Lucida Sans Unicode"
ActiveSheet.Rows("2:4").Font.Size = 7
ActiveSheet.Rows("2:7").RowHeight = 0.5
ActiveSheet.Rows("8").RowHeight = 23.75
ActiveSheet.Rows("9").RowHeight = 0.75
'***********************************************************************************************************************************************
ActiveWindow.Zoom = 130
'
lblSH.Caption = "."
lblHH.Caption = "."
Range("E3") = ""
'
If cboFN.value = "Select a Training Event to View the Participants:" Then Columns.EntireRow.Hidden = False
'
With Columns("K:L")
.ColumnWidth = 0.01
End With
'
On Error Resume Next
'
Dim wCnt As Long 'eCnt
Dim uCnt As Long 'tCnt
Dim jJol As Long 'kKol
jJol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
' CALCULATE THE NUMBER OF EMPLOYEES SHOWN ON THE SCREEN AND CHANGE THE LABEL TO REFLECT THE #
uCnt = ActiveSheet.Range(Cells(1, 14), Cells(1, jJol)).SpecialCells(xlCellTypeVisible).Cells.Count
lblCount.Caption = uCnt
lblCount.ForeColor = &HFFFFFF
lblCount.Font.Bold = True
lblCountBASE1.BackColor = &H800000
'
ActiveWindow.ScrollColumn = 1
'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
'
End Sub
Attachments
Last edited: