Naming and looping userform control items

[aalansari]

I'm trying to make my VBA code less cryptic and easier to debug by renaming my control items with unique names that make sense. For example, instead of "Textbox1" use "FirstName". Unfortunately, I lose the convenience of using loops to automate certain processes (like the code below).

'Clear all entries
For i = 1 To 83
    Controls("Textbox" & i).Value = ""
Next i

Is there a solution where I could use unique names for my controls and still be able to loop through them?

I thought I could create a table with all the names of my control items and just loop through that list...but I wanted to see if there was a better option first.

Thanks!

 

[Fluff]

Are you trying to set all the textboxes to "", or just some of them?

 

[aalansari]

Are you trying to set all the textboxes to "", or just some of them?

Both. I posted the code snippet just as an example of how I would loop through them. In some cases, I want to copy values from a range of textboxes to a sheet while in other cases I populate textboxes with values from the sheet.

 

[Fluff]

If you preface the controls with (for instance) txtb like txtbFirstName or txtbLastName then you could use

Dim Ctrl As Control
For Each Ctrl In Me.Controls
   If LCase(Ctrl.Name) Like "txtb*" Then
      Ctrl.Value = ""
   End If
Next Ctrl

 

[dmt32]

I thought I could create a table with all the names of my control items and just loop through that list...but I wanted to see if there was a better option first.

I refer to controls on a complex userform using a table that also includes additional checks for user entry requirements with error message prompts, data type & things like tab order, & which column data is posted to. This is read in to an an array which you can loop through

Dave

example

Control Name	Required	Data Type	Custom Message	Parent	Tab Order	Data Column
txtForename	Yes	Text	Please enter the Forename	Frame1	0	2
txtSurname	Yes	Text	Please enter the Surname	Frame1	1	3
txtDOB	Yes	Date	Please Enter date of DOB	Frame1	2	4
txtJobTitle	Yes	Text	Please enter the Job Title	Frame1	3	5
txtManager	Yes	Text	Please enter the site manager name	Frame1	4	6
txtSiteCode	Yes	Text	Please enter the site code	Frame1	5	7
txtPhoneNumber	Yes	Numeric	Please enter contact phone number	Frame1	6	8

 

[aalansari]

I refer to controls on a complex userform using a table that also includes additional checks for user entry requirements with error message prompts, data type & things like tab order, & which column data is posted to. This is read in to an an array which you can loop through

Dave

example

Control Name	Required	Data Type	Custom Message	Parent	Tab Order	Data Column
txtForename	Yes	Text	Please enter the Forename	Frame1	0	2
txtSurname	Yes	Text	Please enter the Surname	Frame1	1	3
txtDOB	Yes	Date	Please Enter date of DOB	Frame1	2	4
txtJobTitle	Yes	Text	Please enter the Job Title	Frame1	3	5
txtManager	Yes	Text	Please enter the site manager name	Frame1	4	6
txtSiteCode	Yes	Text	Please enter the site code	Frame1	5	7
txtPhoneNumber	Yes	Numeric	Please enter contact phone number	Frame1	6	8

This is what I had in mind, but I didn't think I could do all of that using this approach!

So, if you wanted to make sure that the user entered a Numeric value in a textbox, do you have it loop through all textboxes after update?

 

[aalansari]

If you preface the controls with (for instance) txtb like txtbFirstName or txtbLastName then you could use

Dim Ctrl As Control
For Each Ctrl In Me.Controls
   If LCase(Ctrl.Name) Like "txtb*" Then
      Ctrl.Value = ""
   End If
Next Ctrl

Thanks! This works.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dmt32]

This is what I had in mind, but I didn't think I could do all of that using this approach!

So, if you wanted to make sure that the user entered a Numeric value in a textbox, do you have it loop through all textboxes after update?

I do all the checks at time user presses the Enter or Submit button - You only need to loop once doing all the data checks & at same time, populate an array to output to range if all complete.

Dave

 

[aalansari]

I do all the checks at time user presses the Enter or Submit button - You only need to loop once doing all the data checks & at same time, populate an array to output to range if all complete.

Dave

Gotcha