Simplifying my code using FOR loops

BritsBlitz

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
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:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
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
 

BritsBlitz

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980

ADVERTISEMENT

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

BritsBlitz

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

BritsBlitz

New Member
Joined
Jan 10, 2014
Messages
18
The option buttons are on one of the slides. In the VB, the code is in Module 1.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

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
Top