Desperate for help with VBA List Box

B

bluegirl

I have a sheet that one cell is configured to make a choice from th
dropdown box containing the class names.

The class names are very very long. I would like to create a pop u
box that has a drop down list that I can make larger to accomodate th
entire line of text.

Trouble is, I don't know how to assign this to write the choice to th
corresponding field on the spreadsheet.

Can someone help me please?
 
B

Bob Phillips

BlueGirl,

In the cell you want to trap the result, add Data Validation (men u
Data>Validation). Change the Allow option to List), and the Source to the
range on your worksheet that has your class names). You can then make that
cell as wide as you like.

If the class names are on another sheet, you will need to create a workbook
name for that range and use that in DV.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

bluegirl

Maybe I need to clarify a little. The problem with making the cell wid
enough to accomodate the entire line of text for the class name is tha
it would be way too long.

Is it possible to create a userform box that will pop up for this cel
and after they make their selection it will write it to th
appropriate row?

Once it is selected I have the cell set to word wrap so it will no
take up as much room, it is just viewing the entire line of text tha
is the problem.

Does that make sense
 
D

Dick Kusleika

bluegirl

Yes, you can do that. First, make a userform with a listbox and a
commandbutton. In the Initialize event, put the classes in the listbox. I
don't know where your class list is, but here's an example

Private Sub UserForm_Initialize()

Dim cell As Range

For Each cell In Sheet2.Range("ClassList").Cells
Me.ListBox1.AddItem cell.Value
Next cell

End Sub

Then, in your commandbutton's click event, you need code like this

Private Sub CommandButton1_Click()

If Me.ListBox1.ListIndex >= 0 Then
ActiveCell.Value = Me.ListBox1.Value
End If

Unload Me

End Sub

Finally, to show the userform, use the Selection change event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$A$1" Then 'limit to A1
UserForm1.Show
End If

End Sub
 
B

bluegirl

I am going to play with that.... will let you know if it works. Sorr
but I really truly have NO experience with VBA except for what I hav
been guided in doing through the online forums! I have plans to take
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 ou
how to use the Dependent Lists and now have the fields for th
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 a
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 tha
cell, (J2 would be the first cell in the column) a form would pop u
instructing them to choose the correct class based on what they hav
chosen in the Product Category and they are given a list of classes t
choose from.

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

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

Dick Kusleika

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.
 
B

bluegirl

I must be just dumb.... I can not seem to grasp how to actually make i
all work.

I have the commands in the VBA sheet, and I have the form set up th
way I want...

How do I make it actually come up when the cell is selected on th
worksheet.

ACK:confused
 
D

Dick Kusleika

bluegirl

This is the part that shows the userform when a cell in column J is selected

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

End Sub

It goes in the code module behind the sheet, not in a standard module.
Right click on the sheet tab where you want this to happen and choose View
Code. This will take you to the proper code module for this sub.
 
Top