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


"OssieMac" wrote:

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

--
Regards,

OssieMac


"Noemi" wrote:

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" wrote:

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



--
Regards,

OssieMac


"Noemi" wrote:

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