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]

good afternoon Joe4,

thank you for your patience, assistance and ongoing support.


The whole none yards is to complete several tasks and indicate when each task is in progress. This is so if a manager enquires what's the current state? I can then show the totals (IE how many have turned up or not( (totals displayed/located on another sheet) . so really its just for statistics .

Some may ask 'is it worth it' but it would save so much time in the long run, so for me 'YES' its worth it.

OK.... so here is another attempted to explain what I require.

Cols E-F , when Y is entered, it triggers a notification in Col D. Once the task in Col E is completed, the Y is removed and Y placed into Col F, this then triggers the date in Col B.

For every Y entered this triggers a 1 retrospectively into a hidden col. All applicable columns then count the 1's and a total for each applicable column is captured on another worksheet under their applicable titles.

There will only ever be one " Y" in each row . But the position of the Y will depend on the task in progress.


KR
Trevor3007

 

[Joe4]

This was your original request:

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?

So, it sound like this original request was to prevent the user from adding a "y" in columns E, F, or G if a "y" already appears in one of those other columns in the same row.
Is that correct? Has this requirement changed?

It sounds like in addition to this, you want certain things to happen in certain columns when a "y" is added. So let's just simplify it like this:
- When a "y" is placed in column E, perform action "A"
- When a "y" is placed in column F, perform action "B"
- When a "y" is placed in column G, perform action "C"

So here is the big question that I have been trying to ask, and need you to answer.
What if a "y" already exists in one of the columns, and they try to add a "y" in one of the other columns in that same row?
Based on your original requirement, it sounds like we should prevent this and return a Message Box saying that they cannot do this (yet).
If that is the case, then the succeeding related action should not happen, correct? That is what I need you to confirm.

Walking through our example above, let's say F2 has a "y" in it, and they try to place a "y" in column G2.
According to your original requirements, we should not allow this to happen, so we will erase the "y" they just entered from G2 and return the message box.
So, does that mean in this instance, action "C" should NOT be performed, since we are not allowing the "y" in G2 at this time?

 

[Trevor3007]

Hello Joe$,

thanks again for your swift reply.

the only columns that will trigger are as follows:-

- When a "y" is placed in column E, perform action "D"
- When a "y" is placed in column F, perform action "D"
- When a "y" is placed in column G, perform action "B" (date displayed...The code I sent you prevents the date changing when the 'work book' is opened after the last saved , IE , Y is placed onto G2 and date is 10/7/18 in B2. I open the file 12/7/18 and the date will still be 10/7/18. If I continue adding new data on any of the other rows( IE B77) G77 with a Y would place the date 12/7/18 into B77

​

What if a "y" already exists in one of the columns, and they try to add a "y" in one of the other columns in that same row?​

​

There should only ever be one Y in a row at one time , should another Y appear this would be incorrect & in turn would 'throw' the totals off. IE a Y in E&F would = 2 , when in fact it should be 1.
​

Based on your original requirement, it sounds like we should prevent this and return a Message Box saying that they cannot do this (yet).
If that is the case, then the succeeding related action should not happen, correct? That is what I need you to confirm.​

​

_{^{<strike>
</strike>}}​

Yes your are correct.



Col A&C are not impacted at this stage & preform another function not linked to the above.


Many thanks again & fingers crossed.


KR
Trevor3007

 

[Joe4]

See if this successfully combines your current code with my new code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    Dim r As Long
    Dim c As Long
  
    Set rng = Intersect(Target, Range("E2:G200"))
    
'   Exit sub if no cells updated in range
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
'   Loop through updated cells in range
    For Each cell In rng
'       Get row and column number of updated cell
        r = Target.Row
        c = Target.Column
'       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
            cell.ClearContents
'           Return message
            MsgBox "You cannot enter a Y in cell " & cell.Address(0, 0), vbOKOnly, "ERROR!"
        Else
'           See which column was updated and make appropriate adjustments
            If LCase(cell) = "y" Then
                Select Case c
'                   What to do if column E updated to "y"
                    Case 5
                        'enter any desired code here
'                   What to do if column F updated to "y"
                    Case 6
                        'enter any desired code here
'                   What to do if column G updated to "y"
                    Case 7
                        With Cells(r, "B")
                            .NumberFormat = "dd/mm/yyyy"
                            .Value = Date
                        End With
                End Select
            End If
        End If
    Next cell
    
    Application.EnableEvents = True
    
End Sub

 

[Trevor3007]

Absolutely pure genius...you have cracked ... Thank you so much.



well apart from a wee issue ... when you remove the Y in G , the date still appears in B (sorry) .


KR
TRevor3007

 

[Joe4]

If you want to remove the value from column B when the "y" is removed from column G, add another Case statement to handle the case where the updated value is something other than "y", like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    Dim r As Long
    Dim c As Long
  
    Set rng = Intersect(Target, Range("E2:G200"))
    
'   Exit sub if no cells updated in range
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
'   Loop through updated cells in range
    For Each cell In rng
'       Get row and column number of updated cell
        r = Target.Row
        c = Target.Column
'       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
            cell.ClearContents
'           Return message
            MsgBox "You cannot enter a Y in cell " & cell.Address(0, 0), vbOKOnly, "ERROR!"
        Else
'           See which column was updated and make appropriate adjustments
            Select Case LCase(cell)
'               If updated value is "y"
                Case "y"
                    Select Case c
'                       What to do if column E updated to "y"
                        Case 5
                            'enter any desired code here
'                       What to do if column F updated to "y"
                        Case 6
                            'enter any desired code here
'                       What to do if column G updated to "y"
                        Case 7
                            With Cells(r, "B")
                                .NumberFormat = "dd/mm/yyyy"
                                .Value = Date
                            End With
                    End Select
'               If updated value is something other than "y"
                Case Else
                    Select Case c
'                       What to do if column E updated to something else
                        Case 5
                            'enter any desired code here
'                       What to do if column F updated to something else
                        Case 6
                            'enter any desired code here
'                       What to do if column G updated to something else
                        Case 7
                            Cells(r, "B").ClearContents
                    End Select
            End Select
        End If
    Next cell
    
    Application.EnableEvents = True
    
End Sub

 

[Trevor3007]

Hello Joe4,


once again ..thank you.


I read your "

If you want to remove the value from column B when the "y" is removed from column G, add another Case statement to handle the case where the updated value is something other than "y", like this:​

" but am sorry I did not have a scoobie as to what I was supposed to do.

However, on the worksheet I have a macro button that clears all entries ( Col A+C & B-H). I have now amended that to include Col B.

unless of course you can sort? ....I think this will have to do.

Many,many thanks & I could NOT have done this with out your "Wizard" genius.

KR
Trevor3007

 

[Joe4]

You are welcome.

 

[Trevor3007]

You should change your name to 'Merlin'

many many thanks Joe4 (AKA Merlin)