Question about checkboxes

[WLMPilot]

I have 11 checkboxes (checkbox23-33). Currently, I have a macro per checkbox
that evaluates if box is True/False and then places a value in adjacent cell.

Is there a way to write one macro that will look at each checkbox and do the
same thing?

Thanks,
Les

 

[Madiya]

I have 11 checkboxes (checkbox23-33).  Currently, I have a macro per checkbox
that evaluates if box is True/False and then places a value in adjacent cell.

Is there a way to write one macro that will look at each checkbox and do the
same thing?

Thanks,
Les

Use a For loop.
for each checkbox in userform1
if checkbox.name="23" then
Do things
end if
next.

Regards,
Madiya

 

[Dave Peterson]

Why not just change the linked cell to point at that adjacent cell? Then you
don't even have to use code.

 

[WLMPilot]

I do not use a userform. Just the spreadsheet. Below is the code that is
currently used for each checkbox (with the checkbox number changed for each):

Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then
Worksheets("Pay-Calc").Cells(20, 2).Value = 16
Else: Worksheets("Pay-Calc").Cells(20, 2).Value = 8
End If
End Sub


Les

 

[WLMPilot]

I do not understand what you mean.

Below is the code that is currently used for each checkbox (with the
checkbox number changed for each):

Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then
Worksheets("Pay-Calc").Cells(20, 2).Value = 16
Else: Worksheets("Pay-Calc").Cells(20, 2).Value = 8
End If
End Sub


Les

 

[WLMPilot]

I do not understand what you mean.

Below is the code that is currently used for each checkbox (with the
checkbox number changed for each):

Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then
Worksheets("Pay-Calc").Cells(20, 2).Value = 16
Else: Worksheets("Pay-Calc").Cells(20, 2).Value = 8
End If
End Sub


Les

 

[Dave Peterson]

These are checkboxes from the Control toolbox toolbar.

Go into Design Mode
Rightclick on one and choose Properties
Scroll down to Linked cell and type in an address (I'll use A1 for ease of use,
but it can be in a hidden column on the same worksheet or even on a different
(hidden) worksheet).

Then you can use a formula that in B20 of the Pay-Calc worksheet:
=if(a1=true,16,8)
or
=if('Sheet 9999'!a1=true,16,8)

=========
Since you're using checkboxes from the control toolbox, you can't have one
subroutine that does all the work. You could have 11 subroutines that call a
single routine (passing it the information that the common routine would need)
that does all the work.

Or you could use checkboxes from the Forms toolbar and you can assign a single
common macro to each of the checkboxes.

But I don't think I'd use either macro approach. I'd just use the linked cell
and a formula.

 

[WLMPilot]

That worked great! Thanks.

I would like to re-ask the question, though. In an effort to help learn VBA
in Excel and different ways to do things, can a single macro be written that
can look at each checkbox, evaluate it (true/false), and place its respectful
value in the appropriate cell? I am thinking it can be down, but somehow the
macro must look at the name of the checkbox and pull out the numeric value,
checkbox1, 2, 3, etc. before making further determinations. I just do not
know how to get the name.

Thanks again for you help?
Les

 

[Dave Peterson]

You can use a single macro if you use checkboxes from the Forms toolbar.

You could use 11 macros that call a single macro that does the real work if you
use checkboxes from the Control toolbox toolbar.

For checkboxes from the Forms toolbar, you could use a macro like this (placed
in a general module):

Option Explicit
Sub testme()
Dim CBX As CheckBox

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
CBX.TopLeftCell.Offset(0, 1).Value = 16
Else
CBX.TopLeftCell.Offset(0, 1).Value = 8
End If

End Sub

For checkboxes from the Control toolbox toolbar, you could use something like:

Option Explicit
Private Sub CheckBox1_Click()
Call DoTheWork(Me.CheckBox1)
End Sub
Private Sub CheckBox2_Click()
Call DoTheWork(Me.CheckBox2)
End Sub
Private Sub DoTheWork(CBX As MSForms.CheckBox)
If CBX.Value = True Then
CBX.TopLeftCell.Offset(0, 1).Value = 16
Else
CBX.TopLeftCell.Offset(0, 1).Value = 8
End If
End Sub

You'd need 11 of those _click events that call the single DoTheWork subroutine.

And all these procedures would be in the worksheet module (although, the
DoTheWork procedure could live in a General module -- but remove the Private
from the definition).

That worked great! Thanks.

I would like to re-ask the question, though. In an effort to help learn VBA
in Excel and different ways to do things, can a single macro be written that
can look at each checkbox, evaluate it (true/false), and place its respectful
value in the appropriate cell? I am thinking it can be down, but somehow the
macro must look at the name of the checkbox and pull out the numeric value,
checkbox1, 2, 3, etc. before making further determinations. I just do not
know how to get the name.

Thanks again for you help?
Les

 

[WLMPilot]

Thanks for your help. However, it now brings up a question about something I
did not know existed. I was not aware of the Forms toolbar. What is the
difference between the Forms and Control toolbar and why would one be used
over the other?

Les

 

[Dave Peterson]

The control toolbox toolbar and all its controls were added in xl97.

The controls on the control toolbox have lots and lots of different events and
properties. The controls from the Forms toolbar can't be formatted as many
different ways and have macros assigned to them.

I find that the controls from the Forms toolbar behave more nicely. And if I
don't need all those other properties and events, I'll use them instead of the
control toolbox toolbar controls.

Thanks for your help. However, it now brings up a question about something I
did not know existed. I was not aware of the Forms toolbar. What is the
difference between the Forms and Control toolbar and why would one be used
over the other?

Les