asked on

Need help with modifying VBA (in Excel)

Hello Experts:

I need some help with modifying an IF THEN ELSE (VBA) routine in MS Excel which validates that "required survey questions" have been answered.

Please see attached XLS... all details (e.g., problem and envisioned process flow) are included in the XLS.

I'd welcome any feedback on how the validation for "required survey question" routine can be modified so that the thrown dialogue boxes won't be a nuisance.

P.S. Please note that the actual survey has approximately 50 questions. If "nested IFs" are required, this could become an issue.

Thank you in advance!
Tom
Testing.xlsm

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpExchHelp

ASKER

Glenn:

Absolutely perfect!!! Thousand thanks for providing me such an elegant solution!

Cheers,
Tom

Glenn Ray

You're welcome. Pretty cool control you've got there; took me forever to exit my copy! :-P

-Glenn

ExpExchHelp

ASKER

Glenn:

Quick follow-up... I forgot to consider one scenario.

Not all of the question are in consecutive order... there will be blank rows in between (for comment boxes).

Also, due to other constraints, the drop-downs may be offset by a column or two.

Please see attached XLS to illustrate the setup. My question... given that I have 3 groups of questions, how can I ensure the numbering for all of these are the same? That is, any dialogue boxes for the 2nd group would start with "Q#6" and Q#9 for the 3rd group.

Again, thank you for your help!!!
Tom

P.S. I wasn't able to attach the file... I'll open another post and cross-reference postings.

ExpExchHelp

ASKER

Glenn:

In in a new post, I'm unable to attach any files (XLS, TXT, JPT).... totally odd.

I gladly open up a new post... but again, I'm just unable to attach any files in EE right now.

Either way, allow me to further describe the follow-up:
Question Range #1: Q1 through Q5 (cells B1:B5)
Question Range #2: Q6 through Q8 (cells E7:E9)
Question Range #3: Q9 through Q11 (cells E12:E14)

Given that I have 3 groups of questions, how can I ensure the numbering for all of these are the same? That is, any dialogue boxes for the 2nd group would start with "Q#6" and Q#9 for the 3rd group.

Please let me know if you'd like me to open up a new post in order to award you points. I would have already done it but, again, I'm unable to attach any files in ExpertsExchange right now.

Thank you in advance!
Tom

ExpExchHelp

ASKER

Glenn:

I actually figured it out... below is the attached code... I had to use "y - 1" and "z - 3" in order to adjust the actual question numbering.

Again, thanks!
Tom

    If objNet.UserName = "James" Then
        'Only system administrator should have these permissions
        'Do nothing... workbook will close even when required questions are not answered
    Else
        'Q1 through Q5 (rows x columns)
        'Question #6 is NOT tested (optional demographics question)
        For x = 1 To 5
            If Cells(x, 2).Value = "" Then
               MsgBox "Please enter a value for question #" & x & ".", vbInformation, "Missing information"
               Cancel = True
               Exit Sub
            End If
        Next x
    End If
    
    
    If objNet.UserName = "James" Then
        'Only system administrator should have these permissions
        'Do nothing... workbook will close even when required questions are not answered
    Else
        'Q1 through Q5 (rows x columns)
        'Question #6 is NOT tested (optional demographics question)
        For y = 7 To 9
            If Cells(y, 5).Value = "" Then
               MsgBox "Please enter a value for question #" & y - 1 & ".", vbInformation, "Missing information"
               Cancel = True
               Exit Sub
            End If
        Next y
    End If
    
    
    If objNet.UserName = "James" Then
        'Only system administrator should have these permissions
        'Do nothing... workbook will close even when required questions are not answered
    Else
        'Q1 through Q5 (rows x columns)
        'Question #6 is NOT tested (optional demographics question)
        For z = 12 To 14
            If Cells(z, 5).Value = "" Then
               MsgBox "Please enter a value for question #" & z - 3 & ".", vbInformation, "Missing information"
               Cancel = True
               Exit Sub
            End If
        Next z
    End If

Open in new window