Enhancement to Conditional Field Selection


EE Professionals,

I have a great little app. written for me by SiddharthRout which I'd like a slight enhancement for.  The Macro works great however, I'd like the ability to have a cell in the spreadsheet that actually determines how many "falses" are necessary to shut down the "further questions" (currently set at 3 ((hardcoded)).  I think it's an enhancement to this part of the code:

        On Error Resume Next
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 And Range("A" & i).Offset(-1).Value = "False" And _
            Range("A" & i).Offset(-2).Value = "False" And _
            Range("A" & i).Offset(-3).Value = "False" Then
                Target.Value = ""
                Application.EnableEvents = True
                Exit Sub

I have put the change cell (IN RED) in the file example so you could see what I'm attempting to do.  It's set at 3 in the code.  I want to vary the number of "falses" that must be consecutively entered in order to shut down the rest of the responses within the section  (for clarification, there are 12 sections w/15 responses/section but I believe they are all affected by the code above).

Thank you in advance.

Bright01
Conditional-Input-Macro.xlsm
Bright01Asked:
Who is Participating?
 
Arno KosterConnect With a Mentor Commented:
Bright,

1) it is definitely a good idea to keep the "On error goto 0" statement as long as one of the "On error resume next" or "On error goto [...]" statements are used.
    These statements determine how the macro handles exceptions and should always be used in pairs.

2) that's what I did, changed the code and when selecting new values, they were removed when they made 4 consecutive falses.
I see now that I made a slight mistake while posting the question :

considering me working on a dutch language office suite, and "False" translating to "Onwaar", you can see my mistake in line 6 :
you should be using this code
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 Then
                valid = False
                For response = 1 To Range("L3")
                    valid = (Range("A" & i).Offset(-response).Value <> "False")
                    If valid Then Exit For
                Next response
                    If valid = False Then
                    Target.Value = ""
                    Application.EnableEvents = True
                    Exit Sub
                End If
            End If
        Next

Open in new window


sorry !
0
 
Arno KosterCommented:
replace the complete 'for ... next' loop with
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 Then
                valid = False
                For response = 1 To Range("L3")
                    valid = (Range("A" & i).Offset(-response).Value <> "Onwaar")
                    If valid Then Exit For
                Next response
                    If valid = False Then
                    Target.Value = ""
                    Application.EnableEvents = True
                    Exit Sub
                End If
            End If
        Next

Open in new window

0
 
Bright01Author Commented:
Akoster,

Thank you for your quick response.  Several questions after testing;

1.) What do you do about the         On Error GoTo 0    command at the end of the "for....next" loop?  Do I keep that in or delete it?  You can see this in the actual code.

2.) When I substitute the code, go to the worksheet, put in 4, it doesn't stop at 4 consecutive "Falses"; even when I shut down Excel and bring it back up.

Can you look at the sample I sent, make the adjustments you have suggested, test it once or twice and send it back?  I think we're close.

Thank you in advance,

B.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
B,

Since Akoster is already working on it, let him give a try first :) I am sure he will be able to crack it. If you guys get stuck, lemme know and I will step in :)

Sid
0
 
Bright01Author Commented:
Excellent JOB!  Thank you.
0
 
SiddharthRoutCommented:
Good Job akoster :)

Sid
0
 
Bright01Author Commented:
Hey guys; I have a quick request.  I'm now trying to incorporate the model that both of you have contributed to; into my production system.  I'm having difficulty in understanding the code and trying to line up the right fields.  In my production copy, I have several rows in-between each section and I don't know how "case targets" work.  Also, if you look at your reference to j= and k=, I'm assuming that that is the "To/Be" cells/fields as j and k are the columns in the To/Be sections. Finally, I'm getting an error with regard to  Private Sub Worksheet_Change(ByVal Target As Range) because I have another macro named the same thing.  Can I combine them or change the name?

Thanks for the additional help.

Best regards,

B.

0
 
Arno KosterCommented:
the case target statements function like a big if ... then ... elseif ... then ... elseif ... then ... else ... end if

eg.

select case [question]
  case [answer1]
    [solution1]
  case [answer2]
    [solution2]
  [...]
end select

translates to

if [question] = [answer1] then
  [solution1]
elseif [question] = [answer2] then
  [solution2]
[...]
end if
0
 
Arno KosterCommented:
"j" and "k" indicate from which row to which row the false conditions are to be checked.
eg. j = 26, k = 15 indicates that questions 4 up to 15 are to be checked. As the check is done on column A, it would be the "As/Is" that is checked.
0
 
Arno KosterCommented:
The worksheet_change function is triggered by any change in the worksheet. That is : when you change something in the worksheet containing this macro code, the macro will be executed.

So to answer you question : it depends on where the macro code is placed, and what you want to do with it.
Having the same macro on different worksheets is no problem as the macro will only function in the worksheet it is placed in.
When you have two or more worksheet_change macro's in a single worksheet, you will have to combine them in order for it to work.
When you change the name of one of those macro's, it will not be automatically called and you will have to make a button for it or make sure that it is called from another macro.
0
 
Bright01Author Commented:
Akoster,

Thank you for your response.  I can handle the Worksheet_Change function part.  Can you take a quick look at your code?  I think we may have confused the AS/IS selection with the TO/BE results in the macro (i.e. J and K are in the TO/BE column and the selection of "FALSE" X # of times is in the AS/IS selection).  

Finally, I need a way to identify how to discriminate between the extra spaces I have in my production copy.  In other words, in the production copy, I have 3 to 4 spaces between each CATOGORY.  The numbering in the code makes it difficult to figure out how to stop and start the logic within each Category.   I'm happy to open a related question if so advised.

Thank you,

B.
0
 
Arno KosterCommented:
About the J&K :

the original code checks if the change is somewhere in the As/Is column (between cells A12 & A191).
It loops through a number of rows (set by the j & k variables) from the As/Is column, when an arbitrarily chosen number of rows (in the original code : 3 rows) contain values "False" in this column, then the value will be cleared.

When the change is not in the As/Is column, but instead in the To/Be column, then it checks for values "True" or N/A, in which case the value is cleared.


[b]the J & K variables do not act on the To.Be column.[/b]

On the spaces,
you could design a public function that replaces multiple spaces with single spaces or underscores or something.
For leading or trailing spaces (in front of a text or after a text), you can use the "TRIM" function.
eg :
Public Function cleanup(value As String) As String
Dim temp As String
    
    temp = Trim(value)
    While InStr(temp, "  ")
        temp = Replace(temp, "  ", " ")
    Wend
    cleanup = temp

End Function

Open in new window

0
 
Arno KosterCommented:
although indeed the latter question on the spaces might have been better situated in another (related) question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.