Initial to Target Conditional Selection of Question Answer

Bright,

Thanks for using the related question, otherwise I may not have noticed your problems in getting it to work...
Give me a couple of minutes, and i'll help you out.

Much thanks!  You can probably see how tough it is for me to match up the logic.

B.

This should be it.

I copied the original macro contents to their new location and updated the cell references to take into account the different categories and their cell placement.

 updated-Target-of-Limit-to-Answe.xlsm

Before you can use it however, you need to put the consecutive number of falses somewhere in a cell. I have preset the formulae to use the value 3 , but ideally this should be a cell, such as eg. [E2]. In order to update the worksheet, a password is required which I do not posess.

'=========================================================
                For response = 1 To 3 'Range("E2")
'=========================================================

Select allOpen in new window

Akoster,

Thanks for the reply and code!  I'm testing it now.  When you say I need to have the number of Falses in a cell, I have place them in a cell outside the Q & A; and it seems to work with regard to limiting the number of falses, however, take a look at the cell next to the cell that cannot be inputed and the subsiquent (within category that are then limited) CELLS.  You should not be able to input anything into them either once the threshold is reached.  BTW; the range should be from 1 - 15 Falses entered stops the next entry with 15 meaning there is no "governor" on the entry.  I could however, live with 1 to 5 (falses) halt entry.  

BTW; password is JAM.

Thank you!

B.

Akoster,

I'm still testing the macro and it's very good. However, try skipping one of the questions; you can still answer questions down the category.  By giving you the Password, can you make the slight modeifications to include the cell reference?

Thank you,

B.

Bright,

Nice to see that it works out !

I have placed the "number of falses" limit in cell E1, and included this in the macro code.
By using the same color text as the background, it should be invisible when viewing or printing. Just to be sure, I set the font size to 1.


update-2-Target-of-Limit-to-Answ.xlsm

Akoster,

This may require some testing on your end.  Here's what I have.  I see where if set to three, on the third false, it prevents a third false.  When set to three, it should allow three falses, then prevent ANY ADDITIONAL INPUT IN ANY OF THE CELLS left in the category (1-15) including input in the cells in column D.  Cells in C and  D that are prior to the limit being reached may be changed but not any data elements beyond the threshold in C or D.  

Let me explain; When a question is asked, we want to limit the number of false answers before shutting down the ability to add any additional answers.  This saves both time and frustration since the questions tend to build on one another.  So if someone answered X number of questions False, we want to shut down the category and move on to the next.  Make sense?

Also, I'm getting a "Type MisMatch" error; not sure what that is.

Take a look,

And "thank you" in advance.

B.

B,

So if I do understand you correctly, you want to start reading the answers, and after the third (when number of falses is set to 3) false answer (no matter how much partial or correct answers are in between) you want to block any changes in columns C and D for all questions that have not been answered yet.

this differs from how I interpreted the starting post, where a sequential (consequtive) number of false answers is mentioned.

Yes, sequential and "false" stops category input. A true or partial restarts the count.  New Category starts count over also.  Yes; C & D are blocked when count "false" is reached...

B

Thank you for your response, it's all clear to me now.
I couldn't get the "a true or partial result restarts the 'number of falses' count" out of the prior information !

Sorry.  I must say, I love EE for both the results and the interaction with smart and talented people. Sometimes these macros are rather difficult to understand and the requirements are always a challenge to explain.  However, the tenacity of most of the EE Professionals is unbelievable.  My membership to EE is the best dollar I spend every month.  Thanks again for hanging in on this.

B.

Akoster,

Any update?  Much thanks,

B.

I got it working, it actually became somewhat simpler :

Private Sub Worksheet_Change(ByVal Target As Range)
'This macro automatically populates a To/Be cell with Achieve thus auto including the value
'for the maturity model.  Also it sorts the priority questions in the Priorities tab so that
'you can quickly see which questions have the most value to the client
'Finally, it makes sure that after a number of consecutive false answers, any additional
'selections are shut down. This check is performed for each category
'

Dim i As Integer, j As Integer, k As Integer
Dim valid As Boolean
Dim response As Integer
Dim checks As Integer
Dim number_of_falses As Integer
    
    On Error GoTo errHandler
'-- disabling screenupdating prevents visual flicker when running this subroutine.
'-- pay attention that it is enabled again !
'    Application.ScreenUpdating = False

