Checkboxes from Control Toolbar

[Noemi]

Hi

I have done some reading here on Checkboxes from Control Toolbar and I am a
little confused.

I have a worksheet which has around 50 checkboxes (had to use this so users
perform the task correctly) and i have re-named all of them cause I wanted to
run a macro which will see if the checkbox is true and if so then perform a
task.

However based oN what I have read does this mean that it is not possible to
do this.

Thanks
Noemi

 

[OssieMac]

There is more than one way to do this. Depends whether you want to run the
code each time an individual checkbox is either checked or unchecked or if
you want to run code to iterate through all the checkboxes after
checking/unchecking.

To run code each time a check box is checked/unchecked, turn on Design Mode
(an icon with a set square, ruler and pencil.). Right click the the check box
and select View code and the VBA editor opens with Private Sub/End sub. You
can insert code like the following between the sub and end sub. Simply
replace the msgboxes with your code for what you want to do. You must then
turn off design mode. This is Event code and runs automatically each time the
checkbox is clicked.

If Me.CheckBox1 = True Then
MsgBox "CheckBox1 is checked"
Else
MsgBox "CheckBox1 is un-checked"
End If


The other way is to open the VBA editor and insert a module. Alt/F11 to open
the VBA editor then menu item Insert->Module.

You can then name your own sub and handle the check boxes like the
following. Note the different way of addressing the check boxes. This code
needs to be started by the user either using a command button or from the
menu.

Sub MyCheckboxCode()

If Sheets("Sheet1").CheckBox1 = True Then
MsgBox "CheckBox1 is checked"
Else
MsgBox "CheckBox1 is un-checked"
End If

End Sub

Alternative way of addressing the checkboxes.

If ActiveSheet.CheckBox1 = True Then
MsgBox "CheckBox1 is checked"
Else
MsgBox "CheckBox1 is un-checked"
End If

 

[Noemi]

Hi OssieMac

Thanks for the information.

I guess I can do the individual checkboxes but before I go down that path I
was hoping you might be able to help me more with your last code.

That is what I have been trying however because I have renamed my checkboxes
to be A1 A2 A3, B1 B2 B3 etc I am not able to get the code to work as it
doesn't like the name.

TO give you more info, I was hoping to have a command button which once
clicked will make some changes based on which checkboxes are ticked and
ignore anything that is not. To help with this I have nmaed each checkbox
with letters as rows and numbers as columns. ou see I have names in the rows
and projects in the columns. The user will click on the box which is
applicable and when complete will click on the command button which will
reformat and remove the checkboxes.

I know you are wondering why not use the reformat version because it would
be much easier and makes the workbook smaller also howeer for some reason the
user cannot provide the details in correct format (due to lasiness) so I have
come up with this solution which doesnt require much effort except clicking
on the check boxes.

So..is there a way to use my own nameing sequence for the checkboxes or am I
making it more complicated and should just use the individual checkbox option
you provided.

I hope I have not confused things.

Thanks
Noemi

 

[OssieMac]

You have me wondering what you have really done Noemi. The code works fine
with renamed checkboxes. When you renamed the checkboxes, did you do it by
turning on Design Mode and then right click and select properties. The
checkbox name is at the very top of the list with the label in brackets like
this (Name) and you should change the name in the column to the right. Both
the following subs work in a module. (You didn't change the Caption instead
of the name did you? Caption is what the user sees on a label adjacent to the
checkbox.)

I would avoid Event driven code if you intend deleting the checkboxes.

I don't suppose that data validation is an option where the user clicks in a
cell and gets a drop down arrow and selects from a list? (This is not Listbox
or Combo box; it is making a drop down from the cell and the drop down arrow
only appears when the user clicks in the cell. It is a good way of forcing a
user to answer in a particular way.)

Sub MyCheckboxCode()

If Sheets("Sheet1").A1 = True Then
MsgBox "CheckBox A1 is checked"
Else
MsgBox "CheckBox A1 is un-checked"
End If

End Sub

Sub MyCheckBoxCode_2()
If ActiveSheet.A1 = True Then
MsgBox "CheckBox A1 is checked"
Else
MsgBox "CheckBox A1 is un-checked"
End If

End Sub

 

[Jon Peltier]

Wouldn't it be a lot easier to link each checkbox to a cell, and query the
cell?

Mac -

You should explicitly state the Value property, even if it is the default
property of a checkbox:

If Sheets("Sheet1").A1.Value = True Then

and you can make it even shorter, since it's a boolean:

If Sheets("Sheet1").A1.Value Then

Noemi -

Using a name that can be confused with a cell address is very bad practice.
What if you adopt Mac's code, and in two weeks look back at it, forgetting
the details? You would be confused by this:

Sheets("Sheet1").A1 = True

because you can see plainly that cell A1 is blank. Use a name like CheckA1
or chkA1 or whatever, where the prefix is a little mnemonic to remind you
what it means.

- Jon

 

[OssieMac]

Hi Jon,

I'm always prepared to listen to advice. On your second comment, "you can
make it even shorter, since it's a boolean:" I was aware of that but the
reason for using =True is because it is self documenting; particularly to new
users.

On your other comment "You should explicitly state the Value property, even
if it is the default property of a checkbox:" Is there a technical reason for
this? Referring to my comment re =True, I can see the value (No pun intended)
of it as self documenting but the fact you commented suggests there is
another technical reason.

 

[Jon Peltier]

Hi Jon,

