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

 

[Rick Rothstein]

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

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

The only problem with using Data Validation is that it can be defeated by copy/pasting improper values into the cells.

 

[Trevor3007]

Hi & good evening,


Thank you for your input . Yes that would work, but there are 200 rows & would take some time to input. If, however you have a faster method I would be must grateful.

KR
Trevor

 

[Rick Rothstein]

Thank you for your input . Yes that would work, but there are 200 rows & would take some time to input. If, however you have a faster method I would be must grateful.

When there are multiple people responding to your question, we cannot tell who your reply is meant for unless you specifically mention that responder's name and message number.

 

[Joe4]

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

That means that you already have a "Worksheet_Change" event procedure in that sheet!
You cannot have two procedures in the same module with the same name (and Worksheet_Change event procedure MUST be named a certain way to work automatically), so we would need to combine the two of them together in one procedure.

If you do not feel comfortable doing this yourself, please post the VBA code from your other Worksheet_Change event procedure.

 

[Trevor3007]

Rick Rothstein ,

Many thanks for your emails & my apologies for not including the applicable 'reply' name.


KR
Trevor3007

 

[Trevor3007]

Hi Joe4,

yes there is other code & below is currently what is there:-


Private Sub Worksheet_Change(ByVal Target As Range)


ActiveSheet.Unprotect




Dim Changed AsRange, c As Range

Set Changed =Intersect(Target, Columns("G"), Rows("2:" &Rows.Count))
If Not Changed IsNothing Then
Application.EnableEvents = False
For Each c InChanged
IfLCase(c.Value) = "y" Then
WithRange("B" & c.Row)
.Value = CDate(Date)
.NumberFormat = "dd/mm/yyyy"
End With
Else
Range("B" & c.Row).ClearContents
End If
Next c
Application.EnableEvents = True
End If


ActiveSheet.ProtectDrawingObjects:=False, Contents:=True, Scenarios:= _
False,AllowFormattingCells:=True
End Sub

Hoping you can solve.
KR
Trevor3007

 

[Rick Rothstein]

Rick Rothstein ,

Many thanks for your emails & my apologies for not including the applicable 'reply' name.

So, which message was Message #12 meant as a reply to?

 

[Joe4]

I see a potential data collision between your two codes.
It looks like that other code is looking for the value of "y" to be placed in column G, and if it is, then to update column B of that row with the current date.
But what if column E or F already has a "y" in it, and then you try to add a "y" to column G?
The code you are asking for in this post would prevent column G from updating. What implications should that have for column B?

 

[Trevor3007]

Hello Rick Rothstein,


Again my apologies. I think it was you? I will ensure that in all future corro that the applicable is mention IE '

Rick Rothstein'


KR
Trevor3007
​

 

[Joyner]

thanks Joe

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

Hello, just my thoughts,
But depending on your application and how much you want tocontrol what is entered, you may want to consider more than one solution sincewith VBA for example, it will only work if the user actually enables content.
I have used VBA with password protected sheets and lockedcells, etc. to try to force the user to enable content in conjunction with a DataValidation and conditional formatting solutions.