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

 

[Trevor3007]

Hi Joyner,

Thank you for your input. It would better (for me) to have VB as the particular sheet is currently 200 rows long at present & potentially will grow.

KR
Trevor

 

[Joe4]

Not sure if you missed my reply back in post 18, in which there are some potential conflicts/issues that we need clarification from you on.

 

[Trevor3007]

[h=2]

Re: message if more than 1 box has y[/h]

Hi Joe4,

Think I may have replied to another 'Mr Excel' moderator in error.


In answer to your question:-




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 ​

​

 

[Trevor3007]

Hi Joe4,

yes it looks like I have, could you resend?
KR
Trevor3007

 

[Joe4]

Hi Joe4,

Think I may have replied to another 'Mr Excel' moderator in error.


In answer to your question:-

I already replied to that with follow-up questions. That is what I need your feedback on.
As I said, it is post 18 in this thread (if you look at the top of each post on the right-hand side, there is a post number).
Here is a link to that exact post I need your answers on: https://www.mrexcel.com/forum/excel...e-if-more-than-1-box-has-y-2.html#post5102101

 

[Trevor3007]

Thanks Joe4,

you must have some patience with peeps like me.

What implications should that have for column B?

Col B puts the date, when col G has a Y in ( if this was now, IE "Y" in Col G would place the date 09/07/18) this date will not change the next day unless Y is removed & then re entered.

If you can resolve both , then you would be a VB god!!
​

 

[Joe4]

You still haven't answered my question.

With this thread, you want to prevent "y" from appearing more than once in columns E, F, and G in any single row, right?
Let's walk through a simple example. What if a "y" already exists in cell E2, and then someone tries to put a "y" in cell G2.
Your old code is telling it to put a date in column B2.
But the new code you want is telling it NOT to allow an entry of "y" in G2, because a "y" already exists in E2.
So who wins in this case?
What should happen to B2 in this instance?

We need to have a full understanding of how you want these different conditions to work together, and what takes precedence.

 

[Trevor3007]

I do my very best to include all.

ok, I will try to give you a better idea.


There (ATM) are 200 rows but at any one time any of the rows (cols E:G) will either be blank or have one "Y". Column G is only used to verify that a particular task in the that row has completed ( no other "Y"s should be in the E2:G2 section) cell G2 when a "Y" is placed puts the date (IE 09/07/18) in cell B2

Every "Y" will generate a 1 in another column. (formula IE "=if(g2>0,1,"") all these 1s are counted and a totals thereof are displayed on another worksheet.

I do hope this helps. I thank you for all your assistance/help.

KR
Trevor3007

 

[Trevor3007]

Joe4,

sorry I just noticed that G2 also impact D2, which will change D2 to 'Task Complete' as well as the date being triggered in B2. D2 has the formula :-

=IF(G2>0,"Task Complete",IF(E2>0,"BIP",IF(F2="Y","Added","Send Report")))

 

[Joe4]

There (ATM) are 200 rows but at any one time any of the rows (cols E:G) will either be blank or have one "Y". Column G is only used to verify that a particular task in the that row has completed ( no other "Y"s should be in the E2:G2 section) cell G2 when a "Y" is placed puts the date (IE 09/07/18) in cell B2

You still have not addressed the situation I brought up in my previous post.
Until you answer address that, it is not possible for me to come up with code (so I do not know what it should do in that situation).
When coding, you need to consider all the possible scenarios, and determine exactly what needs to happen in the different scenarios.