How to reference/set a checkbox value

**PCI** · 09-28-2014, 04:01 AM

Have a look in the file attached it can help

**Banaticus** · 09-28-2014, 01:27 PM

I'm asking a question. That code that I gave in the original post doesn't seem to work. PCI's code does bring up a form, but clicking on the checkboxes doesn't seem to do anything, no matter what combination I click them in.

I want to have a checkbox appear on a worksheet (well, multiple checkboxes). I want to be able to reference the value of that checkbox in a formula in that worksheet, which I believe means I need to use a form control so that I can reference the true/false which would appear in the checkbox's linked cell. However, I also want someone to be able to click a "reset" button on the worksheet that will reset all the checkboxes to their default values.

**David A Coop** · 09-28-2014, 11:02 PM

Hi Banaticus,

Looking at the code you posted, your IF statement looks backwards! Are you checking to see if MondayNoonBox contains a 1? If so the line should read as follows:

Private Sub SundayNoonBox_Click()
    If MondayNoonBox.Value = 1 Then
        TuesdayNoonBox.Value = 1
    Else
        TuesdayNoonBox.Value = 0
    End If
End Sub

If this doesn't solve your problem, I really need to see a sample of the workbook. Can you post a copy? Be careful about any sensitive data, and try to post a small version if the file is particularly large.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

**Banaticus** · 09-29-2014, 12:43 AM

Ok, it looks like the problem was that, after a series of copy/pasting the other day, I had 5 or 6 checkboxes stacked on top of each other in each open spot and Excel was just confused about which one I was trying to reference. The ones underneath were probably turning on and off as well. After deleting all the checkboxes, then adding them back in and setting everything up again, I finally solved the problem by enabling macros and putting in the following code:

Sub TheResetMacro()
    With ActiveSheet
        .Shapes("SundayLunchBox").ControlFormat.Value = xlOn
        .Shapes("MondayLunchBox").ControlFormat.Value = xlOn
        .Shapes("TuesdayLunchBox").ControlFormat.Value = xlOn
        .Shapes("WednesdayLunchBox").ControlFormat.Value = xlOn
        .Shapes("ThursdayLunchBox").ControlFormat.Value = xlOn
        .Shapes("FridayLunchBox").ControlFormat.Value = xlOn
        .Shapes("SaturdayNoonBox").ControlFormat.Value = xlOn
        .Shapes("SundayNoonBox").ControlFormat.Value = xlOn
        .Shapes("MondayNoonBox").ControlFormat.Value = xlOn
        .Shapes("TuesdayNoonBox").ControlFormat.Value = xlOn
        .Shapes("WednesdayNoonBox").ControlFormat.Value = xlOn
        .Shapes("ThursdayNoonBox").ControlFormat.Value = xlOn
        .Shapes("FridayNoonBox").ControlFormat.Value = xlOn
        .Shapes("SaturdayNoonBox").ControlFormat.Value = xlOn
        Range("B7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("C7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("D7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("E7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("F7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("G7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("H7").Select
            ActiveCell.FormulaR1C1 = "12:30:00 AM"
        Range("B2").Select
    End With
End Sub

The linked sheet here is from "before" I made those changes.