Userform TextBoxes

[chriscorpion786]

Hi All,

I have a userform where the user inputs data for all the fields and part numbers.
Now , I have 10 Part Numbers, if the user has data for only 1 Part Number he should be able to click on Submit button and dump the data on the sheet, however if the user has multiple Part Numbers , he should be able to input more Part Numbers. How can I make sure that when the user inputs the Part Number ...the qty and discount should be filled as well for the respected part number. This should be applicable for all 10 Part Numbers. The Part Number text box is names as tbPartNo1...tbPartNo2 and so on.
The qty textbox is named as tbqty1....tbqty2...and so on
The Discount textbox is named as tbdis1....tbdisc2 and so on...
I don't know if I can run a loop over each Part Numbers or what is the alternative.
Appreciate your time and effort...the image is attached.

 

[dmt32]

Hi,
one way maybe would be to create a boolean function that you call when press submit button & if returns false, inform the user with message & exit the sub.

Not fully tested but place following code in standard module

Function AllComplete(ByVal Form As Object) As Boolean
    Dim i                       As Long, a As Long
    Dim Ctrl(1 To 2)            As Control
    Dim EntryValid(1 To 2)      As Boolean
   
   
    AllComplete = True
    For i = 1 To 10
      If Len(Form.Controls("tbPartNo" & i)) > 0 Then
        Set Ctrl(1) = Form.Controls("tbqty" & i): Set Ctrl(2) = Form.Controls("tbdisc" & i)
       
            For a = 1 To 2
                EntryValid(a) = IsNumeric(Ctrl(a).Value)
                Ctrl(a).BackColor = IIf(EntryValid(a), vbWhite, vbRed)
            Next a
       
        If Not (EntryValid(1) And EntryValid(2)) Then AllComplete = False
        End If
        Erase Ctrl: Erase EntryValid
    Next i
   
    If Not AllComplete Then MsgBox "Please Complete All Fields Shown in RED", 48, "Entry Required"
   
End Function

And place line of code below at the start of your submit button code

Private Sub cmdSubmit_Click()
    If Not AllComplete(Me) Then Exit Sub

'rest of code
End Sub

hopefully, this will do what you want

Dave

 

[chriscorpion786]

Hi,
one way maybe would be to create a boolean function that you call when press submit button & if returns false, inform the user with message & exit the sub.

Not fully tested but place following code in standard module

Function AllComplete(ByVal Form As Object) As Boolean
    Dim i                       As Long, a As Long
    Dim Ctrl(1 To 2)            As Control
   [COLOR=rgb(147, 101, 184)][B] Dim EntryValid(1 To 2)      As Boolean[/B][/COLOR]
[B]  [/B]
 
    AllComplete = True
    For i = 1 To 10
      If Len(Form.Controls("tbPartNo" & i)) > 0 Then
      [B][COLOR=rgb(147, 101, 184)]  Set Ctrl(1) = Form.Controls("tbqty" & i): Set Ctrl(2) = Form.Controls("tbdisc" & i)[/COLOR][/B]
     
            For a = 1 To 2
                EntryValid(a) = IsNumeric(Ctrl(a).Value)
                Ctrl(a).BackColor = IIf(EntryValid(a), vbWhite, vbRed)
            Next a
     
        If Not (EntryValid(1) And EntryValid(2)) Then AllComplete = False
        End If
       [B][COLOR=rgb(147, 101, 184)] Erase Ctrl: Erase EntryValid[/COLOR][/B]
    Next i
 
    If Not AllComplete Then MsgBox "Please Complete All Fields Shown in RED", 48, "Entry Required"
 
End Function

And place line of code below at the start of your submit button code

Private Sub cmdSubmit_Click()
    If Not AllComplete(Me) Then Exit Sub

'rest of code
End Sub

hopefully, this will do what you want

Dave

Thank you for the code, I haven't tried it as yet, will try it today. Before I do, could you please explain the code briefly. In particular, the highlighted ones in bold and purple color. Many thanks...

 

[dmt32]

Thank you for the code, I haven't tried it as yet, will try it today. Before I do, could you please explain the code briefly. In particular, the highlighted ones in bold and purple color. Many thanks...

In the Declarations, I have created two fixed length arrays each with two elements.

One is to hold objects (textboxes) the other, Boolean (true /false) values

An array is a single variable that can be defined with many elements that allows you to store values of same data type and work well when using For Next Loops in code. In this case, both arrays are single dimension & only need two elements but could easily be multi dimension.

Set statement is needed to assign an object reference to each element of the array – this creates a reference to the object (your qty & disc textboxes)

Code Uses Two For Next Loops.

Outer Loop - to cycle through all 10 of your part no textboxes

Inner Loop - to initialize the object array to correct textbox object.

The second array is used to verify that correct data has been entered in each textbox

EntryValid(a) = IsNumeric(Ctrl(a).Value)

In this case we are checking for numeric values - each element of the array will hold a value of either True or False

The values stored in second array are then tested here

Ctrl(a).BackColor = IIf(EntryValid(a), vbWhite, vbRed)

To change backcolor of the control

And here

If Not (EntryValid(1) And EntryValid(2)) Then AllComplete = False

if one or both elements are False, the Function is set to False



Erase statement does just that, it erases the values in all the elements of the specified array.



Since posting, I tidied code up little

Function AllComplete(ByVal Form As Object) As Boolean
    Dim i                       As Long, a As Long
    Dim ctrl(1 To 2)            As Control
    Dim EntryValid(1 To 2)      As Boolean
    
    
    AllComplete = True
    For i = 1 To 10
      If Len(Form.Controls("tbPartNo" & i)) > 0 Then
      
            For a = 1 To 2
                Set ctrl(a) = Form.Controls(Choose(a, "tbqty", "tbdisc") & i)
                EntryValid(a) = IsNumeric(ctrl(a).Value)
                 With ctrl(a)
                    .BackColor = IIf(EntryValid(a), vbWhite, vbRed)
                    .ControlTipText = IIf(EntryValid(a), "", "Please Enter " & _
                                          Choose(a, "Qty Amount", "Discount Amount"))
                 End With
            Next a
        
            If Not (EntryValid(1) And EntryValid(2)) Then AllComplete = False
        End If
        Erase ctrl: Erase EntryValid
    Next i
    
    If Not AllComplete Then MsgBox "Please Complete All Fields Shown in RED", 48, "Entry Required"
    
End Function

Dave

 

[chriscorpion786]

Thank you so much for the explanation, I customized the code accordingly to my needs.
Learnt a couple of new things in the process..
??

 

[dmt32]

Thank you so much for the explanation, I customized the code accordingly to my needs.
Learnt a couple of new things in the process..
??

most welcome - appreciate feedback

Dave