Simplifying my code using FOR loops

BritsBlitz

New Member
Joined
Jan 10, 2014
Messages
19
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Code:
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
 
Last edited:
Upvote 0
Perhaps

Code:
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

Code:
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top