USER FORM ISSUES: Clear data from various ranges using checkboxes and other issues.

**MrBlackd** · 10-05-2013, 11:19 PM

I have an excel file with a sheet named Schedule that contains cell ranges for 12 months ( they are named sep_std, oct_std, noe_std, dec_std,....etc....,aug_std).
I have also made a userform that has 12 checkboxes one for each month and I want to associate the checkbox with the ranges mentioned,
for example Checkbox1 for sep_std, Checkbox2 for oct_std, and so on ending with Checkbox12 for aug_std, so as to be able to delete the
contents of the selected ranges for every month that is clicked.

Started by using the code below but this would be for one month only

Code:

If CheckBox1.Value = True Then
    Worksheets("Schedule").Activate
    Range("sep_std").Select
    Selection.ClearContents

Then I thought that I could use Union method and I wrote this code

Code:

If CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True And CheckBox8.Value = True And CheckBox9.Value = True And CheckBox10.Value = True And CheckBox11.Value = True And CheckBox12.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std"), Range("mar_std"), Range("apr_std"), Range("may_std"), Range("jun_std"), Range("jul_std"), Range("aug_std")).Select
    Selection.ClearContents
    
    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True And CheckBox8.Value = True And CheckBox9.Value = True And CheckBox10.Value = True And CheckBox11.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std"), Range("mar_std"), Range("apr_std"), Range("may_std"), Range("jun_std"), Range("jul_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True And CheckBox8.Value = True And CheckBox9.Value = True And CheckBox10.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std"), Range("mar_std"), Range("apr_std"), Range("may_std"), Range("jun_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True And CheckBox8.Value = True And CheckBox9.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std"), Range("mar_std"), Range("apr_std"), Range("may_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True And CheckBox8.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std"), Range("mar_std"), Range("apr_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std"), Range("mar_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True And CheckBox6.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std"), Range("feb_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True And CheckBox5.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std"), Range("jan_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std"), Range("dec_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std"), Range("nov_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True And CheckBox2.Value = True Then
    Worksheets("Schedule").Activate
    Union(Range("sep_std"), Range("oct_std")).Select
    Selection.ClearContents

    ElseIf CheckBox1.Value = True Then
    Worksheets("Schedule").Activate
    Range("sep_std").Select
    Selection.ClearContents

    End If

This has worked but it partially did what I wanted. Meaning that if the months were selected sequentially it would have no problem.
But in case I selected 3 checkboxes i.e. Checkbox1 (September) , Checkbox4(December) and Checkbox12(August) then the code failed
to clear the contents of these months (apparently).

Any suggestion of how to overcome this??

BTW I will have to ask a few more questions about this userform but I will do so later. I need to focus on this now.

Thanx in advance for any reply.

Mr.B.

**snb** · 10-06-2013, 12:47 AM

Code:

private Sub CheckBox1_change()
     If CheckBox1.Value  Then   Range("sep_std").ClearContents
end sub

Code:

private Sub CheckBox2_change()
     If CheckBox2.Value  Then   Range("oct_std").ClearContents
end sub

**bakerman** · 10-06-2013, 11:53 AM

Code:

For i = 1 To 12
    If Me("Checkbox" & i) Then Sheets("Schedule").Range(Choose(i, "sep_std", "oct_std", "nov_std", "dec_std", _
        "jan_std", "feb_std", "mar_std", "apr_std", "may_std", "jun_std", "jul_std", "aug_std")).ClearContents
Next

**MrBlackd** · 10-06-2013, 12:24 PM

@snb

Capture.PNG

If you see the attached image you will probably understand better what I am trying to do.

I want to click on one or more months to select the monthly data range and then press the button to actually delete them.
So I modified your code by replacing ClearContents with Selcet, intending to leave clear contents for the command button.
The problem is that if I click one month then the data for this month is selected, but when selecting a second month then there is no union of the ranges... instead the range associated to this month is selected and the other selection is lost. Any idea?

@ bakerman: I think that this did that job as intended, sorry to have missed your reply before posting again...

Thank you both!

**MrBlackd** · 10-06-2013, 01:04 PM

Now I have another stupid question I have extended the code to

Code:

        For i = 13 To 24
            If Me("Checkbox" & i) Then Sheets("Schedule").Range(Choose(i, "sep_pay", "oct_pay", "nov_pay", "dec_pay", _
                "jan_pay", "feb_pay", "mar_pay", "apr_pay", "may_pay", "jun_pay", "jul_pay", "aug_pay")).ClearContents
        Next

so as to clear the respective ranges of the payment status per month to but it gets stuck on

Code:

Sheets("Schedule").Range(Choose(i, "sep_pay", "oct_pay", "nov_pay", "dec_pay", _
                "jan_pay", "feb_pay", "mar_pay", "apr_pay", "may_pay", "jun_pay", "jul_pay", "aug_pay")).ClearContents

what went wrong?? I mean it worked great with the monthly ranges that contain the names of students, it should similarly work on payment status monthly ranges. No?

**bakerman** · 10-06-2013, 01:37 PM

First of all press F1 in VBA and lookup Choose

Code:

        For i = 13 To 24
            If Me("Checkbox" & i) Then Sheets("Schedule").Range(Choose(i-12, "sep_pay", "oct_pay", "nov_pay", "dec_pay", _
                "jan_pay", "feb_pay", "mar_pay", "apr_pay", "may_pay", "jun_pay", "jul_pay", "aug_pay")).ClearContents
        Next

**MrBlackd** · 10-06-2013, 01:46 PM

OK thanks for the info, indeed it works now, but what if I had j instead of i, why would this also crash?

Code:

 For j = 13 To 24
            If Me("Checkbox" & j) Then Sheets("Schedule").Range(Choose(j, "sep_pay", "oct_pay", "nov_pay", "dec_pay", _
                "jan_pay", "feb_pay", "mar_pay", "apr_pay", "may_pay", "jun_pay", "jul_pay", "aug_pay")).ClearContents
        Next

Guess I 'd better check F1 first but thanx anyway for your help both times.

**bakerman** · 10-06-2013, 02:08 PM

Simple.

Code:

 For j = 13 To 24
            If Me("Checkbox" & j) Then Sheets("Schedule").Range(Choose(j-12, "sep_pay", "oct_pay", "nov_pay", "dec_pay", _
                "jan_pay", "feb_pay", "mar_pay", "apr_pay", "may_pay", "jun_pay", "jul_pay", "aug_pay")).ClearContents
        Next

**MrBlackd** · 10-06-2013, 03:28 PM

I had figured it myself about the code. I asked sth different but nevermind it got clear by reading abt Choose.
Thanks again for the help provided.

**MrBlackd** · 10-13-2013, 09:09 PM

This thing has gone further than I thought so I have one last question ...

I am attaching a sample of the excel file that I am working on so as to be better understood.

I have a weekly schedule for a guitar teacher that he has max 5 classes per day.
I want for every student to have a count of the times that the payment status is false or cancel

I have used named ranges for the cells that correspond to names and payment status and
tried with instr to search first for each student name and then offset 2 rows to check for
false or cancelled in the payment status but it was a fiasco so I am ahamed to share the code.

I appreciate any ideas.
thanks a priori