Excel Magic needed Please read!

I have a Excel Sheet which have ROWS for Employee #1 to # 12 for each given day(Column), once 4 A's are chosen for a Day the Little Combo box for input on each cell needs to disappear for that Column because somewhere between Employee #1 and #12         4 A's have been reached No Higher then 4 A's before the Column gets Locked and Combo box disappears Total must be 4 and 4 A's within those Column which represent a single day. 5 A's are Too Many and 3 A's are 1 shy You guys are the best Help!
I need one column for each day to have 4 A's after Four A's have been chosen the drop down Selection disappeared on that Day's Column.Beta-Schedule.xlsx
powerztomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Do you mean in the range D9:BC27, each column can have max 4 A's selected and user shouldn't be allowed to select the 5th A in a column if there are already 4 A's exists in that column?
e.g. in the range D9:D27, there are already 4 A's in there so user shouldn't be allowed to select another A in this range. Is that correct?
0
powerztomAuthor Commented:
Yes You understand it correctly D9:BC27 is the Range.
Make Combo boxes stop dropping down if 4 A's are chosen
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please place the following code on Schedule Sheet module. To do so, right click the Schedule Sheet Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

I have already placed the following code on Schedule Sheet Module in the attached.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim rng As Range
Dim col As Long
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("D9:BC27")) Is Nothing Then
    If Target <> "" Then
        col = Target.Column
        Set rng = Range(Cells(9, col), Cells(27, col))
        If Application.CountIf(rng, "A") > 4 Then
            MsgBox "More than 4 A's are NOT Allowed for a day.", vbExclamation
            Application.Undo
        End If
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

Open in new window

Beta-Schedule.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
powerztomAuthor Commented:
Thank You so Much I work in IT and there are many things I can do one thing I have to ask for help  is EXCEL SOME OF THESE Functions  and VBA Code is mind blowing Thank You Sir for a job well done.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it worked as desired.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.