Using arrays in a userform

[Roderick O'Regan]

I have an Excel file which is a database of names and addresses. It
consists of 16 columns of data and 20 rows for each office.

In my Word template I have a userform with, among other things, a
dropdown list and two command buttons.

One button Retrieves the first nine fields in the database and
displays these on the form for the user to check the correct details
have been selected based on the dropdown list. I have no problems with
this part.

On retrieval, Fields 10 to 16 are put into an array variant, like
this:
VarData(0) = rs.Fields(9).Value
VarData(1) = rs.Fields(10).Value
VarData(2) = rs.Fields(11).Value
VarData(3) = rs.Fields(12).Value
VarData(4) = rs.Fields(13).Value
VarData(5) = rs.Fields(14).Value
VarData(6) = rs.Fields(15).Value

I have checked and each element of the array is holding its correct
information.

What I don't know how to do is to pass the data that was in the array
under the "Retrieve" button so that it can be put on the paper when I
press the "OK" button.

Each piece of the array would be concatenated with a vbCr prior to
going on the page.

Also, if there was no data (Null) in the last field then I would
discard it from the operation.

Can anyone help, please?

Roderick

 

[Doug Robbins - Word MVP]

Why not just put all 16 fields in the dropdown list. You can set the width
of the columns in the list to 0 so that the data in the fields will not
appear. Then you can deal with all of the fields in the same way.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Roderick O'Regan]

Thanks for the reply, Doug.

Because prior to this extra requirement everything was working
perfectly well and all data was going to their correct locations.

I didn't fancy re-inventing the wheel so thought of an array to hold
all of the "unseen" elements.

They are there, and retrieved, but I cannot seem to fathom out how to
maipulate it.

Roderick

 

[Doug Robbins - Word MVP]

Something like

Dim i as Long
Dim mystr as String
mystr = ""
For i = 0 to 5
mystr = mystr & VarData(i) & vbCr
Next i
If VarData(6) <> "" then
mystr = mystr & VarData(6)
End If



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP