message if more than 1 box has y

[Trevor3007]

Good evening,

I have a issue which I hope someone can solve for me .

Cells E2 - G2, when a certain task is done, the user puts a "Y" into the applicable. However some users are putting in more than 1 Y into and this gives the wrong count.

Is it possible to have it so that only Y can be put in & if a 2nd Y is put in the same row, a message appears stating that only one Y is allowed in that row?

I cannot use 'conditional formatting'


Thank you for your time today & i do hope someone is up for the challenge.

KR
Trevor3007

 

[Joe4]

I cannot use 'conditional formatting'

Why not?

The only other option I can think of is VBA, and usually people are even more reluctant to use VBA than Conditional Formatting (security concerns, limitations, etc).

Cells E2 - G2, when a certain task is done, the user puts a "Y" into the applicable. However some users are putting in more than 1 Y into and this gives the wrong count.

Another option may be to change your count formula instead. If you explain your data structure and what it is you are trying to count, we may be able to help with that.

 

[Trevor3007]

Thanks Joe4,

I am more than happy to have VB sort. As for con form..this is not an option as the range is from e2:g200 and although the user should on have 1 y in the e2:g2 row, they can put another y in the row below (e3:g3)

example

man woman pet transport
Y Y

Y
y
y

so the red is wrong (cells e2 & f2)

green is correct (cells e3,f4 & g5)

hope this helps & thanks you

 

[Joe4]

As for con form..this is not an option as the range is from e2:g200 and although the user should on have 1 y in the e2:g2 row, they can put another y in the row below (e3:g3)

This actually can be accommodated with conditional formatting quite easily.
Just follow these steps:

1. Select the range E2:G200
2. Enter the following Conditional Formatting formula:
=COUNTIF($E2:$G2,"Y")>1
3. Choose your desired formatting option

As you will see, each row will be handled independently of each other, and if there are more than one "Y" in that row, it will be formatted.

Of course, that will only "highlight" your row, and won't prevent the entry or return a Message Box.
If that is not sufficient, and you would like a VBA approach that prevents that with a message, let me know.

 

[Trevor3007]

thanks Joe

VB would be preferred please. The range is E2:G2 BTW

 

[Trevor3007]

...with message box too(sorry)

 

[Joe4]

VB would be preferred please. The range isE2:G2 BTW

this is not an option as the range is from e2:g200

OK, give me a few minutes and I will whip it up for you.

But I just wanted to note above, as I think you may be confused as to what you can do with Conditional Formatting.
Basically, you only want to look at columns E:G in rows 2:200, and only want to allow one "Y" per row, right?
That is what my Conditional Formatting instructions will do.
By highlighting E2:G200, we are adding the conditions to all 200 rows at once, but rest assured, it is applying the conditions to each respective row independently (try it and you will see!).

Just want to make sure you understand the full power of Conditional Formatting!
You can even format cells based on values in other cells!

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen (of the sheet you want to apply this code to), select "View Code", and paste this VBA code in the resulting VB Editor window.
It should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    Dim r As Long
    
    Set rng = Intersect(Target, Range("E2:G200"))
    
'   Exit sub if no cells updated in range
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in range
    For Each cell In rng
'       Get row number of updated cell
        r = Target.Row
'       Count how many cells have "Y" in current row
        Set rng2 = Range("E" & r & ":G" & r)
        If Application.WorksheetFunction.CountIf(rng2, "Y") > 1 Then
'           Clear entry
            Application.EnableEvents = False
            cell.ClearContents
            Application.EnableEvents = True
'           Return message
            MsgBox "You cannot enter a Y in cell " & cell.Address(0, 0), vbOKOnly, "ERROR!"
        End If
    Next cell
    
End Sub

 

[Trevor3007]

Hi Joe,


Followed you instructions, but comes back with Compile Error: Ambiguous name detected worksheet_change

 

[mikerickson]

I would use Data Validation instead of Conditional Formatting, since it includes an error message.

The formula =COUNTIF($E2:$G2,"y")<2 should work.