USER FORM ISSUES: Clear data from various ranges using checkboxes and other issues.
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.
