Pulling Userform inputs in specified order

[business_analyst]

Hello All,

So I am trying to pull several textboxes and a combobox from a userform onto an excel sheet. Here is my code:

Sub form_inputs()
    Dim ctl As Control
    Dim count As Integer
    
    For Each ctl In frm_01.Controls
        count = count + 1
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            Sheet5.Cells(count, 1) = ctl.Value
        End If
    Next ctl
    
    MsgBox ("Inputs transferred")
End Sub

This works, however, it is not pulling values from each textbox in the order that I would like. I am not sure if this is because of the count variable I am using or simply because of the logic involved in how ctl is used within the For loop. Is there a way I can specify a top down approach, where each textbox value is pulled in order from the first text box to the last?

Along with the order, there are large gaps in the worksheet, for instance the first 5 textbox values were pulled into cells A1:A5 as they should be, then there are about 8 empty cells before the other textbox values are show. I just dont understand the reasoning behind why this is happening.

 

[Norie]

You can't rely on For Each to loop in any specific order.

You should try and find some other way of looping/identifying the textboxes.

eg using their names

The reason for the gaps is because you are increasing the count for every control on the form not just the comboboxes/textboxes.

 

[Jon von der Heyden]

Hi

For starters try putting the counter within the If block:

If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
    [B]count = count + 1[/B]        
    Sheet5.Cells(count, 1) = ctl.Value
End If

Have you changed the names of the controls? What are the names, from top to bottom?

 

[business_analyst]

Yea, I actually just realized the count + 1 issue after I posted the question , so I adjusted my code just like von der Heyden mentioned and put it within the if statement. This did eliminate the gaps, but I guess as Norie mentioned, the For loop doesn't have any specified order. The names are based on textbox content more so than order. Basically the first few are:

txt_01_company
txt_02_address
txt_03_zip..

so on and so forth, but not all follow this particular format. I guess I have to find another way to loop it other than a For loop.

 

[Jon von der Heyden]

I was thinking 1 of two ways:

1. Store the names in an ordered array, loop thru the array and refer to the control by the array item value.
2. Follow a numbered naming convention, e.g: (Ctrl1,Ctrl2,Ctrl3,Ctrl4...) Then use your counter to refer to your control.

 

[business_analyst]

I was thinking 1 of two ways:

1. Store the names in an ordered array, loop thru the array and refer to the control by the array item value.
2. Follow a numbered naming convention, e.g: (Ctrl1,Ctrl2,Ctrl3,Ctrl4...) Then use your counter to refer to your control.

Thanks von der Heyden, I also briefly thought about using an array, but was unsure of exactly how to do this. I am probably not as familiar with arrays as I should be. Is an ordered array different than other arrays? By any chance would you be able to provide me with a short example of how I would create an array involving a textbox name? Any help would be greatly appreciated.

 

[Norie]

An ordered array is just an array that's well in some kind of order.

You could create one like this.

arrCtrls = Array("Textbox1", "Textbox2", "Combobox1", "Combobox2")
 
' then loop through it like this
 
For I = Lbound(arrCtrls) To Ubound(arrCtrls)
 
       Msgbox Controls(arrCtrls(I)).Value
 
Next I

 

[mikerickson]

Another approach would be to save the appropriate row number in the control's .Tag property

Private Sub UserForm_Initialize()
    Rem assign row numbers to controls
    With Me
        .txt_Name.Tag = 4
        .txt_Address.Tag = 5
        .txt_Phone.Tag = 8
        .cbx_District.Tag = 2
    End With
End Sub

Sub DataToSheet()
    Dim ctl As Control
    For Each ctl In frm_01.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            Sheet5.Cells(ctl.Tag, 1) = ctl.Value
        End If
    Next ctl
End Sub

 

[business_analyst]

Thanks guys, I think I'm going to have to incorporate both of those good ideas!! In the end, I actually am going to be pulling data from several Userforms to a worksheet, and also be able to re-populate these Userforms from that worksheet. Soooo, the arrays and the tags are probably going to help a lot. Thanks for all the help

 

[Jon von der Heyden]

Neat idea Mike

Another approach would be to save the appropriate row number in the control's .Tag property

Private Sub UserForm_Initialize()
    Rem assign row numbers to controls
    With Me
        .txt_Name.Tag = 4
        .txt_Address.Tag = 5
        .txt_Phone.Tag = 8
        .cbx_District.Tag = 2
    End With
End Sub
 
Sub DataToSheet()
    Dim ctl As Control
    For Each ctl In frm_01.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            Sheet5.Cells(ctl.Tag, 1) = ctl.Value
        End If
    Next ctl
End Sub