vb code error OptionButton not defined? and syntax

[MeisterConrad]

I've got a UserForm in a financial Workbook that handles transfers from one internal account to another. The idea is to click one of the odd-numbered OptionButtons at the left of the Account List to denote the origin of the transfer, and then click on of the even-numbered OptionButtons at the right side to denote the destination account.

I literally used to have 40 IF/THEN statements that included code for the entire operation - it works, but it's SO much "duplicate" (slightly modified) code. I think I'm on a much more efficient track now, but I'm still having trouble making the code say what I want it to say. Getting this bit right would be a big hurdle for me in this project. I thought I hit the idea spot-on, so I tried just the important piece of the code to test it out. I get an error message saying that "OptionButton" is not defined. I thought vba already knew what an OptionButton is. Doesn't it? Or how do I tell the vba what an OptionButton is?

Private Sub CommandButton1_Click()
'Dim the variables
Dim OBf As Integer
Dim Tf As Integer
Dim FROMAC As Range

Dim OBt As Integer
Dim Tt As Integer
Dim TOAC As Range

' Build the FROM account
For OBf = 1 To 39 Step 2                                  ' HERE'S WHERE I NEED HELP:
If OptionButton(OBf).Value = True Then          'I get an error message saying OptionButton isn't defined.
Set Tf = (OBf + 1) / 2
Set FROMAC = "LedgerTable" & Tf
FROMAC.Select

'   Export the input from the form into the cells of the Ledger Table.

'   Exit the IF/THEN statement, and Quit the FOR/NEXT loop.
'         I ALSO NEED HELP WITH THIS:
'   I'm confused as to how to phrase it in code, but, if OptionButton 1 is selected as the FROM acct., then LedgerTable1 should pop up on the screen.
'   If that all happens, then we're done here and it's time to move on to the TO account stuff.
'   We need to quit the IF/THEN and the FOR/NEXT.
'   If none of the above happens, THAT's when we go to Next OBf = loop-cycle.

Else
Next OBf
End If


'     This is the same as above, just modified for the different account.
' Build the TO account
For OBt = 2 To 40 Step 2
If OptionButton(OBt).Value = True Then
Set Tt = (OBt + 1) / 2
Set TOAC = "LedgerTable" & Tt
TOAC.Select

'   Export the input from the form into the cells of the Ledger Table.

'   Exit the IF/THEN statement, and Quit the FOR/NEXT loop.
'  So, all the TO account business would happen with, say, OptionButton 12, and LedgerTable6 should be what's showing when the code is done.

Else
Next OBt
End If
End Sub

I'm learning, but I certainly wish I didn't have to learn - and just already knew instead. Thanks for the help. I'm so glad that Mr. Excel exists.

 

[dmt32]

Hi,
to do what you want you would use the Controls Collection to loop through all your OptionButtons

For OBf = 1 To 39 Step 2
    If Me.Controls("OptionButton" & OBf).Value = True Then
    Tf = (OBf + 1) / 2
    Set FROMAC = Range("LedgerTable").Offset(Tf)


Next

This of course assumes that your controls are Named OptionButton1, OptionButton2 etc etc
The other two lines in your code would also likely to have failed - I changed them to what I think you are trying to do but its just a guess

As an alternative suggestion, why not just use TWO comboboxes where users can select from a list - this would be more dynamic & easier to maintain

Dave

 

[MeisterConrad]

Hi,
to do what you want you would use the Controls Collection to loop through all your OptionButtons

For OBf = 1 To 39 Step 2
    If Me.Controls("OptionButton" & OBf).Value = True Then
    Tf = (OBf + 1) / 2
    Set FROMAC = Range("LedgerTable").Offset(Tf)


Next

This of course assumes that your controls are Named OptionButton1, OptionButton2 etc etc
The other two lines in your code would also likely to have failed - I changed them to what I think you are trying to do but its just a guess

As an alternative suggestion, why not just use TWO comboboxes where users can select from a list - this would be more dynamic & easier to maintain

Dave

View attachment 75462

