Arrays and Checkboxes

  • Thread starter Thread starter John Smith
  • Start date Start date
J

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
 
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
Click to expand...

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 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 -
Click to expand...

Thanks, Garry, I really appreciate your help!
James
 
Back
Top