How to select answer in dropdown list to automatically open up new cells to input data

[simeonmcc]

Hi people,

If anyone can help.

I have near completed my excel sheet but I'm wondering where my dropdown list is, when a yes or no answer is selecting can the sheet automatically insert new cells for further answers to be entered into the sheet that does not mess up the format of my sheet?

Any help will be greatly appreciated.

Thanks

 

[pbornemeier]

Assuming by "dropdown list" you mean a Data Validation cell with Select,Yes, No choices.
Assuming the added questions and spaces for answers will be in columns I:J
If the Data Validation cell in in F6, add the following code to that worksheet's code page:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F6")) Is Nothing Then
        Select Case Target
        Case "Yes"
            ResetQuestionColumns
            'Add code here to add the cells to be inserted with a Yes answer
            'First Yes Question
            Range("I2").Value = "Q1."
            Range("J2").Value = "What is your favorite color?"
            Range("I3").Value = "A1."
            Range("J3").Interior.Color = rgbLightBlue
            'Second Yes Question
            
        Case "No"
            ResetQuestionColumns
            'Add code here to add the cells to be inserted with a No answer
            'First No Question
            Range("I2").Value = "Q1."
            Range("J2").Value = "What is the capital of ancient Assyria?"
            Range("I3").Value = "A1."
            Range("J3").Interior.Color = rgbLightBlue
            'Second No Question



        Case Else
            'Reset your other input cells
            ResetQuestionColumns
        End Select
            
    End If
    
    'Set column widths
    Range("I:J").Columns.AutoFit
    
End Sub

Sub ResetQuestionColumns()
    With Intersect(ActiveSheet.UsedRange, Range("I:J"))
        .Value = vbNullString
        .Interior.Color = xlNone
    End With
End Sub