Problem with MANY checkboxes

[tdw4]

I have a program with tons of checkboxes (over 250). I also have 2 or 3 combo boxes next to the checkbox. How it works is when a user checks a checkbox, the combo boxes need to become visible. I wrote code for each and every checkbox, but it caused the program to run out of memory. To give you an idea, what I have written is...

Private Sub chk70_Click()
If chk70.Value = True Then
    cbxYN47.Visible = True
    cbxB47.Visible = True
    lbl35.Visible = True
    lbl36.Visible = True
Else
    cbxYN47.Visible = False
    cbxB47.Visible = False
End If
End Sub

My main question is can I have a routine that will be called when any checkbox is clicked, rather than having 250 subroutines?

[kadghar]

I have a program with tons of checkboxes (over 250). I also have ...

My main question is can I have a routine that will be called when any checkbox is clicked, rather than having 250 subroutines?

It depends of the version of VB you're using, if it allows you to create control arrays, then you're done, since you can name the comboboxes with the same name, different index, and do the same for the checkboxes.. and then just write the code once with indexes.

If your version doesn't allow you to do that then i think you'll have to play a little bit with the controls and their names as strings. Its a little harder but can be done...

...so, what version are you using?

[tdw4]

My version is Visual Basic 6.3 for Excel 2003.

[kadghar]

My version is Visual Basic 6.3 for Excel 2003.

ok, so it's VBA?? i think we'll have to do it this way. I'll asume you have let the original names for the controls, i.e. CheckBox1, CheckBox2.... ComboBox1, ComboBox2... And they're related to each other the same way, i.e. CheckBox1 is for ComboBox1.
I'll also asume the Form is called UserForm1

Remember you can call any control in Userform1 with something like:
UserForm1.Controls("combobox1").visible = true

so now we will work with the names, to get the number of a control, the easiest way to me would be, knowing they all have the same kind of name (combobox2, checkbox35) to take the name as string and using MID take the characters from the 9th position and transform them into a integer with CINT.

This way if your active control is Checkbox26, the function:
cint(mid(me.activecontrol.name,9)) will return you 26 as an integer.

Try writing a little procedure like:

Public Sub Something(ByVal i As Integer)
    UserForm1.Controls("combobox" & i).Visible = UserForm1.Controls("checkbox" & i).Value
End Sub

And adding this very same code to every checkbox click event:

Private Sub CheckBox1_Click() 'it's the same code for each chkbox
    Call Something(CInt(Mid(Me.ActiveControl.Name, 9)))
End Sub

(im not sure if there's a way to make it the default code for the checkboxes click event, or there's something like the HANDLES command, so useful in 2005, surely Killer would know better)

HTH

[mafaisal]

Hello Kadghar

In VBA Control Array Not taken...?
If yes it is easy to use control Array

eg:-
use Checkbox & Combobox has control array
Private Sub Check1_Click(Index As Integer)
combobox1(index).visible=checkbox1(index).value
End sub

ok, so it's VBA?? i think we'll have to do it this way. I'll asume you have let the original names for the controls, i.e. CheckBox1, CheckBox2.... ComboBox1, ComboBox2... And they're related to each other the same way, i.e. CheckBox1 is for ComboBox1.
I'll also asume the Form is called UserForm1

Remember you can call any control in Userform1 with something like:
UserForm1.Controls("combobox1").visible = true

so now we will work with the names, to get the number of a control, the easiest way to me would be, knowing they all have the same kind of name (combobox2, checkbox35) to take the name as string and using MID take the characters from the 9th position and transform them into a integer with CINT.

This way if your active control is Checkbox26, the function:
cint(mid(me.activecontrol.name,9)) will return you 26 as an integer.

Try writing a little procedure like:

Public Sub Something(ByVal i As Integer)
    UserForm1.Controls("combobox" & i).Visible = UserForm1.Controls("checkbox" & i).Value
End Sub

And adding this very same code to every checkbox click event:

Private Sub CheckBox1_Click() 'it's the same code for each chkbox
    Call Something(CInt(Mid(Me.ActiveControl.Name, 9)))
End Sub

(im not sure if there's a way to make it the default code for the checkboxes click event, or there's something like the HANDLES command, so useful in 2005, surely Killer would know better)

HTH

[kadghar]

Hello Kadghar

In VBA Control Array Not taken...?
If yes it is easy to use control Array

eg:-
use Checkbox & Combobox has control array
Private Sub Check1_Click(Index As Integer)
combobox1(index).visible=checkbox1(index).value
End sub

noup, VBA and VB 2005 doesnt have control arrays. You know its sad but true (with guitar solo). Anyway, the me.controls(index) its not a "that hard" alternative. And i'm sure there're many other ways.

[Killer42]

noup, VBA and VB 2005 doesnt have control arrays. You know its sad but true (with guitar solo). Anyway, the me.controls(index) its not a "that hard" alternative. And i'm sure there're many other ways.

Don't forget, there are ways to simulate a control array. One is pretty much what you described here - that is, just using a number on the end of the control name as an "index".

Another possibility (don't know whether it has been tried in VBA) is what I proposed back in December 2006 in this thread. I don't know whether anyone has put it to practical use yet, but it should make coding simpler.

I guess it still hits the same problem, though - you need some way to connect it up to the actual events.

You know, it occurs to me that you might be able to work around the immediate problem (out of memory) by simply using shorter names, if it's the size of the source which is the problem. For example, "C70" rather than "Chk70". Along the same lines, you could...

• Remove or reduce indenting
• Remove unnecessary parts of the code.
  For instance, these two lines perform (I think) the exact same test...
  If Chk70.Value = True Then
  If Chk70 Then

[Killer42]

Another thought comes to mind (prompted in part by the mention of the Controls collection). But it depends on one important question. Do controls on userforms in Excel have a Tag property? I don't remember.

If so, you could use one big loop through the control on the userform, and put special values in the Tag property of each control to indicate how they link together. For instance, if a combobox has "ABC" in it's tag, then it is made visible when any checkbox with "ABC" in its tag is checked. And so on...