Thanks for the response.
As to your question of 'why not comboboxes?', I'm using the ListBox in the middle as a sort of "Account Menu" in that each of the items listed is a link to that particular Table.
As to how the OptionButtons are organized, all the ones in the "From" Frame are the odd-numbered ones, in order, so that the code line Tf = (OBf+1)/2 works out to be the correct LedgerTable to match the Account that lines up in the ListBox. Ex., OptionButton1 makes OBf = 1. (1+1)/2 = 1, which gets us to LedgerTable1. If OptionButton3 is clicked, then OBf = 3, (3+1)/2 = 2 for LedgerTable2, and so on. Likewise, the OptionButtons in the Frame on the right are even-numbered, in order, so that the code line Tt=OBt/2 works out to be the correct LedgerTable . Clicking OptionButton2 means OBt = 2, 2/2=1, and LedgerTable1; When OBt=4, then 4/2=2, and we get LedgerTable2, and so on.
Regarding your code, it looks like your approach is more "meta" than I was thinking, and, I bet that's the right approach. But there's a few things I don't understand.
1. In the phrase, "Me.Controls("OptionButton" & OBf).Value", what is the "Me"? Is it the Frame on the UserForm? Is it the UserForm itself?
2. Doesn't there need to be an "End If" statement somewhere?
3. My whole point for this part of the code is so that, after the FROMAC is selected, then I can transfer the data from the UserForm to the Table cells. So, I'd have to put that code before the "Next" statement, right? - because we haven't exited the If/Then? But then, how do we, after the FROMAC has been selected and the goal of this portion of code is successful, quit the If/Then, and how do we quit the For/Next so that we can move on to the TOAC code?
4. It occurs to me that the two lines, "Tf = (OBf + 1) / 2", and "Set FROMAC = Range("LedgerTable").Offset(Tf)" don't necessarily need to be included as part of the If/Then. Their definitions are the same whether or not the If conditions are True. I guess it doesn't matter if they go within or outside of the If/Then. -Or does it?

Anyways, I tried your code and got an error message about a FOR with no NEXT, and I don't get it because the NEXT is right there. I see it.
I took your code and put in what feels like is needed to address those questions above. But, something doesn't quite feel right in the nesting of the If/Then and the For/Next. Straighten me out?

Private Sub CommandButton1_Click()
'Dim the variables
Dim OBf As Integer
Dim Tf As Integer
Dim FROMAC As Range

Dim OBt As Integer
Dim Tt As Integer
Dim TOAC As Range

' Build the FROM account
For OBf = 1 To 39 Step 2
If Me.Controls("OptionButton" & OBf).Value = True Then
Tf = (OBf + 1) / 2
Set FROMAC = Range("LedgerTable").Offset(Tf)
FROMAC.Select
'   Is this where I export the data from the form into the cells of the Ledger Table?
Exit If      'is that a valid command?  Either way, I think that here is where the exit should go.
Else
Next OBf     'do I need to actually say "OBf"? -or is it ok to simply say "Next"?
End If

-And I can't help but think that this all feels like we're asking the Frame to check on this OptionButton , then on that one, and on and on. And I wonder if there's a way to just tell the frame, "Get me the number of the clicked OptionButton." Is that possible? What does that look like?


Thanks for the help.

 

[dmt32]

1. In the phrase, "Me.Controls("OptionButton" & OBf).Value", what is the "Me"? Is it the Frame on the UserForm? Is it the UserForm itself?

"ME" keyword represents the parent object from where the code resides. in this case, your userform

2. Doesn't there need to be an "End If" statement somewhere?

yes but I was only correcting the part of your code you were having issues with

3. My whole point for this part of the code is so that, after the FROMAC is selected, then I can transfer the data from the UserForm to the Table cells. So, I'd have to put that code before the "Next" statement, right? - because we haven't exited the If/Then? But then, how do we, after the FROMAC has been selected and the goal of this portion of code is successful, quit the If/Then, and how do we quit the For/Next so that we can move on to the TOAC code?

when your If statement is True you exit a For Next Loop with "Exit For"

4. It occurs to me that the two lines, "Tf = (OBf + 1) / 2", and "Set FROMAC = Range("LedgerTable").Offset(Tf)" don't necessarily need to be included as part of the If/Then. Their definitions are the same whether or not the If conditions are True. I guess it doesn't matter if they go within or outside of the If/Then. -Or does it?

Your need to determine which OptionButton has been selected for required activity (From & To) & your code then take required action.

