How to uncheck checkboxes with Date Macro

**Ashnette** · 02-02-2010, 02:19 PM

I am new to VBA and this Forum. I am trying to create a bit of a tricky Excel Spreadsheet. I currently have a spreadsheet that records our battery recharging information(Crate #, Date last charged, Date for Recharge). This spreadsheet currently has a Macro that when opened sends an email to certain employees as to which crate requires charging. I would like to change it so that instead of manually entering the dates you check a checkbox and the next date automatically comes up. That part is easy. The difficult part is getting the checkbox to uncheck when the date for recharge passes. The code below unchecks the checkboxes but how do I modify it to uncheck when date is less than today?

Sub ResetAllCheckboxes()
    With ActiveSheet
        For Each chk In .CheckBoxes
            chk.Value = False
        Next
    End With
End Sub

Any help would be appreciated!

Thanks,

Ash

**Leith Ross** · 02-02-2010, 02:26 PM

Hello Ash,

Welcome to the Forum!

Your post title should convey a brief description of what you need help with. Please do not use general words and phrases like "Help!", "Code doesn't work", "Urgent!", "Should be an easy question", "Is this possible?", etc. Your present title doesn't tell anyone what your question is or what type of problems you are experiencing.
A better title would be "How to uncheck a CheckBox after a certain date ".

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags
[code] at the start of the first line,
[/code] at the end of the last line.

As a new member, please take the time to read over the Forum Rules .

**shg** · 02-02-2010, 03:10 PM

... how do I modify it to uncheck when date is less than today?

When what date is less than today? If it's in some cell adjacent to the checkbox, we'd need to see the layout. And if that's true, a Forms checkbox is overkill; you could just use Marlett checkboxes, which are on the same layer as the cells.

**Ashnette** · 02-02-2010, 04:09 PM

I included an example of the layout that currently exists, the date in question is the Date For Recharge. Unfortunately, I have never used Marrett checkboxes. If it makes it easier I am all for it.

**shg** · 02-02-2010, 04:11 PM

There's no checkboxes, Ashnette. Want to back up a step and explain?

**Ashnette** · 02-02-2010, 05:43 PM

The update will look similar to this, the last file was an example of the information I am using. If the date that comes up when the checkbox is checked is less than the current date (eg. Today is 2-Feb-2010 and E1 or E2 is 31-Jan-2010), then the checkbox automatically unchecks.

**shg** · 02-02-2010, 05:58 PM

In the workbook you just posted, delete the checkboxes, and change the font in col A to Marlett. Then insert a formula in A1 and copy down:

=IF(TODAY() >= E1, "a", "")

**Ashnette** · 02-02-2010, 06:58 PM

Unfortunately, that wouldn't work with this worksheet. The original worksheet has a macro that emails certain employees when the date for recharge passes. When the crate is recharged the employee then goes back and enters in the date that he/she finished charging that crate. The goal of using the checkbox was so the employee would check the box when finished charging a crate and a pre-written formula would populate the information after(eg. date charged, date for next recharge). Once the date for recharge passes the checkbox would automatically uncheck. Is there a way to do this with Marlett?

**Ashnette** · 02-04-2010, 01:53 PM

While researching the Marlett checkbox I found this code to make the Marlett checkbox a double click instead of manually entering a letter.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Target.Count > 1 Then Exit Sub
        If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub
        Target.Font.Name = "marlett"
        If Target.Value <> "a" Then
        Target.Value = "a" 
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.ClearContents 
        Cancel = True
        Exit Sub
    End If
End Sub

After entering the following code to the Workbook Open section of the file.

For Each c In Worksheets("Sheet1").Range("H7:H78")
    If c.Value = "Needs Recharge" Then Range("A" & c.Row).Value = "r"
    Next c

Now with these 2 codes it detects when a crate needs to be recharged and replaces the checkbox letter "a" with an "r"(which comes up as an "X" in Marlett font). When the employee who recharges the crate finishes recharging a crate they then double click the checkbox, which will then change the 'X" back to a check.

**Ashnette** · 02-04-2010, 01:55 PM

Thanks to shg and Paul for their help!

**shg** · 02-04-2010, 02:39 PM

Well done, Ashnette.

How to uncheck checkboxes with Date Macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to uncheck checkboxes with Date Macro

Re: Need Some help!

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Re: How to uncheck checkboxes with Date Macro

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions