By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

Problem with MANY checkboxes

P: 2
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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub chk70_Click()
  2. If chk70.Value = True Then
  3.     cbxYN47.Visible = True
  4.     cbxB47.Visible = True
  5.     lbl35.Visible = True
  6.     lbl36.Visible = True
  7. Else
  8.     cbxYN47.Visible = False
  9.     cbxB47.Visible = False
  10. End If
  11. 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?
Jan 10 '08 #1
Share this Question
Share on Google+
7 Replies


kadghar
Expert 100+
P: 1,295
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?
Jan 10 '08 #2

P: 2
My version is Visual Basic 6.3 for Excel 2003.
Jan 10 '08 #3

kadghar
Expert 100+
P: 1,295
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub Something(ByVal i As Integer)
  2.     UserForm1.Controls("combobox" & i).Visible = UserForm1.Controls("checkbox" & i).Value
  3. End Sub
And adding this very same code to every checkbox click event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CheckBox1_Click() 'it's the same code for each chkbox
  2.     Call Something(CInt(Mid(Me.ActiveControl.Name, 9)))
  3. 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
Jan 10 '08 #4

mafaisal
100+
P: 142
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub Something(ByVal i As Integer)
  2.     UserForm1.Controls("combobox" & i).Visible = UserForm1.Controls("checkbox" & i).Value
  3. End Sub
And adding this very same code to every checkbox click event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CheckBox1_Click() 'it's the same code for each chkbox
  2.     Call Something(CInt(Mid(Me.ActiveControl.Name, 9)))
  3. 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
Jan 10 '08 #5

kadghar
Expert 100+
P: 1,295
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.
Jan 10 '08 #6

Expert 5K+
P: 8,434
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
Jan 11 '08 #7

Expert 5K+
P: 8,434
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...
Jan 11 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.