For Each c with Do Loop - Help

[bong25]

Hi again,

I have this code:

Dim mc As Control
Dim hasselect As Boolean
hasselect = False
Dim cbT
Dim Z
Z = 0

For Each mc In Me.Controls
If TypeName(mc) = "TextBox" Then
    Do Until Z = 13
        If mc.ControlTipText = "Required" And Not IsEmpty(mc) Then
        Z = Z + 1
    Loop
ElseIf TypeName(mc) = "CheckBox" And mc.Value = True Then
    hasselect = True
    cbT.Value = mc.Caption
Exit Sub
End If
Next mc

If Not Z = 13 Then
MsgBox "Following required fields must be filled in:" & vbCrLf & _
       "1. Year" & vbCrLf & _
       "2. Date" & vbCrLf & _
       "3. Stock Quantity (Tons)" & vbCrLf & _
       "4. Stock Quantity (BDTons)" & vbCrLf & _
       "5. Project Name" & vbCrLf & _
       "6. Project Date" & vbCrLf & _
       "7. Project Quantity" & vbCrLf & _
       "8. Supplier's Name" & vbCrLf & _
       "9. Supplier's Price" & vbCrLf & _
       "10. Supplier's Date" & vbCrLf & _
       "11. Proposed Price"
                    
ElseIf Z = 13 Then
    LRowCll.Offset(1, 0).Value = Application.WorksheetFunction.Max(rng) + 1
    LRowCll.Offset(1, 1).Value = TextBox1.Value
    LRowCll.Offset(1, 2).Value = TextBox2.Value
    LRowCll.Offset(1, 3).Value = cbT.Value
    LRowCll.Offset(1, 4).Value = TextBox3.Value
    LRowCll.Offset(1, 5).Value = TextBox4.Value
    LRowCll.Offset(1, 6).Value = TextBox5.Value
    LRowCll.Offset(1, 7).Value = TextBox6.Value
    LRowCll.Offset(1, 8).Value = TextBox7.Value
    LRowCll.Offset(1, 9).Value = TextBox10.Value
    LRowCll.Offset(1, 10).Value = TextBox11.Value
    LRowCll.Offset(1, 11).Value = TextBox8.Value
    LRowCll.Offset(1, 12).Value = TextBox9.Value
    LRowCll.Offset(1, 13).Value = TextBox12.Value
    LRowCll.Offset(1, 14).Value = TextBox13.Value
    LRowCll.Offset(1, 15).Value = TextBox14.Value
    LRowCll.Offset(1, 16).Value = TextBox15.Value
    LRowCll.Offset(1, 17).Value = TextBox16.Value
    LRowCll.Offset(1, 18).Value = TextBox17.Value
    LRowCll.Offset(1, 19).Value = TextBox18.Value
    LRowCll.Offset(1, 20).Value = TextBox19.Value
    LRowCll.Offset(1, 21).Value = TextBox20.Value
    LRowCll.Offset(1, 22).Value = TextBox21.Value
    LRowCll.Offset(1, 23).Value = TextBox22.Value
    LRowCll.Offset(1, 24).Value = TextBox23.Value
    LRowCll.Offset(1, 25).Value = TextBox24.Value
    LRowCll.Offset(1, 26).Value = TextBox25.Value
    LRowCll.Offset(1, 27).Value = TextBox26.Value
    LRowCll.Offset(1, 28).Value = TextBox27.Value
    LRowCll.Offset(1, 29).Value = TextBox28.Value
    LRowCll.Offset(1, 30).Value = ComboBox1.Value
    
ElseIf hasselect = False Then
    MsgBox "Come on! Please THICK One!"

End If

It show "Compile Error: Loop without Do"

With my little brain, I could not do it properly, I spend already week to try so many approach and can't get things to work.

Please help.

 

[Richard Schollar]

Would you mind explaining in words what the code is meant to do? I expect there are more efficient ways of doing what you are attempting and it would help anyone reading if you could provide a full explanation

 

[bong25]

Would you mind explaining in words what the code is meant to do? I expect there are more efficient ways of doing what you are attempting and it would help anyone reading if you could provide a full explanation

Thanks so much for quick reply,

In UserForm there are TextBoxes and CheckBoxes.

In CheckBoxes one must selected, and In 30 TextBoxes 13 TextBoxes must fill in, I have Put a Word "Required" in TextBox ControlTipText as a required textBox to fill in.

I hope it's clear now,

Thanks again.

 

[Richard Schollar]

Does it matter if more than 13 textboxes are populated? Or more than one checkbox checked?

 

[bong25]

It doesn't as long as the 13 TextBoxes with ControlTipText.Value = "Required" are filled in, the user can fill the whole 30 TextBoxes and only 1(one) CheckBox requires to select.

 

[Andrew Poulsom]

You are missing an End If before Loop.

 

[bong25]

You are missing an End If before Loop.

Hi,

Thanks I will check now.

 

[bong25]

Now I have this message:

Run-time error '438': Object doesnt support this proprty or method.

It is pointing at "ElseIf TypeName(mc) = "CheckBox" And mc.Value = True Then"

 

[Jon von der Heyden]

Try mc.Object.Value

 

[bong25]

Hi,

I did try that and the error stays the same.

Thanks for reply.