simplifying routine
 

I have the following procedure for a checkbox. The trouble is it feels very
cumbersome and long and I have to do it for each box; is there a shorter more
efficient way to do the same?

Private Sub CheckBox87_Click()
cellno = "f34"
If CheckBox87.Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox88.Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox88.Visible = True
End If

Because I need a large number of these boxes, I was thinking of a routine
such as;

For i = 1 to 200

If CheckBox(i).Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox(i+1).Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox(i+1).Visible = True
End If
next i

which would be a load easier than having to copy this the required number of
times for each checkbox. Can anybody guide me in the right direction please?

Many thanks in advance.

simplifying routine
 

My opinion? Keep the individual Click code. That will run rather quickly at
the individual level, where the looping thing has to be called on demand, and
has to look at all whether they changed or not.

Actually, 'best' thing to do is take the code from within one of your _Click
routines and put it in a generic Privat Sub() then from each of the _Click
events, call the generic routine, passing cellno as a parameter. i.e.
something like this, based on your example.

Private Sub CheckBox87_Click()
CheckClickResults "f34", CheckBox87.Value
End Sub

Private Sub CheckClickResults (cellno as String, BoxState as Boolean)
If BoxState = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0,255,0)
... rest of the true condition code
Else
Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
...more of the code
End If
Exit Sub

"KneeDown2Up" wrote:

I have the following procedure for a checkbox. The trouble is it feels very
cumbersome and long and I have to do it for each box; is there a shorter more
efficient way to do the same?

Private Sub CheckBox87_Click()
cellno = "f34"
If CheckBox87.Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox88.Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox88.Visible = True
End If

Because I need a large number of these boxes, I was thinking of a routine
such as;

For i = 1 to 200

If CheckBox(i).Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox(i+1).Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox(i+1).Visible = True
End If
next i

which would be a load easier than having to copy this the required number of
times for each checkbox. Can anybody guide me in the right direction please?

Many thanks in advance.

simplifying routine
 

That's a great improvement, thanks very much 'J' - means I can make
adjustments to the coding and only have to do it once. I understand what
you're saying about it being quicker, but for my 'learning' for future ref,
can the combo boxes be called and incremented in a routine like I suggested?

Thanks

Martin

"JLatham" wrote:

My opinion? Keep the individual Click code. That will run rather quickly at
the individual level, where the looping thing has to be called on demand, and
has to look at all whether they changed or not.

Actually, 'best' thing to do is take the code from within one of your _Click
routines and put it in a generic Privat Sub() then from each of the _Click
events, call the generic routine, passing cellno as a parameter. i.e.
something like this, based on your example.

Private Sub CheckBox87_Click()
CheckClickResults "f34", CheckBox87.Value
End Sub

Private Sub CheckClickResults (cellno as String, BoxState as Boolean)
If BoxState = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0,255,0)
... rest of the true condition code
Else
Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
...more of the code
End If
Exit Sub

"KneeDown2Up" wrote:

I have the following procedure for a checkbox. The trouble is it feels very
cumbersome and long and I have to do it for each box; is there a shorter more
efficient way to do the same?

Private Sub CheckBox87_Click()
cellno = "f34"
If CheckBox87.Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox88.Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox88.Visible = True
End If

Because I need a large number of these boxes, I was thinking of a routine
such as;

For i = 1 to 200

If CheckBox(i).Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox(i+1).Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox(i+1).Visible = True
End If
next i

which would be a load easier than having to copy this the required number of
times for each checkbox. Can anybody guide me in the right direction please?

Many thanks in advance.

simplifying routine
 

Yes, doing that kind of thing when possible can really simplify code
maintenance later on.

As for the second question, the answer is basically yes - you can loop
through collections like that and base actions upon the state of each one in
turn. You can also do things to them as a group, such as unchecking or
checking all checkboxes in the collection.

"KneeDown2Up" wrote:

That's a great improvement, thanks very much 'J' - means I can make
adjustments to the coding and only have to do it once. I understand what
you're saying about it being quicker, but for my 'learning' for future ref,
can the combo boxes be called and incremented in a routine like I suggested?

Thanks

Martin

"JLatham" wrote:

My opinion? Keep the individual Click code. That will run rather quickly at
the individual level, where the looping thing has to be called on demand, and
has to look at all whether they changed or not.

Actually, 'best' thing to do is take the code from within one of your _Click
routines and put it in a generic Privat Sub() then from each of the _Click
events, call the generic routine, passing cellno as a parameter. i.e.
something like this, based on your example.

Private Sub CheckBox87_Click()
CheckClickResults "f34", CheckBox87.Value
End Sub

Private Sub CheckClickResults (cellno as String, BoxState as Boolean)
If BoxState = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0,255,0)
... rest of the true condition code
Else
Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
...more of the code
End If
Exit Sub

"KneeDown2Up" wrote:

I have the following procedure for a checkbox. The trouble is it feels very
cumbersome and long and I have to do it for each box; is there a shorter more
efficient way to do the same?

Private Sub CheckBox87_Click()
cellno = "f34"
If CheckBox87.Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox88.Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox88.Visible = True
End If

Because I need a large number of these boxes, I was thinking of a routine
such as;

For i = 1 to 200

If CheckBox(i).Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox(i+1).Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox(i+1).Visible = True
End If
next i

which would be a load easier than having to copy this the required number of
times for each checkbox. Can anybody guide me in the right direction please?

Many thanks in advance.

simplifying routine
 

ok, thanks. Having now just done your routine, I realise I do actually need
to be able to identify the checkboxes in question, so for example in this
one, the checkboxes 87 and 88 would need to be adjusted, but if it were
checkbox 1, similarly box 1 & 2, and so on - how do I get it to recognise
that?

