Please help an old novice out.

[Joe at CMS]

Please help an old novice out. The last time I was heavy into Excel was before the year had a "2" in the front (1990's). I have a Excel questionnaire I've made that has 5 pages to it. The first page has two drop down cells for responses. if either of the two are yes, the other pages need not be completed. And if both are no, then the rest of the 102 questions need a response.

So what would you all suggest? And how would you accomplish this?

Thank you so very much, in advance, for your suggestions.

Joe at CMS

 

[James_Latimer]

Hi Joe,

I'd be inclined to put some code in Worksheet_Change for the first page. If the user selects the relevant combination of answers then the other worksheets can be hidden or showing as necessary.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$A$2" Then
    If ActiveSheet.Range("A1") = "No" And ActiveSheet.Range("A2") = "No" Then
        Sheets("sheetname1").Visible = False
        Sheets("sheetname2").Visible = False
        Sheets("sheetname3").Visible = False
        Sheets("sheetname4").Visible = False
    Else
        Sheets("sheetname1").Visible = True
        Sheets("sheetname2").Visible = True
        Sheets("sheetname3").Visible = True
        Sheets("sheetname4").Visible = True
    End If
End If
End Sub

 

[Joe at CMS]

Thank you James.

 

[jmacleary]

Welcome to the forum. I'm 64 29 years IT and still learning excel (mainly from this forum!).

To your question, I would suggest an event handler - if you give the drop-down cells an initial value of blank, then you could create an onchange macro that would look for two 'nos', and if both were no, switch to the second page. If both were answered, with at least one 'yes', then a polite message to say thanks.

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)With ActiveSheet
If .Range("A1").Value <> "" And .Range("A2").Value <> "" Then
    If .Range("A1").Value = "yes" Or .Range("A2").Value = "yes" Then
        MsgBox "Thank you for completing this questionnaire"
    Else
        Worksheets("SECONDPAGE").Activate
    End If
End If
End With


End Sub

 

[Joe at CMS]

Thank you jmacleary.

Welcome to the forum. I'm 64 29 years IT and still learning excel (mainly from this forum!).

To your question, I would suggest an event handler - if you give the drop-down cells an initial value of blank, then you could create an onchange macro that would look for two 'nos', and if both were no, switch to the second page. If both were answered, with at least one 'yes', then a polite message to say thanks.

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)With ActiveSheet
If .Range("A1").Value <> "" And .Range("A2").Value <> "" Then
    If .Range("A1").Value = "yes" Or .Range("A2").Value = "yes" Then
        MsgBox "Thank you for completing this questionnaire"
    Else
        Worksheets("SECONDPAGE").Activate
    End If
End If
End With


End Sub