Solved

Conditional Cell Input Capability Now Needs to Scale

Posted on 2011-03-13
10
198 Views
Last Modified: 2012-05-11
This request was originally handled successfully by Siddharth.  I need this application to "scale".  Here is the complete file.  There are 15 answers, 12 categories, a total of 180 responses. I think there are only two things that need to be modified for this to work.  One is that the condition on 3 False Answers has to be reset after the 15th answer (for the next category) and the other thing is that right now it needs to allow for three FALSE answers (I can only log two at the present time).

Thank you in advance.

B.
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122144
Sample Attached. Please try it and let me know if it is okay?

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A12:A191")) Is Nothing Then
        
        Select Case Target.Row
        Case 12, 27, 42, 57, 72, 87, 102, 117, 132, 147, 162, 177
        Case Else
            If Len(Trim(Target.Offset(-1).Value)) = 0 Then Target.Value = ""
        End Select
        
        Select Case Target.Row
        Case 11 To 27
            j = 26: k = 15
        Case 26 To 42
            j = 41: k = 30
        Case 41 To 57
            j = 56: k = 45
        Case 56 To 72
            j = 71: k = 60
        Case 71 To 87
            j = 86: k = 75
        Case 86 To 102
            j = 101: k = 90
        Case 101 To 117
            j = 116: k = 105
        Case 116 To 132
            j = 131: k = 120
        Case 131 To 147
            j = 146: k = 135
        Case 146 To 162
            j = 161: k = 150
        Case 161 To 177
            j = 176: k = 165
        Case 176 To 192
            j = 191: k = 180
        End Select
        
        On Error Resume Next
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Range("A" & i).Offset(-1).Value = "False" And _
            Range("A" & i).Offset(-2).Value = "False" And _
            Range("A" & i).Offset(-3).Value = "False" And _
            Range("A" & i).Offset(-4).Value = "False" Then
                Target.Value = ""
                Application.EnableEvents = True
                Exit Sub
            End If
        Next
        On Error GoTo 0
    '~~> Check if the chnage is happening in B12:B26
    ElseIf Not Intersect(Target, Range("B12:B191")) Is Nothing Then
        '~~> To check for rest 3 conditions
        If Target.Offset(, -1).Value = "True" Or _
        Target.Offset(, -1).Value = "N/A" Or _
        Len(Trim(Target.Offset(, -1).Value)) = 0 Then Target.Value = ""
    End If
    Application.EnableEvents = True
End Sub

Open in new window

Total-File.xlsm
0
 

Author Comment

by:Bright01
ID: 35122379
Sid,

Almost there!  When you hit the third "False" you should not be able to input anything (including true, partial or false) in any of the remaining cells in that section. The counter starts over on the next section.

Thank you!  
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122404
>>> The counter starts over on the next section.

yes that is because I set it like that. You don't want that?

Sid
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122413
If you don't want that then replace the code

        Select Case Target.Row
        Case 12, 27, 42, 57, 72, 87, 102, 117, 132, 147, 162, 177
        Case Else
            If Len(Trim(Target.Offset(-1).Value)) = 0 Then Target.Value = ""
        End Select

by

If Target.Row <> 12 And Len(Trim(Target.Offset(-1).Value)) = 0 Then Target.Value = ""

in the above code and now try.

Sid
0
 

Author Comment

by:Bright01
ID: 35123586
Sorry for the confusion.  Here's what I'm getting.  With Macros enabled, I'm able to add more to the fields below.  See the picture I have sent; you will see three falses than I can add more below.  I'm not suppose to be able to add to the section once the threshold has been reached.

B.


Conditional-Column.png
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35125182
Just made some changes. Please check this file.

Sid
Total-File.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 35127569
Sid,

"Thank you!"  Works as promised..... you do very very good work.

When you get a chance, I sent you several operational questions about "The Timer" as to what happens to the other passwords when it changes to LOCKME.

Thanks again,

B.
0
 

Author Comment

by:Bright01
ID: 35136193
Sid,

Greetings.  The App. works great but I'm going to ask (hopefully) a simple question about an "enhancement" to this code.  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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35137270
So basically, you want to, instead of hard coding, want to specify the number of "False" dynamically?

Sid
0
 

Author Comment

by:Bright01
ID: 35137312
Yes.  I know this is an enhancement so I issued it as a "related question".  I think you are best to enhance it but wanted to insure you are rewarded for your efforts.  In the file I have a cell that should be able to handle 1 - 15 and based on the number you input, it will allow for that many falses before locking the rest of the section.

Thank you,

B.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 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