Cell Value and checkboxes macro

jlachapelle7

New Member
Joined
Mar 16, 2012
Messages
3
Hi anyone out there. I have to do a template for my cie and I'm almost good at programming on excel , but I don't know a thing on macro but I managed to get my template working with your tread here. I have a 100 questions ''question bank'' template that generate tests with the questions that are checkboxes for 5 different tests, and I assigned a form checkbox to each line for each question and for each exam. So each checkbox is assigned to a cell with return the value 0 or 1 in the same cell that is the checkbox. I then have an advanced filter that generates the test on another sheet. I have over 400 checkbox in my template, so I want to reset all the checkboxes to false (0) and the value of the cell to 0. I assign a button to this macro found on the forum:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Sub LoopCheckBox()
Dim sCheckBox As Shape
For Each sCheckBox In ActiveSheet.Shapes
If sCheckBox.FormControlType = xlCheckBox Then
sCheckBox.ControlFormat.Value = False
End If
Next sCheckBox
End Sub
<o:p></o:p>
; but the problem is that although this macro reset all checkboxes to unchecked, it dosn't update the cell value to 0. On a test sheet, I tried to apply it to a checkbox that is assigned manually to a cell with a false or true value, and it works. Would you please modify this macro to fit what I need or suggest me something?.
<o:p></o:p>
Here is the macro that is applied to the sheet for the checkboxes and it works goods:
<o:p></o:p>
Public Sub CB_Read()
<o:p></o:p>
Dim oShape As Shape
Dim oTarget As Range
<o:p></o:p>
Set oShape = ActiveSheet.Shapes(Application.Caller)
'Change the "Offset(Row,Column)" in the next line to whatever cell is desired for the status of the checkbox.
Set oTarget = oShape.TopLeftCell.Offset(0, 0)
<o:p></o:p>
If oShape.ControlFormat.Value = xlOff Then
oTarget.Value = 0
Else
oTarget.Value = 1
End If
End Sub
<o:p></o:p>
Thank you for taking the time
JLachapelle
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top