Loop through controls on UserForm using For Each Next Loop

[Maneesh Massey]

Hi,
Is there a way in Excel VBA to loop through and check if all controls (text boxes) on a userform are empty or not ?? I have 11 text boxes on a userform with names like: Arec1, Arec2, Arec3... Arec11. A msgbox should pop up and let the user know which text box he/she has left out and needs to be filled in before proceeding onto the next userform. Should this code be placed in the click event of a button on the same form ??
Appreciate any good answers.
~~ Maneesh

 

[Debraj]

Hi @Maneesh Massey
A sample file with user form is appreciable.. so that we dont have to create a form for you..

 

[Maneesh Massey]

Hi Debraj,
I've uploaded the file called UserformTest. Please see the form called frmAdd.
Thanks for reponding.
Regards,
Maneesh

 

[Debraj]

With as short as possible..

I have added few lines in .. UpdatePID
and each textbox change event.. calling that routine..

Dim j As Integer
    For j = 6 To 11
        If Trim(Me("Arec" & j).Text) = "" Then Exit For
    Next
    Me("cmdnext").Visible = j = 12

cmdNext.. will not visible untill you filled all text boxes..

 

[Maneesh Massey]

Thanks Devraj for this!
I want to know what the last line mean ?? Why is there 2 equal (=) signs in the same line ???

Me("cmdNext").Visible = j =12

 

[Debraj]

Something like..

Me("cmdNext").Visible = (if j = 12 then true, else false)

 

[Hui]

I usually set all the controls default values in the Userform_Initialize event

Private Sub UserForm_Initialize()
  Textbox1.Value = 10
  'Etc
End Sub

 

[Debraj]

@Hui
Thats a good idea..

 

[Maneesh Massey]

I usually set all the controls default values in the Userform_Initialize event

Private Sub UserForm_Initialize()
  Textbox1.Value = 10
  'Etc
End Sub

Hi Hui,
Please explain the meaning of the above line of code:

Private Sub UserForm_Initialize()
Textbox1.Text = 10
'Etc
End Sub

Same thing I ask here. What does the equal sign (=) after Textbox1.Text do ??? Can you please elaborate ??

Thanks and A VERY HAPPY NEW YEAR TO ALL MEMBERS & USERS OF THIS EXCELLENT FORUM !!!!!!!!!!!!!!!
Regards,
Maneesh

 

[Hui]

Maneesh

UserForm_Initialize() is the routine that runs when the User Form first opens
Putting code in here ensures it is run before any user interaction on the user form

Textbox1.Text = 10 will place the value 10 in the text box called TextBox1
You will have to do the same for your list/combo boxes etc that are on your userform