Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 45

Thread: If statement with CommandButton

  1. #21
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    LukeAM

    This is an example of what I meant by Section Head comments. As you see, they can explain the algorithm of the code.
    Private Sub OKClick()
         
        Unload Userform1
         
    ''''Get first empty Cell in Column A (rowNr)
        Worksheets("Data").Cells(1, 1).Select
         'Worksheets("Data").Cells(2, 1). = ActiveCell
        Do While ActiveCell.Value <> Empty
            rowNr = rowNr + 1
            ActiveCell.Offset(1, 0).Select
        Loop
         'MsgBox rowNr
        rowNr = rowNr + 1
         
         
        
    ''''Put Checkbox Values in Column C
        Worksheets("Data").Cells(2, 3) = CheckBox1.Value
        Worksheets("Data").Cells(3, 3) = CheckBox2.Value
        Worksheets("Data").Cells(4, 3) = CheckBox3.Value
        Worksheets("Data").Cells(5, 3) = CheckBox4.Value
        Worksheets("Data").Cells(6, 3) = CheckBox5.Value
        Worksheets("Data").Cells(7, 3) = CheckBox6.Value
        Worksheets("Data").Cells(8, 3) = CheckBox7.Value
        Worksheets("Data").Cells(9, 3) = CheckBox8.Value
        Worksheets("Data").Cells(10, 3) = CheckBox9.Value
        Worksheets("Data").Cells(11, 3) = CheckBox10.Value
         
         
    ''''All CheckBoxes = TRUE
        If CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then 'All Checkboxes = True
            Sheets("Template").Copy
            ActiveSheet.Name = "TC1-40"
             
             
    ''''CheckBox 40 = False
        ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = False Then
            Sheets("Template").Copy
            ActiveSheet.Name = "TC1-39"
            Rows(1).Rows(52).Delete
             
    ''''CheckBox 2 = False
        ElseIf CheckBox1 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True And CheckBox2 = False Then
            Sheets("Template").Copy
            ActiveSheet.Name = "TC1,3-40"
            Rows(1).Rows(4).Delete
             
    ''''CheckBox 3 = False
        ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True And CheckBox3 = False Then
            Sheets("Template").Copy
            ActiveSheet.Name = "TC1-2,4-40"
            Rows(1).Rows(5).Delete
             
    ''''CheckBox 4 = False
        ElseIf CheckBox1 = True And CheckBox2 = True And Che
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  2. #22
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I (incompletely) refactored your entire code. You will have to go thru all the Checkboxes on your Form and add the appropriate number to their Tag property. Then, (see next para,) complete the code.

    As you study this code you will see how I designed it using patterns of values. This makes it very easy to discern where a typographical error occurred. Note that for each Case statement, I merely copied one statement the required number of times, then edited the Values per the pattern of values. Except for Case 20 :LOL

    Please note that VBAX enforces its' own ideas of Comment indentation. I always put Section Head Comments at the start of the line.

    About "dim a bunch;" I use "Option Explicit" at the top of all my code pages, which requires you to explicitly declare all variables, thus preveenting any un-caught Typos.
    Private Sub OKClick()
    
    Const NumCheckBoxes As Long = 40 'Adjust to suit
    Const Verifier As Long = NumCheckBoxes * -1 '(True = -1)
    Dim Ctrl As Object
    Dim Verifying As Long
    Dim CheckBoxNumber As Long
    Dim RowToDelete As Variant 'Handles Numbers and Strings for CheckBox 20
    Dim ShName As String
         
         
    '''' Verify None or only one Checkbox is False
      For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is msforms.CheckBox Then
          Verifying = Verifying + Ctrl.Value
          If Not Ctrl.Value Then CheckBoxNumber = Ctrl.Tag 'Number of False Checkbox
        End If
      Next Ctrl
      
      If Verifying > Verifier + 1 Then 'Two false CheckBoxes
        MsgBox "YOU IDIOT! Only uncheck one box! No Biscuit for you!"
        Exit Sub
      End If
      
        Unload Userform1 'I hope this is not the form you are working with below
          
    ''''Get first empty Cell in Column A (rowNr)
    rowNr = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1
         
        
    ''''Put Checkbox Values in Column C
        With Worksheets("Data").Columns("C")
          .Cells(2) = CheckBox1.Value
          .Cells(3) = CheckBox2.Value
          .Cells(4) = CheckBox3.Value
          .Cells(5) = CheckBox4.Value
          .Cells(6) = CheckBox5.Value
          .Cells(7) = CheckBox6.Value
          .Cells(8) = CheckBox7.Value
          .Cells(9) = CheckBox8.Value
          .Cells(10) = CheckBox9.Value
          .Cells(11) = CheckBox10.Value
        End With
         
    ''''All CheckBoxes = TRUE
        If Verifying = Verifier Then 'All Checkboxes = True
            RowToDelete = "none"
            ShName = ""
        Else
    ''''Deal With the False CheckBox
          Select Case CheckBoxNumber
            Case 2
              RowToDelete = 4
              ShName = ",3"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 4
              RowToDelete = 6
              ShName = "-3,5"
            Case 5
              RowToDelete = 7
              ShName = "-4,6"
            Case 6
              RowToDelete = 8
              ShName = "-5,7"
            Case 7
              RowToDelete = 9
              ShName = "-6,8"
            Case 8
              RowToDelete = 10
              ShName = "-7,9"
            Case 9
              RowToDelete = 11
              ShName = "-8,10"
            Case 10
              RowToDelete = 12
              ShName = "-9,11"
            Case 11
              RowToDelete = 13
              ShName = "-10,12"
            Case 12
              RowToDelete = 14
              ShName = "-11,13"
            Case 13
              RowToDelete = 15
              ShName = "-4,6"
            Case 14
              RowToDelete = 16
              ShName =
            Case 15
              RowToDelete = 17
              ShName =
            Case 16
              RowToDelete = 18
              ShName =
            Case 17
              RowToDelete = 19
              ShName =
            Case 18
              RowToDelete = 20
              ShName =
            Case 19
              RowToDelete = 21
              ShName =
            Case 20
              RowToDelete = "22:32"
              ShName = "-19,21"
             Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
            Case 3
              RowToDelete = 5
              ShName = "-2,4"
          End Select
        End If
        
        Sheets("Template").Copy
        ActiveSheet.Name = "TC1" & ShName & "-40"
        If Not RowToDelete = "none" Then .Rows(RowToDelete).Delete
        
        'More Code
         
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #23
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Now that we are dealing with Value patterns, you can see that the RowToDelete is always (Except in Case 20,) The CheckboxNumber + 2 and the shName is always, (except in Case 1,)
    "TC1" & "-" & CStr(CheckBoxNumber -1) & "," & CStr(CheckBoxNumber +1 & "-40" 
    Case 1 is already written and the entire Select Case Statement can be rewritten to
          Select Case CheckBoxNumber
            Case 20
              RowToDelete = "22:32"
              ShName = "-19,21"
            Case Else
      RowToDelete = CheckboxNumber + 2
              ShName = "-" & CStr(CheckBoxNumber -1) & "," & CStr(CheckBoxNumber +1)
           End Select




    Only two Cases vs 39 as I put in the post above.

    here is my final (complete) code for you.
    Option Explicit
    
    Private Sub OKClick()
    
    Const NumCheckBoxes As Long = 40 'Adjust to suit
    Const Verifier As Long = NumCheckBoxes * -1 '(True = -1)
    Dim Ctrl As Object
    Dim Verifying As Long
    Dim CheckBoxNumber As Long
    Dim RowToDelete As Variant 'Handles Numbers and Strings for CheckBox 20
    Dim ShName As String
    Dim i As long
         
         
    '''' Verify None or only one Checkbox is False
      For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is msforms.CheckBox Then
          Verifying = Verifying + Ctrl.Value
          If Not Ctrl.Value Then CheckBoxNumber = Ctrl.Tag 'Number of False Checkbox
        End If
      Next Ctrl
      
      If Verifying > Verifier + 1 Then 'Two false CheckBoxes
        MsgBox "YOU IDIOT! Only uncheck one box! No Biscuit for you!"
        Exit Sub
      End If
      
        Unload Userform1 'I hope this is not the form you are working with below
          
    ''''Get first empty Cell in Column A (rowNr)
    rowNr = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1
         
        
    ''''Put Checkbox Values in Column C
        With Worksheets("Data").Columns("C")
    For i = 1 to 10      
          .Cells(i + 1) = Me.Controls("CheckBox" & Cstr(i)).Value
    Next i
    End With
         
    ''''All CheckBoxes = TRUE
        If Verifying = Verifier Then 'All Checkboxes = True
            RowToDelete = "none"
            ShName = ""
        Else
    ''''Deal With the False CheckBox
    Select Case CheckBoxNumber
            Case 20
              RowToDelete = "22:32"
              ShName = "-19,21"
            Case Else
              RowToDelete = CheckBoxNumber + 2
              ShName = "-" & CStr(CheckBoxNumber - 1) & "," & CStr(CheckBoxNumber + 1)
           End Select    End If
        
        Sheets("Template").Copy
        ActiveSheet.Name = "TC1" & ShName & "-40"
        If Not RowToDelete = "none" Then .Rows(RowToDelete).Delete
        
        'More Code
         
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #24
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    Thanks xld for the suggestion but when I use that code the 'OK' doesn't work when clicked upon. I'm trying to rethink my approach, and Sam T has helped me do this. But I'm not sure what other ways there are.

    Sam T thanks for your time and effort for helping to show me, it is really appreciated. However, I still don't fully understand (since I'm quite new to VBA). And when using this code, it only kind of works. For example, if I miss out CheckBox39, it will delete row 29. If I miss out CheckBox38 it'll delete row 28 etc. This only happens for tick boxes missed after 20. Also, this is just the start of the code I want to create. Eventually I want to be able to tell VBA what happens if you miss out on 2 or 3 or more boxes. Would it be easier if I sent you my workbook so you had a better understanding of what I am trying to achieve? Thanks again.

  5. #25
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Would it be easier if I sent you my workbook so you had a better understanding of what I am trying to achieve?
    Absolutely. I already think that there might be a better way to do what you want. 40 checkboxes!?! Wow!

    Use the "Go Advanced" button below the Quick Reply Editor. Below the Advanced Editor, there is a "Manage Attachments" button that will let you upload the workbook here.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #26
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    OK here it is Sam T. I've had to delete all the text I had with letters (e.g. A, B, C, D etc.) for confidentiality reasons. I've in putted my original code back into this one and you can just copy the code you did if you want to. Thanks very much. And hopefully what I'm trying to achieve is actually possible! Basically I need to tell VBA every possibility of tick boxes that might be 'ticked'. But if you can figure out a code that works, I'll do all the in putting of all the possibilities.
    Attached Files Attached Files

  7. #27
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Do you realise how many combinations of ticked boxes you can have when you have a set of 40 (clue, it is a lot, a very large lot).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #28
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    Yes I understand. I have started making a list of all of them. This is why I feel I need to separate my code (whatever code I use or end up using) into different subs and then use an if statement which includes the OK button (otherwise it doesn't work).

  9. #29
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I think it will take you the rest of your life to make a list of all of them, there are millions. You need a re-think.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #30
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    I don't think there's that many (although there is a LOT), as it doesn't matter what order the boxes are ticked. For example: boxes 1 and 2, boxes 2 and 1. I will only have to input one of these 2 combinations for VBA to make sense of it. Can you think of any other ways to achieve what I want to achieve then?

  11. #31
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You reckon?

    Just imagine you have 3 checkboxes, the valid combinations are

    000
    001
    010
    011
    100
    101
    110
    111

    With 4 it becomes

    0000
    0001
    0010
    0011
    0100
    0101
    0110
    0111
    1000
    1001
    1010
    1011
    1100
    1101
    1110
    1111


    With 5 it becomes

    00000
    00001
    00010
    00011
    00100
    00101
    00110
    00111
    01000
    01001
    01010
    01011
    01100
    01101
    01110
    01111
    10000
    10001
    10010
    10011
    10100
    10101
    10110
    10111
    11000
    11001
    11010
    11011
    11100
    11101
    11110
    11111

    Just think what it will be by the time you get to 40, each checkbox doubles the options as you have the previous number with this checkbox unchecked, then the same number again with it checked. You still think it won't be that many?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #32
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    OK yes there is loads..too many. Is there any other way I can go about this though?

  13. #33
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    OK I think I have kind of figured out a much easier way. I've made another worksheet called 'Template (2)'. In putted all the text in their and then made an if statement via excel. Now all I want to tell VBA is: if a cell says "N/A" Then delete that row. I'm trying to use this formula but it isn't working.
    If Worksheets("Template (2)").Cells("2:2") = "N/A" Then
    Rows(1).Rows(2).Delete

  14. #34
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Luke, What you haven't done is tell us WHAT you are trying to do. You have only told us HOW you are trying to do it.

    Now that I have seen your workbook, I will attempt to tell us what you are trying to do.

    Current existing Objects: Sheets("Template"), UserForm1

    • "Template" has 52 rows with 40 sections delineated by number in Column A (some merged cells)
    • UserForm1 has 40 CheckBoxes whose Tag Values correspond to the section numbers on "Template."
      • Note: Never work on templates. Make a copy and work on the copy.


    The goal is to let the User select by CheckBoxes which Sections on the copy of "Template" to delete.

    The working sheet should, when done, be named with the Pattern:
    "TC & StartSection & If Contiguous(", " & EndContiguousSection &) " - " & StartSection & If Contiguous(", " & EndContiguousSection &) " - " & Etc. (TC3, 12 - 15, 20 - 35, 40)

    The Code for copying the template and deleting the Rows is straight forward using a User Defined Collection of CheckBoxes. You must use a collection because the rows must be deleted from the bottom up and a Collection can be looped thru in either direction. (UDC = User Defined Collection)

    Placing CheckBox Values on Data is a simple 1 to 40 loop thru the UDC with the Row Number = CheckBox.Tag + 1


    Problem in my understanding: Sheets Template and Sheets Data do not match. Your code places the Values of the checkboxes in Data, and deletes sections on Template. What do you intend to do with Sheets Data, Template, (edited,) and the copy of Template(edited)?

    At this time I am setting up your UserForm to only use Labels, not CheckBoxes, with color coding to show which Sections to keep, which to delete, and which have not been checked.

    Check back tomorrow for the code.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  15. #35
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    OK thanks so much for your effort SamT I definitely owe you one. Its quite hard to explain so I'll give it a go.

    Basically the text in Sheets Data is just a shortened down version of the text in Sheets Template (obviously this is hard to see as I have deleted the text, but you can see the row sizes are much bigger in Sheets Template).

    So when you tick the boxes you want it will then pop up a new workbook or sheet with the full version of that text.

    So for example if in Sheets Data in B2 it may say "SWFC". You would then tick the checkbox next to "SWFC" and a completely new workbook/sheet would open saying "Sheffield Wednesday Football Club" in the row "B2".

    Hope you understand it better now. Or do you want me to give another explanation?

  16. #36
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    That's fine. it also meets the specifications of this new UserForm Code I wrote. Replace all the code in the User Form with this code

    First: Delete all the Checkboxes, because I can't scroll down the Form in Design mode.
    Then Copy the Last two Subs in the code, (LabelXXX_Click and LabelXXX_DblClcik) and Paste them 39 times.
    Then change the XXX in the Name to the appropriate Label number and the XXX in the TAg Assignments to the apprpriate Row number String(s)

    BTW, the Sub OK_Click is now only seven lines long including a check that all controls have been set. The longest procedure is only 13 lines long.
    Option Explicit
    
    Dim Sections As Collection
    
    Private Sub Cancel_Click()
    Unload Me
    
    End Sub
    
    Private Sub OK_Click()
    
      If Not AllSectionsMarked Then
        MsgBox "You must mark every section Green or Red"
        Exit Sub
      End If
    
      Valuize_Data
      DeleteRows
      
      'More Sub Calls here
      
      Unload Me
    
    End Sub
    
    Private Function AllSectionsMarked() As Boolean
    Dim i As Long
    
    AllSectionsMarked = True
      For i = 1 To 40
        If Me.Controls(Sections(i)).BackColor = &HFFFFFF Then
          AllSectionsMarked = False
          Exit Function
        End If
    End Function
    
    Private Sub Valuize_Data()
    'Put TrueFalse Values in Column C of Sheet "Data"
    Dim i As Long
        With Worksheets("Data").Columns("C")
            For i = 1 To 40
              If Me.Controls(Sections(i)).BackColor = &HC0FFC0 Then
                .Cells(i + 1) = "True"
              Else: .Cells(i + 1) = "False"
              End If
            Next i
        End With
    
    End Sub
    
    Private Sub DeleteRows()
    Dim i As Long
      With Sheets("Temp")
        For i = 40 To 1 Step -1
          If Not LCase(Me.Controls(Sections(i)).Tag) = "none" Then _
          .Range(Me.Controls(Sections(i)).Tag).EntireRow.Delete
        Next i
      End With
    End Sub
    
    
    Private Sub UserForm_Initialize()
      SetScroolBars
      Init_Sections
      Init_Tags
      Copy_Template
         
    End Sub
    Private Sub SetScrollBars()
      Me.ScrollBars = fmScrollBarsVertical
      Me.ScrollHeight = Me.InsideHeight / 2
    End Sub
    
    Private Sub Init_Sections()
    Dim i As Long
      For i = 1 To 40
        Sections.Add Me.Controls("Label" & CStr(i))
      Next i
    End Sub
    
    Private Sub Init_Tags()
    Dim i As Long
      For i = 1 To 40
        Me.Controls("Label" & CStr(i)).Tag = "none"
      Next i
    End Sub
    
    Private Sub Copy_Template()
      Sheets("Template").Copy After:=Sheets("Data")
      ActiveSheet.Name = "Temp"
    End Sub
    
    Private Sub Label1_Click()
    'If BackColor = Grey Then Single Click turns backcolor Green
    'If BC = Green, then turns Red, add Rows do delete to Tag
    'If BC = Red, Then Turn Green, Add "none" to Tag
    
      With Me.Label1
        If .BackColor = &HFFFFFF Then 'Grey
          .Tag = "None" 'Tag Used to Delete Rows
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0FFC0 Then 'Green
          .Tag = "A1:A2" 'Tag Value = Address of First Cell(s) in Row(s)
          .BackColor = &HC0C0FF
        ElseIf .BackColor = &HC0C0FF Then 'Red
          .Tag = "None" 'Tag Used to Delete Rows
          .BackColor = &HC0FFC0
        End If
      End With
    End Sub
    
    Private Sub Label1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'If BackColor = Grey, Then turn BC = Red, Add Rows to Tag
    'If BC = Green, Then turn BC = Red, Add Rows to Tag
    'If BC = Red, Then turn BC to Green, Add "none" to Tag
    Cancel = True
    
      With Me.Label1
        If .BackColor = &HFFFFFF Then 'Grey
          .Tag = "A1:A2"
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0FFC0 Then 'Green
          .Tag = "A1:A2"
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0C0FF Then 'Red
          .Tag = "None"
          .BackColor = &HC0FFC0
        End If
      End With
    End Sub
    
    Private Sub LabelXXX_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'Copy and paste these two LabelXXX subs 39 times and edit XXX to reflect
    'the Label Name numbers and the Tag Values
      With Me.LabelXXX
        If .BackColor = &HFFFFFF Then 'Grey
          .Tag = "XXX"
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0FFC0 Then 'Green
          .Tag = "XXX"
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0C0FF Then 'Red
          .Tag = "None"
          .BackColor = &HC0FFC0
        End If
      End With
    End Sub
    
    Private Sub LabelXXX_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'Copy and Paste with above LabelXXX sub
    Cancel = True
      
      With Me.LabelXXX
        If .BackColor = &HFFFFFF Then 'Grey
          .Tag = "XXX"
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0FFC0 Then 'Green
          .Tag = "XXX"
          .BackColor = &HC0FFC0
        ElseIf .BackColor = &HC0C0FF Then 'Red
          .Tag = "None"
          .BackColor = &HC0FFC0
        End If
      End With
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #37
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    BTW, for future reference, If a label is not going to be clicked or otherwise used, leave it with the default name (Label1). On the other hand, like in this Form, if the Label will be used for some purpose, change its name to reflect the use or data structure or other mnemonic. In this case I would have used "lblBBClub1" thru "lblBBClub40" or even just "Club1" thru "Club40", being aware that I don't have a clue what the Sections really represent.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  18. #38
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    Thanks a lot again Sam, you really are a 'VBA Master'. I just have some small questions though: the last 2 subs in this code are both LabelXXXDbl_Click, is it these that need to be copied down or did you mean to write one as LabelXXX_Click.


    Also, where you have put ''More sub calls here'. What subs do you want me to call? Call 'Label1_Click()' and all the others?.


    Finally I'm not too sure what you mean by inputting the appropriate Row number String(s) in the tag assignment. For example in the 'Properties' part for 'Label1' where it says 'Tag' I input the cell rows for that label? In this case row 2 and 3. And for 'Label2' it would be row 4.


    Also I am getting an error and it is highlighting in yellow the 'Private Function AllSectionsMarked() As Boolean' line.

  19. #39
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I wrote LabelXXX's so that you could copy them down for the rest of the 40 cases. In the sub names, you have to replace XXX with the label number.

    In the Tag XXX'x you have to replace the XXX's with a Range Address that is in the Row(s) you want deleted Example Lable20.Tag = "A22:A32". Note that you could use "Z22:Z32" or "A22:Z32". All that is important, is that it is a valid Range Address.

    One of the first things initialized in the Form initialize sub is to run InitSections. Since all the important Labels can be accessed by looping thru the Sections collection, all work that is performed on all those labels can be done with a short loop of code.

    When the User clicks on a grey label,it will turn green, if he clicks again, it will turn red, If he doubleclciks on a grey label, it will immediately turn red. If he clicks or d-clicks on a red or green label, it will turn the other color. The Rows in the tags of all red labels will be deleted.

    "Put more Sub [calls] here" is because you have said that you still need to add more code to the project. Ignore it and delete it.

    The code in the module is roughly organized with the command Button subs first followed by the subs they call, then the Form_Initialize and the subs it calls then by the 80 Label click:d-click subs, which are of the type, "if you've seen one, you've seen them all."

    highlighting in yellow the 'Private Function AllSectionsMarked() As Boolean' line.
    The line is yellow because it is the next line to be executed. (It hasn't run yet.) the problem is inside the sub. What is the error?

    There are three main checks built into this code
    1. The user can tell at a glance which labels have been processed and which are set for deletion.
    2. The OK_Click sub won't run if any labels have not been clicked.
    3. Even if it did run, no row of any unclicked label will be deleted.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  20. #40
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    There is a Next i missing from the Function AllSectionsMarked.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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