bluegirl

Everything I told you is still good, just a few changes. Now in the
initialize event, you need to select which range has the cells you want.
Let's say I2 contains the product cat. and J2 will contain the class name.
To make things easier, you should name all your ranges on MASTER LIST the
same as the product category to which they relate. Make the range name
EXACTLY the same as the product category. If you have a category named
General Studies, then name the appropriate range of class names General
Studies. Then you can use code like this to populate the listbox.

Private Sub UserForm_Initialize()

Dim cell As Range
Dim Rng as Range

Set Rng = Sheets("MASTER LIST").Range(Activcell.Offset(0,1).Value)

For Each cell In Rng.Cells
Me.ListBox1.AddItem cell.Value
Next cell

End Sub

Whatever is in I2 will be range that this code looks at to get the classes.
Since your classes are going in J, change the Selection Change event to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = Me.Range("J:J").Column Then
UserForm1.Show
End If

End Sub

Play around with it and post back if you have any questions.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"bluegirl " wrote in message
...
I am going to play with that.... will let you know if it works. Sorry
but I really truly have NO experience with VBA except for what I have
been guided in doing through the online forums! I have plans to take a
class but that isn't going to help me today! LOL

While I was waiting on a reply to my question, I managed to figure out
how to use the Dependent Lists and now have the fields for the
spreadsheet set up for them.

Ideally, here is how I would love to see it work:

My class lists are on a sheet called MASTER LIST.

Each Product has a list of classes underneath which are named as
ranges.

When a user is putting data in, once they choose the product category,
they then go to the column for Course Name. When they select that
cell, (J2 would be the first cell in the column) a form would pop up
instructing them to choose the correct class based on what they have
chosen in the Product Category and they are given a list of classes to
choose from.

Again, sorry to appear so stupid, but it's only because I am VBA
dumb... I have a real interest in learning this, but it is pretty
confusing to someone new!

Thanks so much for the input... I will play with the previous
instructions til I hear back from you.


---
Message posted from http://www.ExcelForum.com/