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
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
