Excel VBA loop changes numbers in Option button name

Question

I'm writing an excel sheet with a macro userform to replace a paper system we have in my work place. Its a simple form with two frames; one containing 3 option buttons and another containing a textbox and then 3 more options. The code I wrote looks like this..

Dim Option1A As String
If Frame1.OptionButton1Low Then
Option1A = "Low"
ElseIf Frame1.OptionButton1Mid Then
Option1A = "Medium"
ElseIf Frame1.OptionButton1High Then
Option1A = "High"
End If

Dim Risk1 As String
Risk1 = TextBox1.Value
If Option1A = "Medium" And Risk1 = "" Then
MsgBox ("Please enter a description of your risk mitigation")
ElseIf Option1A = "High" And Risk1 = "" Then
MsgBox ("Please enter a description of your risk mitigation")
End If

Dim Option1B As String
If FrameF1.OptionButton1FLow Then
Option1B = "Low"
ElseIf FrameF1.OptionButton1FMid Then
Option1B = "Medium"
ElseIf FrameF1.OptionButton1FHigh Then
Option1B = "High"
End If

ActiveCell.Offset(g, 15).Value = Option1A
ActiveCell.Offset(g, 16).Value = Risk1
ActiveCell.Offset(g, 17).Value = Option1B

Simple right? This does exactly what I want it to, the thing is I have 1 pair of frames in this piece of code, there are 17 more pairs on the userform. All the text boxes, frames, option buttons and Strings are named sequentially. So right now I could just copy and paste it 17 more times and get the result I want.

I'm wondering if there's a way to write a Do While loop that changes the numbers, so Option1A becomes Option2A, Option3A and OptionButton1FLow becomes OptionButton2FLow etc. Basically change all the number 1's in the code to x value.

I haven't found any suggestions in my research and am starting to think this isn't possible,

Any insight is greatly appreciated,

Cheers in advance,

Sean

Sorry about the terrible title, I was trying any combination of words to search with!! — Sean Mcfadzean, Feb 22 '18 at 22:59
You mean you have in total 18 pair or frames (that makes 36) and each 1 of them contains 3 options buttons? And what is the value of `g`? I mean, I guess you want to insert 17 rows of data, because you got 17 pairs, right? — Foxfire And Burns And Burns, Feb 22 '18 at 23:08
Why don't you wrap this code into procedure and call it 18 times with appropriate controls as arguments? — Basil Kisel, Feb 22 '18 at 23:14
Foxfire - No in total 18 frames, and yeah the value of g is for later in the code for inserting it into the workbook — Sean Mcfadzean, Feb 22 '18 at 23:17
V. Kisel - That might be a little above my skill level so Ill start reading up on that now, thanks for the tip! — Sean Mcfadzean, Feb 22 '18 at 23:18
For each loop, do you still want to place the variables to those same cells (at the end, `...Offset(g,15).Value = Option1A` and `... = Option2A`, etc? Or will you increase those cells each time too? — BruceWayne, Feb 22 '18 at 23:34
I'm looking to basically use this form to fill out about 54 cells in a row, I left those cells at the bottom so the result of those 20 lines fills the 3 indicated cells, ie (g,15) is Option1A but then the next block of code would be (g,18)=Option2A — Sean Mcfadzean, Feb 23 '18 at 01:27
I used the value g with a piece of code I wrote at the top to find the value of the next empty cell down in the first collumn and write all the values to the right of there. If that makes sense... — Sean Mcfadzean, Feb 23 '18 at 01:28

score 0 · Answer 1 · answered Feb 22 '18 at 23:47

Try something like this. A few quick notes:

1) I don't know what you really want to do at the end. Since what you posted never changes the ActiveCell, in the end, all you're going to do is place the last (17th) values in the Offset values. You should avoid using .Activate/.ActiveCell and use a Range() reference, and increment that.

2) I assume g is properly set to a proper value.

Dim i       As Long
' Create four Arrays to hold the options and risk. Use `1 to 17` instead of just `optionA(16)` 
' (same thing in the end), just for learning purposes and ease of understanding.
Dim optionA(1 To 17), risk(1 To 17), optionB(1 To 17)

' Loop through the array 17 times, checking values each time and saving that value to the array.
For i = LBound(optionA) To UBound(optionB)
    If Frame1.OptionButton1Low Then
        optionA(i) = "Low"
    ElseIf Frame1.OptionButton1Mid Then
        optionA(i) = "Medium"
    ElseIf Frame1.OptionButton1High Then
        optionA(i) = "High"
    End If

    risk(i) = TextBox1.Value
    If (optionA(i) = "Medium" Or optionA(i) = "High") And risk(i) = "" Then
        MsgBox ("Please enter a description of your risk mitigation.")
    End If

    If FrameF1.OptionButton1FLow Then
        optionB(i) = "Low"
    ElseIf FrameF1.OptionButton1FMid Then
        optionB(i) = "Medium"
    ElseIf FrameF1.OptionButton1FHigh Then
        optionB(i) = "High"
    End If

    ActiveCell.Offset(g, 15).Value = optionA(i)
    ActiveCell.Offset(g, 16).Value = risk(i)
    ActiveCell.Offset(g, 17).Value = optionB(i)
Next i

Thanks, That's great but the bit I'm struggling with is increasing the number beside the Frame and option button i.e. "FrameX.OptionButtonXLow", But I really like the If/or in the middle there that's helpful. — Sean Mcfadzean, Feb 23 '18 at 01:31
@SeanMcfadzean what do you mean? Can you detail what you're trying to do with increasing the number beside the frame and option button? — BruceWayne, Feb 23 '18 at 03:49

score 0 · Accepted Answer · answered Feb 23 '18 at 13:34

Use Controls property of UserForm object to address all its members (like Frames, TextBoxes, etc) by their name

Furthermore you can use Switch() function to shorten down the option buttons value checking and corresponding value to be adopted

Dim iFrame As Long
Dim OptionA As String, Risk As String, OptionB As String 'this variables are just loop-scoped, hence no need to name them loop-wise. so let's name them without any numeric index

With Me
    For iFrame = 1 To 18
        OptionA = Switch(.Controls("OptionButton" & iFrame & "Low"), "Low", .Controls("OptionButton" & iFrame & "Mid"), "Medium", .Controls("OptionButton" & iFrame & "High"), "High", True, "") ' the last condition (i.e.: True, "") ensures not to hit any 'Null' error should all option buttons be unchecked

        Risk = .Controls("TextBox" & iFrame).Value
        Select Case OptionA & Risk
            Case "Medium", "High"
                MsgBox ("Please enter a description of your risk mitigation")
        End Select

        OptionB = Switch(.Controls("OptionButton" & iFrame & "FLow"), "Low", .Controls("OptionButton" & iFrame & "FMid"), "Medium", .Controls("OptionButton" & iFrame & "FHigh"), "High", True, "")

        g = iFrame ' just a guess
        ActiveCell.Offset(g, 15).Value = OptionA
        ActiveCell.Offset(g, 16).Value = Risk
        ActiveCell.Offset(g, 17).Value = OptionB
    Next
End With

Thank you the Controls property was exactly what I was looking for — Sean Mcfadzean, Feb 28 '18 at 15:22

Excel VBA loop changes numbers in Option button name

2 Answers2