I'm always prepared to listen to advice. On your second comment, "you can
make it even shorter, since it's a boolean:" I was aware of that but the
reason for using =True is because it is self documenting; particularly to
new
users.

What I've heard is it's a performance issue:

If bVariable = True Then

computes bVariable = True, then compares the result to True

If bVariable Then

compares bVariable to True.

On your other comment "You should explicitly state the Value property,
even
if it is the default property of a checkbox:" Is there a technical reason
for
this? Referring to my comment re =True, I can see the value (No pun
intended)
of it as self documenting but the fact you commented suggests there is
another technical reason.

This is self-documenting to a much greater degree than the If-Then above,
because it could be an unfamiliar object for an experienced developer.
Besides, after seeing a few hundred If-Thens, you should know it's a Boolean
and it's redundant to test the Boolean twice. In addition, there's always a
risk, albeit small, that the default property of the object may be changed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Noemi]

Hi OssieMac & Jon

Thank you both for your comments.

The reason why I haven't used the cell with a selection is because there is
too big a variety that can be choosen plus that would mean an extra step for
the person (I know it is easy but you dont know the person who is using this,
the main reason all this is being done is becuase she is too lazy to type so
this way we are making it easy so all she does is click on a box, nothing
more...it is very sad that we have to do this but she is making life much
harder for others that use the information)

For some reason today it does work with A1 however it still doesn't work if
I change it to a string ie stName (which equals stLetter (for the A cause
this will change) and dbNum (for the number as this will also change)

Here is a better example.

I have about 30 rows with names that vary. There are 12 checkboxes per row
and 5 are selections for Project names (the checkbox is ticked if a person
had worked on the project) and the rest of the checkboxes in the row are to
select what they did on the project ie filing, printing, photocopying etc.

I would like to be able to use a DO...LOOP which will go through each row by
column to see which checkbox is ticked and then store the info ie Noemi -
Project Test - Filing, Printing
Therefor I have tried the following to uncheck all the boxes so temp is
ready for next selection.

You will also notice I am not sure how to refer to the next letter but it is
something I am looking into.

However if there is a much easier way to do this even if it is to remove the
checkboxes and use something else that requires a click then I am all ears.

Public Sub UnCheckAll()

Dim dbNum As Double
Dim stLetter As String
Dim stName As String

Range("A4").Select
Do
If ActiveCell.Value <> "" Then
dbNum = 1
stLetter = "A"
stName = stLetter & dbNum
Do
If Sheets("TEMP").stName = True Then
Sheets("TEMP").stName = False
End If
If dbNum = 6 Then
dbNum = 20
Else
dbNum = dbNum + 1
End If
Loop Until dbNum = 25
dbNum = dbNum + 1
stLetter = stLetter + 1 '''I dont know how to do this yet''''
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value <> ""

End Sub

Thanks for your help

 

[Dave Peterson]

I think you need to provide more info.

But this may help you get started.

First, I wouldn't use the checkboxes from the control toolbox. I'd use the
checkbox from the Forms toolbar. I find them easier to work with and less
impact to excel.

Second, I'd layout my worksheet and checkboxes nicely.

I'd use column A for the names of the workers.

I'd use row 1 for the descriptions of the checkboxes (less space than individual
captions).

I'd put the 12 checkboxes in B2:M31 (30 rows, 12 checkboxes each). I'd make
those columns skinny.

And then I'd use a nice naming convention:
CBX_R##_01 through CBX_R##_12

I'd try it against a test worksheet:

Option Explicit
Sub RunOnce()

Dim CBX As CheckBox
Dim myRange As Range
Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Set wks = ActiveSheet
With wks

'remove any existing checkboxes
.CheckBoxes.Delete

For iRow = 2 To 31
For iCol = .Range("b1").Column To .Range("M1").Column
With .Cells(iRow, iCol)
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Height:=.Height, _
Width:=.Width)
CBX.LinkedCell = .Cells(1).Address(external:=True)
.NumberFormat = ";;;"
End With
With CBX
.Name = "CBX_R" & Format(iRow, "00") _
& "_" & Format(iCol, "00")
.Caption = ""
End With
Next iCol
Next iRow
End With
End Sub

Sub TurnAllOff()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
.CheckBoxes.Value = xlOff
End With
End Sub

Sub CreateStrings()
Dim wks As Worksheet
Dim myStr As String
Dim iRow As Long
Dim iCol As Long

Set wks = ActiveSheet
With wks
For iRow = 2 To 31
myStr = .Cells(iRow, 1).Value
For iCol = .Range("b1").Column To .Range("M1").Column
If .CheckBoxes("cbx_r" & Format(iRow, "00") _
& "_" & Format(iCol, "00")).Value = xlOn Then
myStr = myStr & ", " & .Cells(1, iCol)
End If
Next iCol
.Cells(iRow, "N").Value = myStr
Next iRow
End With
End Sub

Notice that in the RunOnce subroutine, each checkbox has a linked cell. But
that linked cell has a custom number format of ";;;". That means that the
true/false won't be seen in the worksheet cell. But it will be seen in the
formulabar if you select that cell.

It could come in handy if you wanted to count the number of trues:
=countif(b2:B31,true)
or
=countif(b2:m2,true)

======
ps. You can't use names that look like addresses. So a name of A1 isn't going
to work. But _A1 or CBX_A1 would be fine.

Remember that xl2007 has 16384 columns. That's 3 alpha characters for the
column "letter".