Store multiple user form submissions to an array and store the array in a table

Question

I have seen the following question here: How to add multiple data rows at once from UserForm to Excel DataBase which appears to be a solution to my question.

However, I am experiencing some difficulties when trying to implement the above solution.

Background: I want to create a userform that allows me to add stock information such as: market, symbol, currency, price and quantity of stocks and add these to a table.

Attempt:

Here is the user form I have designed (very simple currently)

I would like to store multiple submissions and then add all submissions to the table.

Code

Module1

Public ws As Worksheet
Public DataA()

Userform Code

    '----Code to Set the dimension of the Data array
Private Sub UserForm1_Initialize()
    Dim DataA(5, 0)
    Set ws = ThisWorkbook.Sheets("Portfolio")
    '----Rest of your code
End Sub

'----Code to add a data set to the data array

'Button to add is called cmd_add
'Text Boxes are names: txt_SM, txt_Sy, txt_Cu, txt_Pr and txt_Qu
'These represent the desired headings: Stock Market, Symbol, Currency, Price and Quantity



Private Sub cmd_add_Click()

    DataA(1) = UserForm1.txt_SM
    DataA(2) = UserForm1.txt_Sy
    DataA(3) = UserForm1.txt_Cu
    DataA(4) = UserForm1.txt_Pr
    DataA(5) = UserForm1.txt_Qu

    ReDim Preserve DataA(LBound(DataA, 1) To UBound(DataA, 1), LBound(DataA, 2) To UBound(DataA, 2) + 1)
End Sub

However, after attempting to add a submission I get a "Subscript Out of Range Error". It appears to be an issue with the array DataA but I'm not sure where the error is. I have declared it as a public array in a separate module and have specified the dimensions in the initialisation step.

Any help on solving this issue and get working would be appreciated.

Thanks

Answer 1

In cmd_add_Click() you need to use 2 indexes for the array...

DataA(1, 0) = UserForm1.txt_SM

UPDATE

After seeing your workbook, I suggest you change your code to this...

Private Sub cmd_add_Click()

    ReDim d(1 To 1, 1 To 5)

    With UserForm1
        d(1, 1) = .txt_SM
        d(1, 2) = .txt_Sy
        d(1, 3) = .txt_Cu
        d(1, 4) = .txt_Pr
        d(1, 5) = .txt_Qu
    End With

    ThisWorkbook.Sheets("Portfolio").[a1].End(xlDown).Offset(1).Resize(, 5) = d

End Sub