Question: Literal Variable Values at compile time

[SOLIDGAIN]

Any help would greatly be appreciated.

I am trying to access the properties of a checkbox on a form. To do this I stored the checkbox names in an array. I then want to call the properties with the array values but VBA does not recognize what I am trying to do. Below is the code I am trying to use.



Dim FormChkReport(2) As String
FormChkReport(0) = "Orange"
FormChkReport(1) = "Apple"

If(FormChkReport(i).Value = "True&quot Then

End If

 

[JohnYingling]

If you have Offce 200, see CallByName.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Zathras]

It's quite easy really. You don't need an array. I created a form with two check boxes and a command button. Here is the code for the button click event handler:

Option Explicit

Private Sub CommandButton1_Click()
Dim Ctrl As Control
  For Each Ctrl In UserForm1.Controls
    If TypeName(Ctrl) = "CheckBox" Then
       MsgBox Ctrl.Name & " = " & Ctrl.Value
    End If
  Next Ctrl
End Sub

It should be enough to point you the right way. Note that it works for whatever you choose to name the check boxes.

 

[SOLIDGAIN]

Thanks! Sorry to be a pain; do you know how to do this if this the checkboxes were on a sheet instead of a form?

 

[Zathras]

Try this:

Option Explicit
Sub SetComboBoxes()
Dim Ob As OLEObject
  For Each Ob In Sheets(1).OLEObjects
    If TypeName(Ob.Object) = "CheckBox" Then
      MsgBox Ob.Name & " = " & Ob.Object.Value
    End If
  Next Ob
End Sub