Solved

Enhancement to Conditional Field Selection

Posted on 2011-03-15
13
315 Views
Last Modified: 2012-05-11

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
0
Comment
Question by:Bright01
  • 7
  • 4
  • 2
13 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 35136561
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
 

Author Comment

by:Bright01
ID: 35137045
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35137344
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 35147034
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
 

Author Closing Comment

by:Bright01
ID: 35147553
Excellent JOB!  Thank you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35147617
Good Job akoster :)

Sid
0
 

Author Comment

by:Bright01
ID: 35151053
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
 
LVL 19

Expert Comment

by:akoster
ID: 35155184
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
 
LVL 19

Expert Comment

by:akoster
ID: 35155200
"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
 
LVL 19

Expert Comment

by:akoster
ID: 35155222
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
 

Author Comment

by:Bright01
ID: 35157141
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
 
LVL 19

Expert Comment

by:akoster
ID: 35157370
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
 
LVL 19

Expert Comment

by:akoster
ID: 35157390
although indeed the latter question on the spaces might have been better situated in another (related) question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question