I took your code and put in what feels like is needed to address those questions above. But, something doesn't quite feel right in the nesting of the If/Then and the For/Next. Straighten me out?

Private Sub CommandButton1_Click()
    
    'Dim the variables
    
    Dim OBf         As Integer
    
    Dim Tf          As Integer
    
    Dim FROMAC      As Range
    
    Dim OBt         As Integer
    
    Dim Tt          As Integer
    
    Dim TOAC        As Range
    
    ' Build the FROM account
    
    For OBf = 1 To 39 Step 2
        
        If Me.Controls("OptionButton" & OBf).Value = True Then
            
            Tf = (OBf + 1) / 2
            
            Set FROMAC = Range("LedgerTable").Offset(Tf)
            
            FROMAC.Select ' you do not need to use select
            
            '   Is this where I export the data from the form into the cells of the Ledger Table
             '   This is the True state of the selected optionbutton & your code should perform required task (To / From)
            
            Exit For        'is that a valid command? - You use Exit For 
            
        Else
            'false state
        End If
        
    Next OBf        'do I need to actually say "OBf"? -or is it ok to simply say "Next" - Its Good Practice
    
End Sub

Dave

 

[MeisterConrad]

"ME" keyword represents the parent object from where the code resides. in this case, your userform

yes but I was only correcting the part of your code you were having issues with

when your If statement is True you exit a For Next Loop with "Exit For"

Your need to determine which OptionButton has been selected for required activity (From & To) & your code then take required action.

Private Sub CommandButton1_Click()
  
    'Dim the variables
  
    Dim OBf         As Integer
  
    Dim Tf          As Integer
  
    Dim FROMAC      As Range
  
    Dim OBt         As Integer
  
    Dim Tt          As Integer
  
    Dim TOAC        As Range
  
    ' Build the FROM account
  
    For OBf = 1 To 39 Step 2
      
        If Me.Controls("OptionButton" & OBf).Value = True Then
          
            Tf = (OBf + 1) / 2
          
            Set FROMAC = Range("LedgerTable").Offset(Tf)
          
            FROMAC.Select ' you do not need to use select
          
            '   Is this where I export the data from the form into the cells of the Ledger Table
             '   This is the True state of the selected optionbutton & your code should perform required task (To / From)
          
            Exit For        'is that a valid command? - You use Exit For
          
        Else
            'false state
        End If
      
    Next OBf        'do I need to actually say "OBf"? -or is it ok to simply say "Next" - Its Good Practice
  
End Sub

Dave

Okay. So, I cleaned it up, and just put in the FROM stuff to test it out piece-by-piece. When I hit the "Execute" CommandButton, I get an error with no message; just the opportunity to end or debug. The definition that pops up after right-clicking and selecting "Definition", says, "Identifier under cursor not recognized". What does that even mean? There's no cursor showing on the code; the entire line of code is highlighted to show me where the problem lies.

Private Sub CommandButton1_Click()
   
    'Dim the variables
    Dim OBf         As Integer
    Dim Tf          As Integer
    Dim FROMAC      As Range
   
   
    ' Build the FROM account
    For OBf = 1 To 39 Step 2
        If Me.Controls("OptionButton" & OBf).Value = True Then
            Tf = (OBf + 1) / 2



            'This is the line that breaks the execution.
            Set FROMAC = Range("LedgerTable").Offset(Tf)
            'The definition message says, "Identifier under cursor is not recognized."  What does that even mean?
            'And I guess, more importantly, how do I fix it?



            Exit For
        End If
    Next OBf
   
   
FROMAC.Select
'I kept this SELECT here to be able to test the code; if all my code is right,
'then I should be able to pull up any LedgerTable by selecting
'the FROM OptionButton of choice and then hitting EXECUTE. Otherwise, my code is wrong.

End Sub

Thanks for "holding my hand" through this, Dave

Much appreciated,
Conrad

 

[dmt32]

I get an error with no message; just the opportunity to end or debug. "Definition", says, "Identifier under cursor not recognized". What does that even mean?

At a guess that error could be something unlrelated - Open the references dialog (Tools > references) and Uncheck any items that are marked "MISSING". and see is this resolves.

Thanks for "holding my hand" through this, Dave

Not sure I am but if your OptionButtons are in Frames then you can simply loop through the controls in each frame to determine which control is True

as an example

