range as listbox column
 

Hi All,

Can't figure out how to use a column of data from a listbox as a range.

Hope you can provide some advice.

I have an inventory database (wkbk), each item has a primary key, person,
office, sn etc... A userform is used to transfer equipment from a
person/office to another.
A listbox is used to list the items for transfer, the second column of the
lstbx holds the "primary key" value. By matching the primary key of the lstbx
to the primary key of the ws, I wish to update/append that particular row
with data found in 3 comboboxes (new person, office, etc).

This is what I have so far. The error I receive is "Error: 381: Could not
get the column property. Invalid property array index." (rng =
lst_map_trans_to.Column(2))

Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Inventory")

For i = 0 To Me.lst_map_trans_to.ListCount - 1
Dim prkey As Variant
prkey = (lst_map_trans_to.List(i, 1) - 1)
Dim rng As Range
rng = lst_map_trans_to.Column(2)
For Each prkey In rng
Worksheets("mp").Range("j" & prkey) = cbx_map_office_to.Value
Worksheets("mp").Range("k" & prkey) = cbx_map_person_to.Value
Worksheets("mp").Range("l" & prkey) = cbx_map_loc_to.Value
Next
Next

Thanks for any and all advice.

range as listbox column
 

Add the Set statement at the beginning of the line...
Set rng = lst_map_trans_to.Columns(2).Cells
(note the addition of .Cells at the end and the s to Column)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"BigPig"
wrote in message
Hi All,

Can't figure out how to use a column of data from a listbox as a range.

Hope you can provide some advice.

I have an inventory database (wkbk), each item has a primary key, person,
office, sn etc... A userform is used to transfer equipment from a
person/office to another.
A listbox is used to list the items for transfer, the second column of the
lstbx holds the "primary key" value. By matching the primary key of the lstbx
to the primary key of the ws, I wish to update/append that particular row
with data found in 3 comboboxes (new person, office, etc).

This is what I have so far. The error I receive is "Error: 381: Could not
get the column property. Invalid property array index." (rng =
lst_map_trans_to.Column(2))

Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Inventory")

For i = 0 To Me.lst_map_trans_to.ListCount - 1
Dim prkey As Variant
prkey = (lst_map_trans_to.List(i, 1) - 1)
Dim rng As Range
rng = lst_map_trans_to.Column(2)
For Each prkey In rng
Worksheets("mp").Range("j" & prkey) = cbx_map_office_to.Value
Worksheets("mp").Range("k" & prkey) = cbx_map_person_to.Value
Worksheets("mp").Range("l" & prkey) = cbx_map_loc_to.Value
Next
Next

Thanks for any and all advice.

range as listbox column
 

Hi Jim,

Thank you! You're right that's all it needed.


"Jim Cone" wrote:


Add the Set statement at the beginning of the line...
Set rng = lst_map_trans_to.Columns(2).Cells
(note the addition of .Cells at the end and the s to Column)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"BigPig"
wrote in message
Hi All,

Can't figure out how to use a column of data from a listbox as a range.

Hope you can provide some advice.

I have an inventory database (wkbk), each item has a primary key, person,
office, sn etc... A userform is used to transfer equipment from a
person/office to another.
A listbox is used to list the items for transfer, the second column of the
lstbx holds the "primary key" value. By matching the primary key of the lstbx
to the primary key of the ws, I wish to update/append that particular row
with data found in 3 comboboxes (new person, office, etc).

This is what I have so far. The error I receive is "Error: 381: Could not
get the column property. Invalid property array index." (rng =
lst_map_trans_to.Column(2))

Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Inventory")

For i = 0 To Me.lst_map_trans_to.ListCount - 1
Dim prkey As Variant
prkey = (lst_map_trans_to.List(i, 1) - 1)
Dim rng As Range
rng = lst_map_trans_to.Column(2)
For Each prkey In rng
Worksheets("mp").Range("j" & prkey) = cbx_map_office_to.Value
Worksheets("mp").Range("k" & prkey) = cbx_map_person_to.Value
Worksheets("mp").Range("l" & prkey) = cbx_map_loc_to.Value
Next
Next

Thanks for any and all advice.

range as listbox column
 

You are welcome, the feedback is appreciated.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"BigPig"
wrote in message
Hi Jim,
Thank you! You're right that's all it needed.



"Jim Cone" wrote:
Add the Set statement at the beginning of the line...
Set rng = lst_map_trans_to.Columns(2).Cells
(note the addition of .Cells at the end and the s to Column)