How to reference/set a checkbox value

[Banaticus]

So I had several form-based checkboxes and I can't seem to reference them or change their value in VBA. Perhaps I'm simply not referencing them correctly. I'm currently trying:

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

Also, it appears that code like this won't run unless it's in the Sheet1 code block and not in a Module code block? (Also, you have to be in Design Mode to delete a module, apparently.)

[David A Coop]

Hi Banaticus,

Are you making a statement or asking a question?

Yes, I believe the code you posted cannot be run from a code Module - it has to be in a Sheet or ThisWorkbook object.

As for removing the code, can't you just highlight the macro and delete it? You don't want to delete the whole "Module" - you would loose you worksheet.

BTW - Please wrap code in [CODE] markers. (Use the # icon) - it makes it easier to read.

I hope this helps, please let me know!

Regards,

David

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.

[PCI]

Have a look in the file attached it can help

[Banaticus]

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]

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]

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.

[Banaticus]

I couldn't remember if VBA was one or two equals signs to do a boolean equals and in some languages you can assign a value to a variable in an if statement and because the value was assigned properly the if always evaluates as true -- in those languages you should write "1 = object.value" because you can't assign a value that way, so if you were supposed to use two equals signs then it will tend to throw an error instead of quietly failing, you'll notice the error and realize that you should have put two equals signs there instead of just one.

Ok, back to the checkboxes. I've been looking at my checkboxes and it appears that it may have been failing because I apparently can't rename the darn form-control checkboxes! I'll right click on one to select it (instead of left clicking which just checks/unchecks it) type a new name in the Name box then click something else, and the new name won't stick! If I click anything else or right click on another checkbox then come back and right click on the first checkbox, it'll still have the same old name. This is true whether or not design mode is on. So I have probably been using descriptive names to reference my checkboxes and Excel hasn't had the foggiest idea what I was talking about because the descriptive names haven't been sticking.