'-- disabling events prevents events from happening when the change event is processed.
Application.EnableEvents = False
    
    '~~> populate To/Be cells
    If Target.Column = 3 Then
        For r = 4 To Range("C65536").End(xlUp).Row
            If Cells(r, 3).Value = "True" Then Cells(r, 4).Value = "ACHIEVE"
        Next r
    End If
        
    '~~>  consecutive falses
    If Not Intersect(Target, Range("C3:D219")) Is Nothing Then
        
        '-- verify correct target value
        Select Case Target.Row
        Case 6, 24, 42, 60, 78, 96, 114, 132, 150, 168, 186, 204
        Case Else
            If Len(Trim(Target.Offset(-1).Value)) = 0 Then Target.Value = ""
        End Select

        '-- determine the rows that are to be checked for this category
        Select Case Target.Row
        Case 3 To 20
            '-- cat 1
            j = 20: k = 6
        Case 21 To 38
            '-- cat 2
            j = 38: k = 24
        Case 39 To 56
            '-- cat 3
            j = 56: k = 42
        Case 57 To 74
            '-- cat 4
            j = 74: k = 60
        Case 75 To 92
            '-- cat 5
            j = 92: k = 78
        Case 93 To 110
            '-- cat 6
            j = 110: k = 96
        Case 111 To 128
            '-- cat 7
            j = 128: k = 114
        Case 129 To 146
            '-- cat 8
            j = 146: k = 132
        Case 147 To 164
            '-- cat 9
            j = 164: k = 150
        Case 165 To 182
            '-- cat 10
            j = 182: k = 168
        Case 183 To 200
            '-- cat 11
            j = 200: k = 186
        Case 201 To 218
            '-- cat 12
            j = 218: k = 204
        End Select
        
        '-- check for a number of consequtive false conditions
        number_of_falses = 0
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 Then
                Select Case Range("C" & i).Value
                    Case CStr(False)
                        number_of_falses = number_of_falses + 1
                    Case CStr(True), "PARTIAL"
                        number_of_falses = 0
                End Select
                
                If number_of_falses > Range("E1") Then
                    Target.Value = "n/a"
                    GoTo errHandler
                ElseIf number_of_falses = Range("E1") Then
                    If Target.Column = 4 Then Target.Value = "n/a"
                End If
            End If
        Next
    End If

    '~~> Check if the change is happening in B12:B26
    If Not Intersect(Target, Range("D3:D219")) Is Nothing Then
        '~~> To check for rest 3 conditions
        If Target.Offset(, -1).Value = CStr(True) Or Target.Offset(, -1).Value = "N/A" Or Len(Trim(Target.Offset(, -1).Value)) = 0 Then Target.Value = ""
    End If
   
    
errHandler:
    If Err.Number <> 0 Then
      MsgBox Err.Number & " - " & Err.Description
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Select allOpen in new window

 update-3-Target-of-Limit-to-Answ.xlsm

Akoster,

I am very sorry it has taken me some time to get back with you.  I have been "slammed" with work over the past three weeks.  However, given it is Saturday, I have gotten back into this.  I tested the code and we still have several simple problems.  And most of all, I really appreciate you hanging with me on this.  So here's what I've learned;

If you go to the first Category (CATEGORY1) and select three FALSE answers in Column C you will find that when you select the forth FALSE it does not let you do that ... this is correct. HOWEVER, if you select TRUE or PARTIAL in the next cell, you can then select another FALSE and so on.  What it should do is not let you enter any more answers in any of the cells below the third FALSE regardless of what you select.  This would mean ANY answers for cells C or D WITHIN THAT CATEGORY.  The FALSE count starts over on the next Category.

Next,

If you select 3 FALSES in a row, and don't answer in the corresponding cell "D", for some reason you can't go back and put answers in the corresponding Cells in "D".  You should be able to do that in all corresponding cells that have answers (in "C"), even after the macro/formula has shut down any additional answers to the cells below the third FALSE.  

Finally, a small problem..... if you push the "Clear" button we get a "Type Mismatch".... you must push OK 13 times to eliminate it.  I think this is a simple problem somewhere in Module 1 but I can't find it.

Hopefully this is it in terms of work for you.  Again, you have been most kind to help me out on this.

Very appreciated,

B.

Akoster,

This is great!  I haven't tested it fully, but in the first pass it seems to work very well.  You've done a great job on this; and no simple task.  I hate to scope creep anything but I was wondering, is there a way to Turn this capability ON/OFF by indicating via either an ActiveX "button" or a "YES/NO" or "check box" in a cell?  Also, can I move the Governor # (in this case "3") to another position by cut/past or drop/drag, without changing the code (i.e. does Excel follow the logic in the code of changes to a cell's location)?

Much thanks; and I'm happy to issue a follow on question if these answers are going to take any time.

Best regards,

B.

a "follow on" question for the on/off functionality would be nice, not so much because of the time taken to answer them, but rather to enable others having similar problems to find their way efficiently.

the 'governor number' can be put anywhere, just make sure to update the reference from the code to match the new situation.
To have maximum flexibility in this, you can replace _ALL_  items of

Range("E1")

Select allOpen in new window

to

Range("Governor_number")

Select allOpen in new window

.

Then select the E1 cell (containing the number 3), and assign it the name "Governor_number" by typing this in the name box (the left part of the formula bar, white field with text "E1") or by navigating to the names section in the 'insert' or 'formula' menu sections and linking the name "Governor_number" to the cell E1.

Great work!  Thank you for sticking with this effort.  Not an easy fix but you did a great job with the logic and coding.

Best regards,

B.