Userform multi textboxes data to only specific columns

[quintin]

Good day,
I have a userform that has 20+ textboxes that you can choose from (i know how to have it enter the data to a spreadsheet over x amount of columns) however what I would like to know is if I only pick three or four textboxes (eg: glass, mug, plate - price related (so now I've used 6 textboxes) i need them to be listed down in a spreadsheet - So choosing those items it will list them down in column a and b.

 

[Andrew Poulsom]

What are the names of your TextBoxes and how do those that you want to put in column A relate to those that you want to put in column B?

 

[quintin]

What are the names of your TextBoxes and how do those that you want to put in column A relate to those that you want to put in column B?

They are named textbox1, textbox2 and etc - So textbox1 you would enter a name eg: mug and in the textbox2 next to that the price

 

[Rick Rothstein]

Try using this code in whatever event procedure (probably a button click) you are using to process the data in the TextBoxes...

  Dim Index As Long, Cntrl As Control
  For Each Cntrl In Controls
    If TypeOf Cntrl Is MSForms.TextBox Then
      If Val(Replace(Cntrl.Name, "TextBox", "")) Mod 2 Then
        Index = Val(Replace(Cntrl.Name, "TextBox", ""))
        If Len(Cntrl.Text) Then
          If Len(Controls("TextBox" & Index + 1)) Then
            Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2) = Array(Cntrl.Text, Controls("TextBox" & Index + 1).Text)
          End If
        End If
      End If
    End If
  Next

 

[quintin]

Thank you so much, I was thinking could i change it that I use comboxes in col A and textboxes in col B...Using the comboxes would save typing time...just thinking - once again thx

 

[Rick Rothstein]

Thank you so much, I was thinking could i change it that I use comboxes in col A and textboxes in col B...Using the comboxes would save typing time...just thinking - once again thx

Assuming your ComboBoxes are named ComboBox1, ComboBox2, etc. and their corresponding TextBoxes are named TextBox1, TextBox2, etc., then give this a try...

  Dim Index As Long, Cntrl As Control
  For Each Cntrl In Controls
    If TypeOf Cntrl Is MSForms.ComboBox Then
      If Cntrl.ListIndex > -1 Then
        Index = Val(Replace(Cntrl.Name, "ComboBox", ""))
        If Len(Controls("TextBox" & Index)) Then
          Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2) = Array(Cntrl.Text, Controls("TextBox" & Index).Text)
        End If
      End If
    End If
  Next

 

[quintin]

Assuming your ComboBoxes are named ComboBox1, ComboBox2, etc. and their corresponding TextBoxes are named TextBox1, TextBox2, etc., then give this a try...

  Dim Index As Long, Cntrl As Control
  For Each Cntrl In Controls
    If TypeOf Cntrl Is MSForms.ComboBox Then
      If Cntrl.ListIndex > -1 Then
        Index = Val(Replace(Cntrl.Name, "ComboBox", ""))
        If Len(Controls("TextBox" & Index)) Then
          Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2) = Array(Cntrl.Text, Controls("TextBox" & Index).Text)
        End If
      End If
    End If
  Next

Hi there,
I tried the code and it came up with a message - "could not find the specified object

      If Len(Controls("TextBox" & Index)) Then

 

[Rick Rothstein]

Hi there,
I tried the code and it came up with a message - "could not find the specified object

      If Len(Controls("TextBox" & Index)) Then

Did you see where I wrote this...

"Assuming your ComboBoxes are named ComboBox1, ComboBox2, etc. and their corresponding TextBoxes are named TextBox1, TextBox2, etc"

Actually, though, the sequence of the numbers is unimportant... all that matters is that the number after the ComboBox matches the number after TextBox for the TextBox/ComboBox pairing.

 

[quintin]

Did you see where I wrote this...

"Assuming your ComboBoxes are named ComboBox1, ComboBox2, etc. and their corresponding TextBoxes are named TextBox1, TextBox2, etc"

Actually, though, the sequence of the numbers is unimportant... all that matters is that the number after the ComboBox matches the number after TextBox for the TextBox/ComboBox pairing.

I now understand why it is not working correctly - all the comboxes and textboxes are like it should be combox1 / textbox1 however I have to extra comboxes one for clients name (cmb_name) and the other method of payment named (cmb_method)
Not sure how to over come that
and two textbox name tb_date and tb_time

 

[Rick Rothstein]

I now understand why it is not working correctly - all the comboxes and textboxes are like it should be combox1 / textbox1 however I have to extra comboxes one for clients name (cmb_name) and the other method of payment named (cmb_method)
Not sure how to over come that
and two textbox name tb_date and tb_time

If those extra two ComboBoxes' names do not start with "ComboBox", then I do not see what the problem would be... they will be ignored by my code... only ComboBoxes and TextBoxes with the same ending numbers are looked at.