19496

Excel vba list box multi column multi row from 14 text boxes

Question:

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 arr2).

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 ReDim Preserve.

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).

<strong>Note</strong>

As you changed code in OP, I assume tb0, tb1, ... correspond to enumerated TextBox controls. (<em>Please change the somewhat bizarre order in the controls Array arr1 to your needs.</em>)

<strong>Example code</strong>

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>.

Recommend

  • C# adding click events to a list box
  • simple rx code silently fails in windows forms only during debugging in visual studio 2010
  • Multi Column Listbox
  • Securing web server against MITM attack in Safari
  • C# How to Display Processes with Icons in ListBox
  • Passing parameters in the Form constructor, winforms c#
  • nginx, upstream, cors fail
  • Android: playing audio files in /res/raw by file name
  • UglifyJS2 removes wanted comments at the end of a block or file
  • TabBarController: Orienting views in different orientations
  • Writing Unittest for generic classes… best approach?
  • How can integers (keys & values) be added and retrieved from a NSDictionary
  • FluentMigrator Failed Migrations Don't Rollback?
  • How to add new index numbers to the upsampled data while preserving the orginal indices one
  • Microsoft Chart Controls for Microsoft .NET Framework 4.0
  • Catching exception and throwing the same?
  • Write output of for loop to multiple files
  • Plotting densities in R
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • C: Incompatible pointer type initializing
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • d3 v4 drag and drop with TypeScript
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • JQuery Internet Explorer and ajaxstop
  • Database structure design with variable amounts of fields
  • Is there a javascript serializer for JSON.Net?
  • How do I fake an specific browser client when using Java's Net library?
  • Opengl-es onTouchEvents problem or a draw problem? [closed]
  • How to get a value (ex: baseURL) in every Karate feature?
  • Where to put my custom functions in Wordpress?
  • Weird JavaScript statement, what does it mean?
  • Buffer size for converting unsigned long to string
  • How to set the response of a form post action to a iframe source?
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Linking SubReports Without LinkChild/LinkMaster
  • Checking variable from a different class in C#
  • Django query for large number of relationships
  • reshape alternating columns in less time and using less memory
  • Binding checkboxes to object values in AngularJs