Private Sub CommandButton1_Click()
    Dim FromTo(1 To 2) As Long, i As Long
    Dim ctrl           As Control
  
    For i = 1 To 2
        For Each ctrl In Me.Controls("Frame" & i).Controls
            If ctrl.Value = True Then FromTo(i) = Val(Mid(ctrl.Name, 13)): Exit For
        Next ctrl
    Next i
  
    MsgBox "From " & FromTo(1) & Chr(10) & "To " & FromTo(2)
  
  
End Sub

code assumes that optionbuttons have their default name "OptionButton" 1, 2, 3 etc
The From optionbuttons are in a Frame named "Frame1"
and To optionbuttons are in a Frame named "Frame2"

Dave

 

[MeisterConrad]

At a guess that error could be something unlrelated - Open the references dialog (Tools > references) and Uncheck any items that are marked "MISSING". and see is this resolves.


Not sure I am but if your OptionButtons are in Frames then you can simply loop through the controls in each frame to determine which control is True

as an example

Private Sub CommandButton1_Click()
    Dim FromTo(1 To 2) As Long, i As Long
    Dim ctrl           As Control
 
    For i = 1 To 2
        For Each ctrl In Me.Controls("Frame" & i).Controls
            If ctrl.Value = True Then FromTo(i) = Val(Mid(ctrl.Name, 13)): Exit For
        Next ctrl
    Next i
 
    MsgBox "From " & FromTo(1) & Chr(10) & "To " & FromTo(2)
 
 
End Sub

code assumes that optionbuttons have their default name "OptionButton" 1, 2, 3 etc
The From optionbuttons are in a Frame named "Frame1"
and To optionbuttons are in a Frame named "Frame2"

Dave

Hello,
Sorry to have stepped away for so long; I had surgery done and recovery, and some pretty heavy family stuff going on.
Anyway, to pick up where you left off...

I thought there was a way to just check the Frame for which OptionButton has been selected! I think that's the more efficient way to go. Don't you?

Do I need to have all the From OptionButtons to be in sequence; 1 through 20, - and all the To OptionButtons to be in sequence; OptionButton21 through 40? That shouldn't really make any difference, though, should it? Since we're asking the program to give us the Name of the Control ("OptionButton"&#), it doesn't seem like the order of the OptionButtons should matter. -And really, I only need the number of the OptionButton so I can use it to set the the right accounts to the FromAcc and ToAcc variables.

The code is very concise and pretty, although I have questions:

In the opening For statement, do I understand correctly that the "Me" is referring to the UserForm itself? - and the first "Controls" is talking about the frames as identified in the parenthesis? - and that the last "Controls" is referring to the OptionButtons therein? "don't understand the piece "Val(Mid(ctrl.Name, 13))". Is Val the same thing as Value? And what's Mid? And why does ctrl.Name have a 13 attached to it?

So I tried your code ...
-and Yes, all the From OptionButtons (they're all the odd-numbered ones) are in Frame1, and all the To OptionButtons (the evens) are in Frame2,
... and i didn't get the MsgBox we wanted; I got the following error:
"Object doesn't support this property or method"
I thought maybe the "Me.Controls" bit might be looking for some Frames inside of the CommandButton itself, so I changed it from "Me" to "UserForm3".

I still get the same error message. What's that all about?

 

[dmt32]

In the opening For statement, do I understand correctly that the "Me" is referring to the UserForm itself?

Yes - Me keyword replaces the need to use the parent name (e.g.) "UserForm1" of the object

- and the first "Controls" is talking about the frames as identified in the parenthesis? - and that the last "Controls" is referring to the OptionButtons therein?

Yes

"don't understand the piece "Val(Mid(ctrl.Name, 13))". Is Val the same thing as Value?

Val function coreces numbers in a string to a numeric value

And what's Mid? And why does ctrl.Name have a 13 attached to it?

MID is a VBA function that returns a string containing a specified number of characters from a string. 13 is the starting point in the string

I still get the same error message. What's that all about?

Code posted was just an untested suggestion of how you could loop through all your OptionButtons in each Frame to determine which one had been selected to get the controls Index number.
I have no thoughts at moment why you have the error and would need to see all your code & where error is occurring.
Ideally, place copy of your project with dummy data in a file sharing site like dropbox & provide a link to it

Dave