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

Excel Hero · Accepted Answer · 2020-03-15 21:17:00Z

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

edited Mar 15, 2020 at 21:17

answered Mar 15, 2020 at 20:23

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

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

6 Comments

Seddon016 Over a year ago

Thanks for pointing that out, I thought I may have bee able to just use the single index. Unfortunately I am still getting the error. Do you know of any other reasons this may happen?

Seddon016 Over a year ago

DataA(1, 0) = <Subscript out of range>

Excel Hero Over a year ago

You need to post more of the code. Or send me the workbook.

Seddon016 Over a year ago

I'm happy to send you the workbook. Do you have a preferred method to receive it?

Seddon016 Over a year ago

Thanks, I've sent that now. Hopefully the issue is relatively simple to solve.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related