Help understanding an expression or sub.

[Bill Williamson]

Just a little info, This is from a working userform, it had 3 text box's 6 combo box's and 6 check box's.
All the data went in same row, all columns next to each other. Ex. A1 - A15
This Sub does one of two things. It can Add a record, witch takes the info from userform saves it to worksheet.
Or get a record, that populates the userform with data from a row in the worksheet.
I need to modify it to work with a different form, but to do so I want to understand it.

Sub AddGetRecord(ByVal Action As XLRecordActionType)
    Dim i As Integer
    Dim ControlsArr As Variant
    
    ControlsArr = FormControls
 
    If Action = xlAddRecord Then RecordRow = WorksheetFunction.CountA(wsData.Range("A:A"))    ' Obviously this is part of the Add Record, I Think it is basically saying go ' to the end of the date colum A , and this is where the data is going to the worksheet
    
    For i = 1 To 15                         ' This cycles through the 15 Controls 
        With Me.Controls(ControlsArr(i))
            If i < 10 Then                                            ' I dont understand what this is for

                If Action = xlGetRecord Then
                    .Text = wsData.Cells(RecordRow, i).Value  'I believe this gets the information from the worksheet and populates the 3 text box's and the 6 combo box's 

                Else
                    wsData.Cells(RecordRow, i).Value = .Value     'I believe this is part of AddRecord and populates worksheet with info from the 3 text box's and the 6 combo       '                                                                                               'box's

               End If
            Else
                If Action = xlGetRecord Then
                    .Value = CBool(LCase(wsData.Cells(RecordRow, i).Value) = "yes")       '  I believe this Basically checks the box's if the incoming data from work sheet is Checked
                Else
                     wsData.Cells(RecordRow, i).Value = IIf(.Value, "Yes", "No")                 'I think this is to save a yes or no to worksheet depending on value of check box.
                End If
            End If
        End With
    Next i
End Sub

' these are the 15 form controls, first 3 text box's, next 6 combo boxes and then  6 check box's

Function FormControls() As Variant
    FormControls = Array("Customer", "CSONumber", "JobNumber", _
                        "PCWeldType", "PCWeldGrind", "PCFinish", _
                        "NonPCWeld", "NonPCGrind", "NonPCFinish", _
                        "BRReview", "BOMReview", "DimReview", _
                        "WeldReview", "Apperance", "Complete")
End Function

Am I close?


thanks Bill

 

[Bill Williamson]

Sorry Bad example in post above. Data would be in A2:O2

 

[DanteAmor]

You are correct in all your comments.

If i < 10 Then ' I dont understand what this is for

In this part it means that from 1 to 9 (<10) are combos and textbox
And that does the treatment for those controls.

From 10 to 15 it makes the treatment for checkboxes.

 

[Bill Williamson]

You are correct in all your comments.



In this part it means that from 1 to 9 (<10) are combos and textbox
And that does the treatment for those controls.

From 10 to 15 it makes the treatment for checkboxes.

Thank you for your wisdom, I did realize that part after I posted.

But if I can pick your brain a little more,

Say there is only 1 check box, and its data is no longer in an adjoining column to the first 9 items.
Lets say its in column V. How would I modify the code to look in the new location and to put information in the new location.
I think I Need to give it a new range just for it, but Does it have to come out of the current loop for me to do it?

 

[Bill Williamson]

Sorry I basically, unintentionally got onto the same topic I have in a different post with that question.. and code is slightly different with some updates made
I guess disregard,
But thanks for verifying my train of thought was correct.

 

[DanteAmor]

But thanks for verifying my train of thought was correct.

Of course, let me know if you have any other questions. And thanks for the feedback.

 

[Bill Williamson]

Good morning, I have a hypothetical question on coding multiple ranges
Right now all columns the userform saves and gets information from are adjoining starting column A so range is ("wsData.Range("A:A"))
What if you had to split it, for example, It now gets and saves all the check box Data several columns down from the text and combo boxs
starting in column V Would you have to specify the range for the check boxs separately or could you add it like "wsData.Range("A:A,V:V"))

Sub AddGetRecord(ByVal Action As XLRecordActionType)

    Dim i As Integer

    Dim ControlsArr As Variant

  

    ControlsArr = FormControls



    If Action = xlAddRecord Then RecordRow = WorksheetFunction.CountA(wsData.Range("A:A"))  
  For i = 1 To 15                         ' This cycles through the 15 Controls
        With Me.Controls(ControlsArr(i))

            If i < 10 Then                         
                If Action = xlGetRecord Then

                    .Text = wsData.Cells(RecordRow, i).Value  'This gets the information from the worksheet and populates the 3 text box's and the 6 combo box's

                Else

                    wsData.Cells(RecordRow, i).Value = .Value    

               End If

            Else

                If Action = xlGetRecord Then

                    .Value = CBool(LCase(wsData.Cells(RecordRow, i).Value) = "yes")       '  checks the Checkbox's if the incoming data from work sheet is Checked

                Else

                     wsData.Cells(RecordRow, i).Value = IIf(.Value, "Yes", "No")                 'this is to save a yes or no to worksheet depending on value of check box.

                End If

            End If

        End With

    Next i

End Sub



' these are the 15 form controls, first 3 text box's, next 6 combo boxes and then  6 check box's



Function FormControls() As Variant

    FormControls = Array("Customer", "CSONumber", "JobNumber", _

                        "PCWeldType", "PCWeldGrind", "PCFinish", _

                        "NonPCWeld", "NonPCGrind", "NonPCFinish", _

                        "BRReview", "BOMReview", "DimReview", _

                        "WeldReview", "Apperance", "Complete")

End Function

 

[DanteAmor]

You can put each control in a column for each control.
You just have to change the logic of the code for a different structure to put the controls, for example: textbox1 in column A, combo1 in column B, check1 in column V, etc.

If you already have in which column each control should go, put the list here and help you adjust the macro.

 

[Bill Williamson]

All form control Text Box's go in columns A-I, this seems to be working
checkbox controls depending on which sub is being run it should get or put or update value in Columns V - AA

I think I understand what your saying.
We could set range areas using form control as an integer or set a range for each individual type of form control.

These Form Controls are text box's
("Customer", "CSONumber", "JobNumber", "PCWeldType", "PCWeldGrind", "PCFinish", "NonPCWeld", "NonPCGrind", "NonPCFinish")

These form controls are all check box's ("BRReview", "BOMReview", "DimReview","WeldReview", "Apperance", "Complete")

 

[DanteAmor]

If you want to write the checkbox starting in column V, this could be a solution:

In these lines add a + 13

        If Action = xlGetRecord Then
          .Value = CBool(LCase(wsData.Cells(RecordRow, i + 13).Value) = "yes")     '  checks the Checkbox's if the incoming data from work sheet is Checked
        Else
          wsData.Cells(RecordRow, i + 13).Value = IIf(.Value, "Yes", "No")               'this is to save a yes or no to worksheet depending on value of check box.
        End If

By the way, To add a record you need to add a + 1 on this line:

If Action = xlAddRecord Then RecordRow = WorksheetFunction.CountA(wsData.Range("A:A")) + 1