Inserting Values in a Multidimensional Array from Two Different Controls

[shivboy]

Hi,

I'm using a userform to capture data and there on display them in various cells in a sheet. For storing the data before displaying in the cells, I am using a multidimensional array. Now, the problem I am facing is that I have a combobox and a listbox whose values I want to keep on adding into the multidimensional array till the user clicks the submit button. The code I am using is :

Dim arr() As String
Dim oName As String
Dim oJob As String
Dim cName As Integer
cName = lbColNames.ListCount
oName = txtColName.Value
oJob = cbJob.Value
If cbJob.Value = "" Then
    MsgBox "Please select a Job."
    cbJob.SetFocus
Else
    lbColNames.AddItem (oName)
    ReDim Preserve arr(cName, cName)
    arr(cName, cName) = oName
    arr(cName, cName + 1) = oJob
End If

But this generates an error "Subscript out of range". What wrong am I doing here? And how to insert values from 2 different controls into one multidimensional array? Please help.

Peace,

Shivboy

[XLGibbs]

The subscript out of range error means you are referring to objects not on the referenced sheet. Make sure you identify the proper worksheet or userform to reference with the code..

[Norie]

Shivboy

You can only redimension the last dimension of a multi dimensional array.

Do you actually need the array in the first place?

[shivboy]

Hi Norie,

Let me explain what I am trying to do. I want to add the name of an employee (taken from a textbox) and his job type (taken from a combo box) and display the name of the employee in a listbox. Now, since the job type is not being displayed in the listbox or anywhere else, I need to store it against the concerned employee name in a multi dimensional array. Why I am chosing the multi dimensional array is because I don't know how many names and their corresponding job types would be inserted. Finally, all this data will have to be stored in MS Access.

Do you think this is not a good idea to do so? Please help.

Peace,

Shivboy

Shivboy

You can only redimension the last dimension of a multi dimensional array.

Do you actually need the array in the first place?

[Norie]

I don't see how you are using the array to populate the listbox.

Can't you just use AddItem and Column?

[shivboy]

I think there has been a miscommunication on my part. I am not using the array to populate the listbox. The listbox is getting populated using the AddItem property. What I am trying to do is to simultaneously populate the array which would hold the values inserted in the listbox (the employee name) and the corresponding job type from the combo box. For this purpose I want to use a multi dimensional array. For example,

arr(i,j) = arr("John","Marketing")

While this is done I am also populating the listbox for user reference.

Finally, the data from this array will be fetched and inserted into a table using SQL query.

I hope this clears the objective.

I don't see how you are using the array to populate the listbox.

Can't you just use AddItem and Column?

[mdmackillop]

Hi Shivboy,
As Norie says, you can only redim the last dimension. Your array could be "horizontal", rather than "vertical", and your code something like
[VBA]
Dim arr() As String
Dim oName As String
Dim oJob As String
Dim cName As Integer
ReDim arr(2, 100)
cName = lbColNames.ListCount
oName = txtColName.Value
oJob = cbJob.Value
If cbJob.Value = "" Then
MsgBox "Please select a Job."
cbJob.SetFocus
Else
lbColNames.AddItem (oName)
ReDim Preserve arr(2, cName)
arr(0, cName) = oName
arr(1, cName + 1) = oJob
End If

[/VBA]

If necessary, the array can be adjusted using the Transpose worksheet function.
Regards
MD