....learnt how to call a routine and what to send from your example, thanks!

"JLatham" wrote:

Yes, doing that kind of thing when possible can really simplify code
maintenance later on.

As for the second question, the answer is basically yes - you can loop
through collections like that and base actions upon the state of each one in
turn. You can also do things to them as a group, such as unchecking or
checking all checkboxes in the collection.

"KneeDown2Up" wrote:

That's a great improvement, thanks very much 'J' - means I can make
adjustments to the coding and only have to do it once. I understand what
you're saying about it being quicker, but for my 'learning' for future ref,
can the combo boxes be called and incremented in a routine like I suggested?

Thanks

Martin

"JLatham" wrote:

My opinion? Keep the individual Click code. That will run rather quickly at
the individual level, where the looping thing has to be called on demand, and
has to look at all whether they changed or not.

Actually, 'best' thing to do is take the code from within one of your _Click
routines and put it in a generic Privat Sub() then from each of the _Click
events, call the generic routine, passing cellno as a parameter. i.e.
something like this, based on your example.

Private Sub CheckBox87_Click()
CheckClickResults "f34", CheckBox87.Value
End Sub

Private Sub CheckClickResults (cellno as String, BoxState as Boolean)
If BoxState = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0,255,0)
... rest of the true condition code
Else
Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
...more of the code
End If
Exit Sub

"KneeDown2Up" wrote:

I have the following procedure for a checkbox. The trouble is it feels very
cumbersome and long and I have to do it for each box; is there a shorter more
efficient way to do the same?

Private Sub CheckBox87_Click()
cellno = "f34"
If CheckBox87.Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox88.Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox88.Visible = True
End If

Because I need a large number of these boxes, I was thinking of a routine
such as;

For i = 1 to 200

If CheckBox(i).Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox(i+1).Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox(i+1).Visible = True
End If
next i

which would be a load easier than having to copy this the required number of
times for each checkbox. Can anybody guide me in the right direction please?

Many thanks in advance.

simplifying routine
 

The calling routine, the CheckBox##_Click() event sends two parameters to the
general routine:
that first parameter is the address of the cell to be changed, the second
parameter is the value of CheckBox## itself, so when you get to the general
routine, it "knows" what cell it should deal with, and while it does not know
the name of the actual checkbox that caused things to get going, it does know
the .value of that box which is all you really need to know to do whatever
needs to be done?

So in the original example
Private Sub CheckBox87_Click()
CheckClickResults "F34", CheckBox87.Value
End Sub

it sends the cell address to be altered and the value of the CheckBox that
was clicked (CheckBox87) so that you know what color to set it to.

For another checkbox, you would change things slightly:
Private Sub CheckBox99_Click()
CheckClickResults "G904", CheckBox99.Value
End Sub

You still have one individual Private Sub CheckBox##_Click() event for each
checkbox, but they all call the same general Sub CheckClickResults routine,
passing parameters unique to that checkbox (cell to color, status of
checkbox).

There are other variations of this process you could use, but I'll stick
with this one until you think you understand it a little better and are more
comfortable with it.

"KneeDown2Up" wrote:

ok, thanks. Having now just done your routine, I realise I do actually need
to be able to identify the checkboxes in question, so for example in this
one, the checkboxes 87 and 88 would need to be adjusted, but if it were
checkbox 1, similarly box 1 & 2, and so on - how do I get it to recognise
that?

...learnt how to call a routine and what to send from your example, thanks!

"JLatham" wrote:

Yes, doing that kind of thing when possible can really simplify code
maintenance later on.

As for the second question, the answer is basically yes - you can loop
through collections like that and base actions upon the state of each one in
turn. You can also do things to them as a group, such as unchecking or
checking all checkboxes in the collection.

"KneeDown2Up" wrote:

That's a great improvement, thanks very much 'J' - means I can make
adjustments to the coding and only have to do it once. I understand what
you're saying about it being quicker, but for my 'learning' for future ref,
can the combo boxes be called and incremented in a routine like I suggested?

Thanks

Martin

"JLatham" wrote:

My opinion? Keep the individual Click code. That will run rather quickly at
the individual level, where the looping thing has to be called on demand, and
has to look at all whether they changed or not.

Actually, 'best' thing to do is take the code from within one of your _Click
routines and put it in a generic Privat Sub() then from each of the _Click
events, call the generic routine, passing cellno as a parameter. i.e.
something like this, based on your example.

Private Sub CheckBox87_Click()
CheckClickResults "f34", CheckBox87.Value
End Sub

Private Sub CheckClickResults (cellno as String, BoxState as Boolean)
If BoxState = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0,255,0)
... rest of the true condition code
Else
Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
...more of the code
End If
Exit Sub

"KneeDown2Up" wrote:

I have the following procedure for a checkbox. The trouble is it feels very
cumbersome and long and I have to do it for each box; is there a shorter more
efficient way to do the same?

Private Sub CheckBox87_Click()
cellno = "f34"
If CheckBox87.Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox88.Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox88.Visible = True
End If

Because I need a large number of these boxes, I was thinking of a routine
such as;

For i = 1 to 200

If CheckBox(i).Value = True Then
Me.Cells.Range(cellno).Font.Color = RGB(0, 255, 0)
Me.Cells.Range(cellno).Font.Bold = True
CheckBox(i+1).Visible = False
Else: Me.Cells.Range(cellno).Font.Color = RGB(0, 0, 255)
Me.Cells.Range(cellno).Font.Bold = False
CheckBox(i+1).Visible = True
End If
next i

which would be a load easier than having to copy this the required number of
times for each checkbox. Can anybody guide me in the right direction please?

Many thanks in advance.