!!! 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
|