I have user form with 14 text boxes , 2 command buttons " next " , " Post " and 1 list box
I need code to get the data from the 14 text boxes to the list box , again when the user enter new data and press next this data added to second row in the list box , gain , again
finally when he press post all data move to work sheet " Database"
Sub CommandButton1_Click() Dim arr1, i As Long Dim arr2(0 To 0, 0 To 13) arr1 = Array(TB10, TB10, TB0, tb1, cb1, cb2, tb5, tb4, TB10, TB10, TB10, tb6, tb7, tb8) For i = 0 To UBound(arr1) arr2(0, i) = arr1(i) Next i ListBox1.List = arr2 End Sub
but this code is only add one time data to list box , i need to add more rows ♥Answer1:
<strong><em>"...Need to add more rows"</em></strong>
Normally you would assign a complete(d) data set to the
.List property of your
ListBox1 (you chose to Name it
As you want to increase the number of contained element rows with each
CommandButton1_Click() event and preserve all existing data, theoretically you'd need increment the 1st dimension of a 2-dimensional array - but that's not possible using
To overcome this issue, simply reverse dimensions of
arr2 thus defining your 14 column values in its first dimension and the "row" dimension as 2nd one. A listbox control offers a
.Column property which you can use instead of the usual
.List property to write the whole data set back (without need to care of the intentionally transposed rows & columns).
As you changed code in OP, I assume
tb1, ... correspond to enumerated TextBox controls. (<em>Please change the somewhat bizarre order in the controls Array
arr1 to your needs.</em>)
Option Explicit ' declaration head of userform code module Dim arr2() ' make arr2 values disponible for each CommandButton1_Click event Sub CommandButton1_Click() ' declare/assign variables Dim arr1(), i As Long, nxt As Long arr1 = Array(tb0, tb1, tb2, tb3, tb4, tb5, tb6, tb7, tb8, tb9, tb10, tb11, tb12, tb13) ' <~~ Change order to your needs ' define index of next row in listbox nxt = Me.ListBox1.ListCount ' ListCount automatically counts upper bound + 1 ' a) you can only increment an array's last dimension, so ... ' b) redefine arr2 with new elements in its 2nd dimension ReDim Preserve arr2(0 To UBound(arr1), 0 To nxt) ' assign textbox and combobox values to arr2 For i = 0 To UBound(arr1) arr2(i, nxt) = arr1(i) Next i ' reassign arr2 to the listboxes .Column property (instead of the .List property) ListBox1.Column = arr2 End Sub Private Sub UserForm_Layout() With Me.ListBox1 .ColumnCount = 14 ' define column count .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50" ' <~~ change to your needs ' .Width = 50 * .ColumnCount + 16 End With End Sub
Allow me a remark: I think this answers your original question. You'll find enough examples how to move data back to a worksheet reading StackOverflow site, but this would need to formulate a new question with code showing what you've tried so far - see <a href="https://stackoverflow.com/help/mcve" rel="nofollow">How to create a Minimal, Complete, and Verifiable example</a>.