Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0

    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.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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

  3. #3
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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

  4. #4
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    @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!
    Last edited by MrBlackd; 10-06-2013 at 12:34 PM.

  5. #5
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    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?
    Last edited by MrBlackd; 10-06-2013 at 01:07 PM.

  6. #6
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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

  7. #7
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    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.

  8. #8
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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

  9. #9
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    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.

  10. #10
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    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
    Attached Files Attached Files

Similar Threads

  1. Sending Data From User Form To First Empty Row Of Sheets
    By paul_pearson in forum Excel Help
    Replies: 21
    Last Post: 08-14-2013, 11:04 PM
  2. Replies: 9
    Last Post: 12-04-2012, 09:45 PM
  3. Macro To Clear Certain Data Across Many Sheets
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 12-04-2012, 09:10 AM
  4. Macro to clear data based on color fill
    By Howardc in forum Excel Help
    Replies: 7
    Last Post: 12-03-2012, 09:25 AM
  5. Adding A Menu Bar To A User Form
    By Rasm in forum Excel Help
    Replies: 14
    Last Post: 05-05-2011, 04:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •