Thread: Add Caption to Options Buttons in Order

Say I have 10 Option Buttons on a form that I call .ob1, .ob2, .ob3, up to .ob10

Then say the captions on these buttons need to change based on code. Is there a way to loop through and change the captions with an array or do I need to change each caption individually?

something like

for each control in form
         Control & 1.caption
next

I do not know how it would loop through the controls with the different numbers if that is possible?

Hello djblois1,

It is easier to used a named range to hold the list of captions. In this macro a named range "Captions" is used. The macro retrieves the sequence number of the Option Button from its name and uses it as index into the named range. The allows the option Buttons to be retrieved in any order and still have the correct caption assigned.

Sub AddCaptions()

  Dim Ctrl As Object
  Dim I As Integer
  Dim RegExp As Object
  
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "\D+(\d+)"
    
    For Each Ctrl In Me.Controls
      If TypeName(Ctrl) = "Option Button" Then
        If Ctrl.Name Like "obSelect*" Then
           I = RegExp.Replace(Ctrl.Name, "$1")
           Ctrl.Caption = Range("Captions").Item(I)
        End If
      End If
    Next Ctrl
    
End Sub

Hello djblois1,

Here is the revised macro using a fixed array. The Regular Expressioin (RegExp) is used to parse the sequence number from of the Option Button's name. This sequence number is used as the index for the Caption array.

Sub AddCaptions()

 'Array version
 
  Dim Captions As Variant
  Dim Ctrl As Object
  Dim I As Integer
  Dim RegExp As Object
  
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "\D+(\d+)"
    
    ReDim Captions(1 To 10)
      Captions(1) = "Product Description"
      Captions(2) = "Division"
      Captions(3) = "Deptartment#"
      Captions(4) = "Department Name"
      Captions(5) = "Product Category"
      Captions(6) = "Product Manager"
      Captions(7) = "Second Manager"
      Captions(8) = "Country"
      Captions(9) = "Weight"
      Captions(10) = "UPC"
    
    For Each Ctrl In Me.Controls
      If TypeName(Ctrl) = "Option Button" Then
        If Ctrl.Name Like "obSelect*" Then
           I = RegExp.Replace(Ctrl.Name, "$1")
           Ctrl.Caption = Captions(I)
        End If
      End If
    Next Ctrl
    
End Sub

Why not using the simplest methods ?

for j=1 to 10
  Me("ObSelect" & j).caption=choose(j,"Product Description","Division","Deptartment#","Department Name","Product Category", "Product Manager","Second Manager","Country","Weight","UPC")
next

or

for j=1 to 10
  Controls("ObSelect" & j).caption=choose(j,"Product Description","Division","Deptartment#","Department Name","Product Category", "Product Manager","Second Manager","Country","Weight","UPC")
next

or

 sn=array("","Product Description","Division","Deptartment#","Department Name","Product Category", "Product Manager","Second Manager","Country","Weight","UPC")
 for j=1 to 10
  Me("ObSelect" & j).caption=sn(j)
 next

or

 for j=1 to 10
  Me("ObSelect" & j).caption=split("|Product Description|Division|Deptartment#|Department Name|Product Category|Product Manager|Second Manager|Country|Weight|UPC","|")(j)
next

You could try

Sub AddCaptions()

Dim iX As Integer
For iX = 1 To 9
Me("ob" & iX).Caption = Choose(iX, "Division", "Deptartment#", _
    "Department Name", "Product Category", "Product Manager", "Second Manager", _
    "Country", "Weight", "UPC")
Next iX
End Sub