Creating an array from textbox/listbox

Question

I classify myself as a beginner in programing. I have a userform that first looks up a number presented by the user. Example 12345, 12346,12347. The number entered into the textbox is searched for and then added to the listbox as a valid number. After the user enters all the numbers needed, they should be able to click change and update the records accordingly.

Private Sub Change_Click()
Dim i As Long

For i = LBound(RunArray) To UBound(RunArray)
    ' Code to update each record, still working on it.
Next

End Sub

Private Sub RunNumber_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,     ByVal Shift As Integer)
Dim RunArray() As String
Dim RunCount As Integer
RunCount = 0

If KeyCode = 13 Then
    With Sheets("Sheet1").Range("A:A")
        Set RunFind = .Find(What:=RunNumber, _
                     After:=.Cells(.Cells.count), _
                     LookIn:=xlValues, _
                     LookAt:=xlWhole, _
                     SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, _
                     MatchCase:=False)
        If Not RunFind Is Nothing Then
            ReDim Preserve RunArray(RunCount)
            RunArray(RunCount) = RunNumber.Value
            RunNumberList.AddItem RunNumber.Value
            RunNumber.Value = ""
            RunCount = RunCount + 1
        Else
            MsgBox "The Run Number you entered was not found, Please the number and try again."
            RunNumber.Value = ""
        End If
    End With
End If

End Sub

braX · Accepted Answer · 2021-03-08 08:17:34Z

1

Private Sub CreateArrayFromListbox()
  Dim nIndex As Integer
  Dim vArray() As Variant

  ' dimension the array first instead of using 'preserve' in the loop
  ReDim vArray(ListBox1.ListCount - 1)
  
  For nIndex = 0 To ListBox1.ListCount - 1
    vArray(nIndex) = ListBox1.List(nIndex)
  Next
End Sub

edited Mar 8, 2021 at 8:17

answered Jun 9, 2017 at 12:21

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AmongTheShadows Over a year ago

first off, thank you for your help. as for the code i'm not entirely sure this need to either be combined or integrated into my existing code. would this actually be in with my change_click() or as an independent? again thank you for your help.

braX Over a year ago

It really depends on what you are trying to do... you can remove the 1st line and the last line, and put the remaining code in your change_click(), replacing what you have.. but it not really going to do anything until you add your own code after it to do whatever it is you are planning to do with the array. You will also need to change ListBox1 to the name of whatever you named your listbox.

M-- Over a year ago

If you provide some comments in your answer that'd be better.

Patrick Lepelletier Over a year ago

i'm pretty much sure you can do this without looping. Arr=Me.listbox1.list, something like that.If it doesn't work, check the dimensions of the array. Probably Arr needs to be a dynamic variant Array..

Patrick Lepelletier · Accepted Answer · 2017-06-10 10:09:49Z

0

i have an example how to do it with a combobox, (it's the same with a listbox, just change the name accordingly.

Option Explicit

Private Sub UserForm_Initialize()
Dim i&
Dim Arr()
With Me.ComboBox1
    For i = 1 To 1000
        .AddItem "Item " & i
    Next i

    Arr = .List

    .Clear
End With

For i = 0 To 999
    Debug.Print Arr(i, 0)
Next i

Erase Arr
End Sub

this is just a sample code, in real coding, you won't clear the combobox this early, or erase the array.

The results are shown in the immediate window (alt-F11 , and Ctrl-g).

Note : the array is 2 dimendionned, and 0 based (Option base 1, after Option Explicitcan make the whole module 1-based (arrays start at 1) ).

answered Jun 10, 2017 at 10:09

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

Creating an array from textbox/listbox

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related