CODEWITHSUNDEEP
excelvbauserform
Joe G
Joe G

Reputation: 3

Excel VBA Line of Questioning

I have an excel document that has 5 successive cells on 1 column that need to be filled in with data. I am looking to create a VBA userForm that will go through a line of questioning to help the user input the correct data.

Lets say that the 5 cells are A1 through A5. Once the userform is called it should show a question(label maybe?), A text box for the input of data, and a command button to move on the the next question. all the while moving from A1 to A2 and so on until the line of questioning is done.

Does anybody have any idea how to accomplish this? My VB knowledge is basic but i have tried and tried to no avail.

Thanks in advance!

Upvotes: 0

Views: 367

Answers (1)

Ripster
Ripster

Reputation: 3585

Given a form like this:

UserForm

You could set your questions up in an array and iterate through them each button press while setting the answers to your sheet.

Dim i As Integer
Dim str(1 To 3) As String

Private Sub UserForm_Initialize()
    i = 1
    str(1) = "Question 1"
    str(2) = "Question 2"
    str(3) = "Question 3"

    btnNext.Default = True
    lblQuestion.Caption = str(i)
    txtAnswer.SetFocus
End Sub

Private Sub btnNext_Click()
    Sheets("Sheet1").Cells(i, 1).Value = txtAnswer.Text
    i = i + 1
    If i = UBound(str) + 1 Then
        UserForm1.Hide
        Exit Sub
    End If
    lblQuestion.Caption = str(i)
    txtAnswer.Text = ""
    txtAnswer.SetFocus
End Sub

Example of Result: Result

Upvotes: 2

Related Questions