View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default !!! Newbie VBA question about array's and listbox !!!

If I understand you correctly, you have a master sheet with all products
which you use to fill the listbox (lbxProduct)
Why not put the unit of measure in the adjacent column of this master sheet,
then load your listbox with both columns. You can hide the second column
(look in help on columnwidth) so you can't see it in the listbox (if you
desire), but still reference it in your code.

lblUnits.Caption = lbxProduct.List(lbxProduct.Listindex,1)

--
Regards,
Tom Ogilvy



"David Van Heuverswyn" wrote in message
...
Hi,

I don't know much about Excel VBA yet, but I'm working on it. I have this
problem I'm trying to solve for a few days now.
I have managed to set up a listbox (on a userform) that gets its values

from
the first worksheet of a different .xls file. The first worksheet of the

xls
file is a file of products. I'm just testing so there are only 3 products
(from A1 to A3). I have 1 product with the unit 'mg', 1 with 'ml' and 1

with
'µg'.
I have put the 'mg' product in the 2nd worksheet, the 'ml' product in the
3rd worksheet and the 'µg' product in the 4th worksheet.
The goal of the form is to let the user select the product he used from

the
listbox and type in the amount he used in a different textbox . This

textbox
is followed by a textlabel that changes from mg to ml or µg according to

the
selected product.
To make the latter work I've written some code for the 3 products (A,B,C).
When the form is initialized, the standard caption is 'mg'. This is the

code
I use:

Private Sub lbxProduct_Change()

If lbxProduct.Selected(1) Then
lblUnits.Caption = "ml"
ElseIf lbxProduct.Selected(2) Then
lblUnits.Caption = "µg"
End If

End Sub

It works fine, but I have about 300 products, and I don't want to write

the
above code for 300 products.
So I want to make 3 array's (array for 'mg' products, array for 'ml'
products...) and then see in which array the selected product has a match.
And the label should change according to the array the product is in.
I've attached all of the code I have so far.

Can someone please help me out here !!!

Many thanx in advance.

David