Simplifying my code using FOR loops

[BritsBlitz]

HI, I'm new to VBA and wrote a very simple code to pull data from a survey and store it in a text file. The code is pasted below. I'd like to know if there is a way to simplify the code using For loops to step through the objTs.WriteLine commands and increment the number at the end of the OptionButtons instead of having 9 lines in the code.

Private Sub CommandButton1_Click()

Dim objFSO As Scripting.FileSystemObject
Dim objTS As Scripting.TextStream

' Create the text file.
Set objFSO = New Scripting.FileSystemObject
Set objTS = objFSO.OpenTextFile(CurDir & "/Survey_Results.txt", ForAppending, True)

' Write the results to the text file and then close the file.

'Question 1
objTS.WriteLine "Answer = " & Me.OptionButton1.Value
objTS.WriteLine "Answer = " & Me.OptionButton2.Value
objTS.WriteLine "Answer = " & Me.OptionButton3.Value
objTS.WriteLine "Answer = " & Me.OptionButton4.Value
objTS.WriteLine "Answer = " & Me.OptionButton5.Value
objTS.WriteLine "Answer = " & Me.OptionButton6.Value
objTS.WriteLine "Answer = " & Me.OptionButton7.Value
objTS.WriteLine "Answer = " & Me.OptionButton8.Value
objTS.WriteLine "Answer = " & Me.OptionButton9.Value
objTS.WriteBlankLines 1

objTS.Close

' Thank the user.
MsgBox "Thanks for your feedback."

' Clear the results for next time.
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton3.Value = False
Me.OptionButton4.Value = False
Me.OptionButton5.Value = False
Me.OptionButton6.Value = False
Me.OptionButton7.Value = False
Me.OptionButton8.Value = False
Me.OptionButton9.Value = False

Application.Quit

End Sub

 

[mjbeam]

For i = 1 To 9
    For Each ctl In Me.Controls
        If ctl.Caption = "OptionButton" & i Then
            objTS.WriteLine "Answer = " & ctl.Value
        End If
    Next
Next

 

[mikerickson]

Perhaps

For i = 1 To 9
    With Me.Controls("OptionButton" & i)
        objTS.WriteLine "Answer = " & .Value
        .Value = False
    End With
Next i

But since these are option buttons, only one of them can be true, you might prefer this kind of output

For i = 1 To 9
    With Me.Controls("OptionButton" & i)
        If .Value Then
            objTS.WriteLine "Answer = True in button # " & i
            .Value = False
            GoTo Skip
        End If
    End With
Next i

objTS.WriteLine "Nothing chosen"

Skip:

    Rem rest of code

 

[BritsBlitz]

Thank you. In all three these examples, I get the following error when running the code and the debugger stops at the Me.Controls command: Compile Error: Method or data member not found. I am running this code in Powerpoint so not sure if that makes a difference.

Here's the new code giving the error:

Private Sub CommandButton1_Click()
Dim objFSO As Scripting.FileSystemObject
Dim objTS As Scripting.TextStream
Dim OutApp As Object
Dim OutMail As Object

' Create the text file.
Set objFSO = New Scripting.FileSystemObject
Set objTS = objFSO.OpenTextFile(CurDir & "/Survey_Results.txt", ForAppending, True)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Write the results to the text file, clear the survey and then close the file.
For i = 1 To 9
With Me.Controls("OptionButton" & i)
objTS.WriteLine "Answer = " & .Value
.Value = False
End With
Next i

objTS.WriteBlankLines 1
objTS.Close

' Thank the user.
MsgBox "Thanks for your feedback."

Application.Quit
End Sub

 

[mikerickson]

The of Me keyword assumes that the code is in a user form's code module.

 

[BritsBlitz]

I'm not using UserForms. I'm running the code directly from a Power Point slide. Is there another way to use the For Loop without having to use userforms?

 

[mikerickson]

I don't know Power Point, but where are the option buttons?

 

[BritsBlitz]

The option buttons are on one of the slides. In the VB, the code is in Module 1.