Userform Textboxs to count cells with a specific coloured backgrounds

A

AGrace

Board Regular
Joined
Jun 29, 2010
Messages
150
Morning all,

I have a holiday planner (that is functioning nicely thanks to help from MickG) that displays employee holiday/sickness absence throughout the year (indicated by specific colours).

I have a userform that I want to count the number of these specific colours per employee.

I have a userform that looks like this:

Absence.png


When the user selects an employee from the combobox (all comboboxes, labels etc have default names), the textboxes will count the specific colours for each absence type filled out for that employee.

The colour codes used are :

Code: 
    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37

Planner.png


The range for the count is column C to column NC, and row's 6 through to 20 (but then the row should be selected by choosing the employee from the drop down).

What I'm stuck with is how to count the colours in the cells for each employee. Any help is appreciated!

Thanks,

Adam
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Sorted with this.

Code: 
Private Sub ComboBox1_Change()
    Dim lngRow As Long
    Dim rngData As Range
    If Me.ComboBox1.ListIndex <> -1 Then
        ' get row number - employee range starts in row 6
        lngRow = 6 + Me.ComboBox1.ListIndex
        With Sheet1
            Set rngData = .Range(.Cells(lngRow, "C"), .Cells(lngRow, "NC"))
        End With
        Me.TextBox1.Value = CountByColor(rngData, Sheet1.Range("B22")) ' holiday
        Me.TextBox2.Value = CountByColor(rngData, Sheet1.Range("B23")) ' sick leave
        Me.TextBox3.Value = CountByColor(rngData, Sheet1.Range("B24")) ' other
    End If
End Sub
Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then
            TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing
    CountByColor = TempCount
End Function

credit to romperstomper from ExcelForums! (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA to edit/update cells within a large table from a common "Dashboard"
Replies
1
Views
129
brianv
B
H
  • Solved
Based on calculation of days then divided by the textbox value 6
Replies
1
Views
417
hsolanki
H
C
  • Question
Generic Code To Highlight And Copy Any Selection Of Text On Any Textbox On Any Userform In An Application
Replies
0
Views
467
chazrab
C
S
  • Solved
VBA code for userform , to add data to specific cells based on combolistbox selection
Replies
6
Views
652
sloany101
S
K
  • Solved
trouble with locating a specific cell location (using offset?) for using 'If' / 'And' statement..
Replies
2
Views
433
kbishop94
K

Forum statistics

Threads
1,215,982
Messages
6,128,105
Members
449,421
Latest member
AussieHobbo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top