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

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

On Jan 28, 8:10*pm, GS wrote:
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 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

John Smith formulated the question :
On Jan 28, 8:10*pm, GS wrote:
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 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

You're welcome. I appreciate the feedback!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc