• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenation for a OptionButton

Frank M

New Member
3/21/2020

I have some Option Buttons (10) in my form with the naming convention of OptionButton_ (OptionButton1 - OptionButton10).

I am trying to loop through a With statement (see below) with a concatenated Option Button name, but I can't seem to get the With statement to execute successfully.

However, using an index (Ind) I can concatenate an Option Button name within a Controls statement and it works fine.
Code:
Ind = 5
   
For Each Ctrl In HDate_Form1.Controls
      Select Case Ctrl.Name
           Case Is = "OptionButton" & Ind                 
                   Ctrl.ForeColor = BlueColor2                
                   Ctrl.Font.Bold = True                          
                   Ctrl.Value = True                                 
                   Exit For
        End Select
Next Ctrl
**************************

My WITH statement is coded as follows:
Code:
For Ind = 1 to 10
      With HDate_Form1.OptionButton & Ind
             .ForeColor = vbBlack                             
             .Font.Bold = False                                 
             .Value = False                                     
     End With
Next Ind
The error message states "Method or data member not found" (obviously the reference to the OptionButton & Ind code). Have tried other syntaxes
but nothing seems to work.

Are there any limitations when using this type of code in a With (or other) VBA statements?

Thanks for all your help in previous matters. Hoping someone can help me with my latest issue.

Frank M
 
Last edited by a moderator:

Fluff13

Active Member
How about
Code:
Private Sub CommandButton1_Click()
   Dim i As Long
   For i = 1 To 10
      With Me.Controls("OptionButton" & i)
         .ForeColor = vbBlue
         .Font.Bold = True
         .Value = True
      End With
   Next i
End Sub
 

Frank M

New Member
Fluff13

Thanks for your quick response.

Tested your solution and it worked as expected.

Didn't realize that the Me.Controls syntax could be coded inside of a With statement.

Learned something new today.

Thanks Again,

Frank M
 
Top