Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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? -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
BlueGirl,
In the cell you want to trap the result, add Data Validation (men u DataValidation). 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) "bluegirl " wrote in message ... I have a sheet that one cell is configured to make a choice from the dropdown box containing the class names. The class names are very very long. I would like to create a pop up box that has a drop down list that I can make larger to accomodate the entire line of text. Trouble is, I don't know how to assign this to write the choice to the corresponding field on the spreadsheet. Can someone help me please?? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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 -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "bluegirl " wrote in message ... Maybe I need to clarify a little. The problem with making the cell wide enough to accomodate the entire line of text for the class name is that it would be way too long. Is it possible to create a userform box that will pop up for this cell and after they make their selection it will write it to the appropriate row? Once it is selected I have the cell set to word wrap so it will not take up as much room, it is just viewing the entire line of text that is the problem. Does that make sense? --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperate for help with VBA List Box
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "bluegirl " wrote in message ... I must be just dumb.... I can not seem to grasp how to actually make it all work. I have the commands in the VBA sheet, and I have the form set up the way I want... How do I make it actually come up when the cell is selected on the worksheet. ACK --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
STILL DESPERATE FOR HELP!!! | Excel Discussion (Misc queries) | |||
DESPERATE FOR HELP!!! | Excel Discussion (Misc queries) | |||
Desperate...please help! | Excel Worksheet Functions | |||
Desperate NEED!!!! | Excel Worksheet Functions | |||
Sorry, But Getting Desperate | Excel Programming |