CODEWITHSUNDEEP
arraysvbaexcellistbox
AmongTheShadows
AmongTheShadows

Reputation: 29

Creating an array from textbox/listbox

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

Upvotes: 0

Views: 2414

Answers (2)

braX
braX

Reputation: 11755

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

Upvotes: 1

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

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

Upvotes: 0

Related Questions