Code runs out of line & edit advice needed please

[ipbr21054]

The partial code below needs some advice help please.
On a userform the user completes textbox values but in this example when he runs the code on commandbutton1 he is asked which row the data should be inserted on the worksheet.
Then he is told all fields needs to be completed, he didnt add a value to say 1 textbox.

I am trying to swap it around so the code check that all fields are complete THEN as which row values should be inserted otherwise just dont ask that question until ALL fields are complete.

I did move the code in Red BEFORE the row question but kept getting an error message,simple but im not winning on this one.

 Dim i As Integer
 Dim ControlsArr As Variant, ctrl As Variant
 Dim x As Long
 Dim z As Integer
 
 z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
 For i = 1 To 11
 With Me.Controls("TextBox" & i)
 If .Text = "" Then
 MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
 .SetFocus
 Exit Sub
 End If
 End With
 Next i

 

[Micron]

If 11 textboxes are empty and you click, you will get 11 prompts. One way

 Dim i As Integer
 Dim ControlsArr As Variant, ctrl As Variant
 Dim x As Long 'why this if not being used?
 Dim z As Integer 'why this if not being used?
 
 z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
 For i = 1 To 11
    If Me.Controls("Textbox" & i) = "" Then intCount = intCount + 1
Next
If intCount > 0 Then
    MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
    Exit Sub
End If

There is a more elegant way to do this if interested. It involves putting a Tag property value (e.g. Req'd), then looping over the controls and check the tag. If a control has a tag and it's not filled in, then concatenate the control names and provide a list in one message box. Access has the ability to use the attached label so that the message lists "Department" (if that's what the label says) instead of "txtbox3" but Excel can't do this. So another line would be needed (11 for you) to get a meaningful name for the missing value.

EDIT - you should always id which line causes the error and reveal the message. It's not always obvious to the reader. Also, please use code VBA tags, not BB code tags.

 

[Micron]

Access has the ability to use the attached label so that the message lists "Department" (if that's what the label says) instead of "txtbox3" but Excel can't do this.

I guess the work-around would be to "link" a label to a textbox by giving it the same number as the textbox. So if Textbox1 was for "Department" then Label1 caption should be "Department". Assuming all 11 textboxes need a value you can do it like this (untested):

Dim i As Integer, z As Integer
Dim ControlsArr As Variant, ctrl As Variant
Dim x As Long
Dim strMsg As String

z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
For i = 1 To 11 'loop over the 11 textboxes
    ' if textbox(i) = "" then add "- " and label(i) caption and line wrap to variable
    If Me.Controls("TextBox" & i) = "" Then strMsg = strMsg & "- " & Me.Controls("Label" & i).Caption & vbCrLf
Next
    
If strMsg  <> "" Then 'at least 1 txtbox ="" so there is 1 or more captions in strMsg
    'prepend a Please statement and concatenate the list of label captions for the missing values
    strMsg = "Please enter values for " & vbCrLf & strMsg
    MsgBox strMsg, 48, "GRASS NEW CUSTOMER FORM"
    Exit Sub
 End If