Arrays and Checkboxes

[John Smith]

Hi,
I have four checkboxes on a Userform, named "CheckBox1, etc." and that
need to be disabled if the corresponding value isn't available as a
choice in column 3 of the worksheet. If the number 4 isn't anywhere in
column 3 of the worksheet, then the checkbox (4) on the Userform needs
to be disabled. The code below generates a "Method or data member not
found" error. What do I need to fix to make the code work?
Thanks.
James

Sub Grade_Levels()
Dim NmArray As Variant
Dim Ctr As Integer
NmArray = Array(1, 2, 3, 4)
For Ctr = LBound(NmArray) To UBound(NmArray)
If Application.CountIf(Range("C4:C" & LastRow), NmArray(Ctr))
< 1 Then
UserForm3.CheckBox& NmArray(Ctr).Enabled = False ===> ERROR
MsgBox NmArray(Ctr)
End If
Next
End Sub

 

[GS]

John Smith formulated the question :

Hi,
I have four checkboxes on a Userform, named "CheckBox1, etc." and that
need to be disabled if the corresponding value isn't available as a
choice in column 3 of the worksheet. If the number 4 isn't anywhere in
column 3 of the worksheet, then the checkbox (4) on the Userform needs
to be disabled. The code below generates a "Method or data member not
found" error. What do I need to fix to make the code work?
Thanks.
James

Sub Grade_Levels()
Dim NmArray As Variant
Dim Ctr As Integer
NmArray = Array(1, 2, 3, 4)
For Ctr = LBound(NmArray) To UBound(NmArray)
If Application.CountIf(Range("C4:C" & LastRow), NmArray(Ctr))
< 1 Then
UserForm3.CheckBox& NmArray(Ctr).Enabled = False ===> ERROR
MsgBox NmArray(Ctr)
End If
Next
End Sub

You can't do that to objects in any programming language. An object's
name is its ID, and MUST be coded as a constant. IOW, you can't work
with the ref like a string variable. Unfortunatly, unlike VB, VBA does
not support control arrays whereby you can ref a particula control by
its index. For example, if all the checkboxes were named "Checkbx" you
could ref them as Checkbx(0), Checkbx(1), and so on same as an array
index.

In your case you need to check for each value and act on each control
separately...

With Userform3
.CheckBox1.Enabled = _
Application.CountIf(Range("C4:C" & LastRow), 1) > 0
.CheckBox2.Enabled = _
Application.CountIf(Range("C4:C" & LastRow), 2) > 0
.CheckBox3.Enabled = _
Application.CountIf(Range("C4:C" & LastRow), 3) > 0
.CheckBox4.Enabled = _
Application.CountIf(Range("C4:C" & LastRow), 4) > 0
End With 'Userform3

 

[John Smith]

John Smith formulated the question :







You can't do that to objects in any programming language. An object's
name is its ID, and MUST be coded as a constant. IOW, you can't work
with the ref like a string variable. Unfortunatly, unlike VB, VBA does
not support control arrays whereby you can ref a particula control by
its index. For example, if all the checkboxes were named "Checkbx" you
could ref them as Checkbx(0), Checkbx(1), and so on same as an array
index.

In your case you need to check for each value and act on each control
separately...

  With Userform3
    .CheckBox1.Enabled = _
   Application.CountIf(Range("C4:C" & LastRow), 1) > 0
    .CheckBox2.Enabled = _
   Application.CountIf(Range("C4:C" & LastRow), 2) > 0
    .CheckBox3.Enabled = _
   Application.CountIf(Range("C4:C" & LastRow), 3) > 0
    .CheckBox4.Enabled = _
   Application.CountIf(Range("C4:C" & LastRow), 4) > 0
  End With 'Userform3

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Thanks, Garry, I really appreciate your help!
James

 

[GS]

John Smith formulated the question :

Thanks, Garry, I really appreciate your help!
James

You're welcome. I appreciate the feedback!