`Private Sub CommandButton1_Click()
'I have a UserForm that includes 2 Frames and 2 Command Buttons.
'The 2 Frames each hold 30 OptionButtons;
' Frame1 has OptionButtons 1 through 30 and allows the user to
' select which, of 30 bank accounts is the origin in a money transfer,
' and Frame2 has 31 through 60 and similarly decides the destination Account in the transfer.
'The 2 CommandButtons are to execute the same idea, but in two different ways. I only need one.
'This code below is meant to display the name of the Range that is becoming FromAcc,
' which is: ("LedgerTable" & [the Number of the OptionButton selected]).
' The MsgBox is just for testing to make sure I get this part right;
' it'll eventually get replaced with more code to execute.
'Many statements are commented-out because, -who wants to include ALL the buttons for just a test? Right?
Dim FromAcc As Range
FromAcc = "No OptionButton has been selected."
'The FromAcc begins as that text because if, at the end, no OptionButton has been selected,
'then the MsgBox, whose message is determined by the value of FromAcc, is all prepared with the right message.
'So, I go off thinkin' I'm all clever, right?
' ...Well then I thought I was really hot stuff when
' I came about the second method via CommandButton2. Watch.
'All of these statements check each individual OptionButton and set FromAcc as its own unique textstring.
'These textstrings are recognized Table names; Named Ranges within the Workbook.
If Frame1.OptionButton1.Value = True Then FromAcc = "LedgerTable1"
If Frame1.OptionButton2.Value = True Then FromAcc = "LedgerTable2"
If Frame1.OptionButton3.Value = True Then FromAcc = "LedgerTable3"
If Frame1.OptionButton4.Value = True Then FromAcc = "LedgerTable4"
If Frame1.OptionButton5.Value = True Then FromAcc = "LedgerTable5"
If Frame1.OptionButton6.Value = True Then FromAcc = "LedgerTable6"
'If Frame1.OptionButton7.Value = True Then FromAcc = "LedgerTable7"
'If Frame1.OptionButton8.Value = True Then FromAcc = "LedgerTable8"
'If Frame1.OptionButton9.Value = True Then FromAcc = "LedgerTable9"
'If Frame1.OptionButton10.Value = True Then FromAcc = "LedgerTable10"
'If Frame1.OptionButton11.Value = True Then FromAcc = "LedgerTable11"
'If Frame1.OptionButton12.Value = True Then FromAcc = "LedgerTable12"
'If Frame1.OptionButton13.Value = True Then FromAcc = "LedgerTable13"
'If Frame1.OptionButton14.Value = True Then FromAcc = "LedgerTable14"
'If Frame1.OptionButton15.Value = True Then FromAcc = "LedgerTable15"
'If Frame1.OptionButton16.Value = True Then FromAcc = "LedgerTable16"
'If Frame1.OptionButton17.Value = True Then FromAcc = "LedgerTable17"
'If Frame1.OptionButton18.Value = True Then FromAcc = "LedgerTable18"
'If Frame1.OptionButton19.Value = True Then FromAcc = "LedgerTable19"
'If Frame1.OptionButton20.Value = True Then FromAcc = "LedgerTable20"
'If Frame1.OptionButton21.Value = True Then FromAcc = "LedgerTable21"
'If Frame1.OptionButton22.Value = True Then FromAcc = "LedgerTable22"
'If Frame1.OptionButton23.Value = True Then FromAcc = "LedgerTable23"
'If Frame1.OptionButton24.Value = True Then FromAcc = "LedgerTable24"
'If Frame1.OptionButton25.Value = True Then FromAcc = "LedgerTable25"
'If Frame1.OptionButton26.Value = True Then FromAcc = "LedgerTable26"
'If Frame1.OptionButton27.Value = True Then FromAcc = "LedgerTable27"
'If Frame1.OptionButton28.Value = True Then FromAcc = "LedgerTable28"
'If Frame1.OptionButton29.Value = True Then FromAcc = "LedgerTable29"
'If Frame1.OptionButton30.Value = True Then FromAcc = "LedgerTable30"
MsgBox (FromAcc.Value)
'...and so, this code doesn't work right, but I think you get the idea of what I'm trying to do.
'But I'm trying!! The next bit of code, for CommandButton2, tries to do the same thing,
'but through a different method. But I think you can see how the CommandButton1-method is simple and direct,
'whereas the second method is rather efficient. I'm trying for efficient, not simple. Help please?
End Sub
Private Sub CommandButton2_Click()
'This code tries to use a counter to cycle through the number attached to each of the OptionButtons,
'and check the value for True or not. When it comes to set the value of FromAcc,
'the same counter is added to the text "LedgerTable", which makes the names of the recognized Tables.
Dim i As Integer
Dim FromAcc As Range
Dim ToAcc As Range
For i = 1 To 6 ' (30)
If Frame1.OptionButton & i.Value = True Then FromAcc = "LedgerTable" & i
Next i
'For i = 31 To 60
'If Frame2.OptionButton & i.Value = True Then FromAcc = "LedgerTable" & i
'Next i
MsgBox ("FromAcc:" & FromAcc.Value) ' ( ... & " ToAcc:" & ToAcc)
'...See? Completely more efficient, right? It doesn't work either.
'Do I have to do some fancy thing like,
'copy the Caption of the OptionButton and strip away the "OptionButton" text
'so the number is the only thing left?
'What 's really annoying is that just when I think I'm getting the hang of it (writing code),
'I come to find that I'm really not. Help? Please? Anyone? -Thanks in advance.
End Sub
`
type here
I wrote the code above, but I'm not so good with syntax, so I'm pretty sure is needs a better eye than mine. And I'm always amazed when I learn a new Function of Excel. So, maybe there's a better, more direct/efficient approach than what I'm thinking. Isn't there just a simple function that just returns the name of whatever is selected inside a particular Frame?
I also tried a modified version of the code at: Is is possible to grab the number of frames in a userform and the number of optionbuttons in a frame? But when I changed the MsgBox, something messed up.
`Private Sub CommandButton3_Click() ' I changed the name from "XX()". I've noted all the changes I made.
Dim c As Control, n As Long ' I changed the variable from nl to n.
Dim FromAcc As Range ' I added this line.
For Each c In UserForm1.Frame1.Controls
If TypeOf c Is msforms.OptionButton Then
n1 = n1 + 1
'There was an unnecessary line of code here that I deleted with no loss.
End If
Next c
' this original line works: MsgBox " option button number = " & n
'my modified version does not:
FromAcc = "LedgerTable" & n
MsgBox = FromAcc.Value
End Sub`
The way you are referring to the option buttons here
For i = 1 To 6 ' (30)
If Frame1.OptionButton & i.Value = True Then FromAcc = "LedgerTable" & i
Next i
is wrong. You are concatenating the contents of the Frame1.OptionButton variable (which doesn't exist) and the i variable.
To make your code work, you can concatenate the "OptionButton" string and the i variable and attempt to find a control of that name in the Frame1.Controls collection like so:
Private Sub CommandButton1_Click()
Dim FromAcc As String
FromAcc = "No option has been selected"
For i = 1 To 4 ' (30)
If Frame1.Controls("OptionButton" & i) = True Then FromAcc = "LedgerTable" & i
Next i
MsgBox FromAcc
End Sub
This, however, will crash if the "OptionButton" & i object is not found. You could implement some error handling or you could try something like this, which should work just as well:
Private Sub CommandButton1_Click()
Dim FromAcc As String
FromAcc = "No option has been selected"
Dim c As Control
For Each c In Frame1.Controls
If TypeName(c) = "OptionButton" And c.Value = True Then
FromAcc = "LedgerTable" & Mid(c.Name, 13, Len(c.Name) - 12)
Exit For
End If
Next c
MsgBox FromAcc
End Sub
To make this work, you have to keep the naming convention of "OptionButton" + number but it appears you are already doing that so all should be good.