Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm List Box, Excel 2000 & 2003
Hello,
I've created a UserForm with listboxes: ListBox1 loads all my open workbooks. ListBox2 loads all worksheets in selected workbook. ListBox3 loads data from WorkbookA starting with Range("C5") through the complete column and when I click on data in listbox3 it inserts data in ActiveCell of Selected Workbook. This is what I need to do with ListBox3 load data begin with Range("C5") through rest of rangeC but when I click on a select item in ListBox3 I need it to insert data in activecell the data from RangeC and RangeD data needs to go to the right of activecell. Example: RangeC5 RangeD5 AHU AIR HANDLING UNIT AIR COMP AIR COMPRESSOR AIR CURTAIN AIR CURTAIN AIR DRYER REFRIGERATED AIR DRYER AUTO WINDOW AUTOMATIC WINDOW OPENER AUTOCLAVE-EL AUTOCLAVES-ELECTRIC AUTOCLAVE-ST AUTOCLAVES-STEAM AUTO-DOOR-EL AUTO-DOOR UNITS-ELECTRIC AUTO-DOOR-HY AUTO-DOOR UNITS-HYDRAULIC BOILER, HW BOILERS HOT WATER BOILER, ST BOILER STEAM CAB HEATER CABINET HEATER If I select Air Comp it will insert in activecell then I need Air Compressor to insert to the right of activecell. Thnk you for your help, jfcby |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm List Box, Excel 2000 & 2003
Private Sub ListBox3_Click()
With ListBox3 ActiveCell = .List(.ListIndex, 0) ActiveCell.Offset(0, 1) = .List(.ListIndex, 1) End With End Sub Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm List Box, Excel 2000 & 2003
How do you load Listbox3?
If you use rowsource, then you can use the listindex to determine what row has been selected. With Workbooks("WorkbookA").Worksheets("1") set rng = .Cells(me.Listbox3.ListIndex + 5,"C") End with ActiveCell.Value = rng ActiveCell.offset(0,1).Value = rng.offset(0,1).Value If you use additem you will have to find where in your list the value is located. With Workbooks("WorkbookA").Worksheets("1") set rng = .Range(.Range("C5"),.Range("C5").End(xldown)) End with res = Application.Match(me.Listbox1.Value,rng.0) ActiveCell.Value = rng(res,1) ActiveCell.Offset(0,1).Value = rng(res,2) Easier might be to have a two column listbox (set the column width so the second column isn't visible) and load all the data you need. then write each column of the selected row With Me.Listbox3 ActiveCell.Value = .List(.ListIndex,0) ActiveCell.Offset(0,1).Value = .List(.ListIndex,1) End With "jfcby" wrote in message oups.com... Hello, I've created a UserForm with listboxes: ListBox1 loads all my open workbooks. ListBox2 loads all worksheets in selected workbook. ListBox3 loads data from WorkbookA starting with Range("C5") through the complete column and when I click on data in listbox3 it inserts data in ActiveCell of Selected Workbook. This is what I need to do with ListBox3 load data begin with Range("C5") through rest of rangeC but when I click on a select item in ListBox3 I need it to insert data in activecell the data from RangeC and RangeD data needs to go to the right of activecell. Example: RangeC5 RangeD5 AHU AIR HANDLING UNIT AIR COMP AIR COMPRESSOR AIR CURTAIN AIR CURTAIN AIR DRYER REFRIGERATED AIR DRYER AUTO WINDOW AUTOMATIC WINDOW OPENER AUTOCLAVE-EL AUTOCLAVES-ELECTRIC AUTOCLAVE-ST AUTOCLAVES-STEAM AUTO-DOOR-EL AUTO-DOOR UNITS-ELECTRIC AUTO-DOOR-HY AUTO-DOOR UNITS-HYDRAULIC BOILER, HW BOILERS HOT WATER BOILER, ST BOILER STEAM CAB HEATER CABINET HEATER If I select Air Comp it will insert in activecell then I need Air Compressor to insert to the right of activecell. Thnk you for your help, jfcby |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm List Box, Excel 2000 & 2003
On Feb 13, 9:00 am, "Tom Ogilvy" wrote:
How do you load Listbox3? If you use rowsource, then you can use the listindex to determine what row has been selected. With Workbooks("WorkbookA").Worksheets("1") set rng = .Cells(me.Listbox3.ListIndex + 5,"C") End with ActiveCell.Value = rng ActiveCell.offset(0,1).Value = rng.offset(0,1).Value If you use additem you will have to find where in your list the value is located. With Workbooks("WorkbookA").Worksheets("1") set rng = .Range(.Range("C5"),.Range("C5").End(xldown)) End with res = Application.Match(me.Listbox1.Value,rng.0) ActiveCell.Value = rng(res,1) ActiveCell.Offset(0,1).Value = rng(res,2) Easier might be to have a two column listbox (set the column width so the second column isn't visible) and load all the data you need. then write each column of the selected row With Me.Listbox3 ActiveCell.Value = .List(.ListIndex,0) ActiveCell.Offset(0,1).Value = .List(.ListIndex,1) End With "jfcby" wrote in message oups.com... Hello, I've created a UserForm with listboxes: ListBox1 loads all my open workbooks. ListBox2 loads all worksheets in selected workbook. ListBox3 loads data from WorkbookA starting with Range("C5") through the complete column and when I click on data in listbox3 it inserts data in ActiveCell of Selected Workbook. This is what I need to do with ListBox3 load data begin with Range("C5") through rest of rangeC but when I click on a select item in ListBox3 I need it to insert data in activecell the data from RangeC and RangeD data needs to go to the right of activecell. Example: RangeC5 RangeD5 AHU AIR HANDLING UNIT AIR COMP AIR COMPRESSOR AIR CURTAIN AIR CURTAIN AIR DRYER REFRIGERATED AIR DRYER AUTO WINDOW AUTOMATIC WINDOW OPENER AUTOCLAVE-EL AUTOCLAVES-ELECTRIC AUTOCLAVE-ST AUTOCLAVES-STEAM AUTO-DOOR-EL AUTO-DOOR UNITS-ELECTRIC AUTO-DOOR-HY AUTO-DOOR UNITS-HYDRAULIC BOILER, HW BOILERS HOT WATER BOILER, ST BOILER STEAM CAB HEATER CABINET HEATER If I select Air Comp it will insert in activecell then I need Air Compressor to insert to the right of activecell. Thnk you for your help, jfcby- Hide quoted text - - Show quoted text - Hello Tom, When I put part of your code in Userform it still only inserts the RangeC5 data in activecell only. How can I modify it to insert RangeC data in activecell and RangeD data in right cell? This is the code you provided if you need to see my complete UserForm code let me know. Private Sub UserForm_Initialize() Dim r As Range, c As Range Dim wb As Workbook With ListBox1 For Each wb In Workbooks 'If wb.Name < ThisWorkbook.Name Then .AddItem (wb.Name) 'End If Next wb .ListIndex = 0 End With With Sheets("Major_Category_MODIFY") Set r = .Range(.Range("C5"), .Range("C" & Rows.Count).End(xlUp)) For Each c In r ListBox3.AddItem c Next c End With End Sub Private Sub ListBox3_Click() With Me.ListBox3 ActiveCell.Value = .List(.ListIndex, 0) ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1) End With End Sub Thank you for your help, jfcby |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm List Box, Excel 2000 & 2003
I explained the conditions for each - you can't just do whatever and then
select whichever solution. You chose the solution for a two column Listbox, but you are not using a two column listbox. Private Sub UserForm_Initialize() Dim r As Range With Sheets("Major_Category_MODIFY") Set r = .Range(.Range("C5"), _ .Range("C" & Rows.Count).End(xlUp)) End With With ListBox3 .ColumnCount = 2 .ColumnWidths = .Width - 1 & ";0" .List = r.Resize(, 2).Value End With End Sub Private Sub ListBox3_Click() With Me.ListBox3 ActiveCell.Value = .List(.ListIndex, 0) ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1) End With End Sub worked fine for me. (as advertised) work in the code from my initialize event to your existing code (replace your code to populate listbox3) -- regards, Tom Ogilvy "jfcby" wrote in message ups.com... On Feb 13, 9:00 am, "Tom Ogilvy" wrote: How do you load Listbox3? If you use rowsource, then you can use the listindex to determine what row has been selected. With Workbooks("WorkbookA").Worksheets("1") set rng = .Cells(me.Listbox3.ListIndex + 5,"C") End with ActiveCell.Value = rng ActiveCell.offset(0,1).Value = rng.offset(0,1).Value If you use additem you will have to find where in your list the value is located. With Workbooks("WorkbookA").Worksheets("1") set rng = .Range(.Range("C5"),.Range("C5").End(xldown)) End with res = Application.Match(me.Listbox1.Value,rng.0) ActiveCell.Value = rng(res,1) ActiveCell.Offset(0,1).Value = rng(res,2) Easier might be to have a two column listbox (set the column width so the second column isn't visible) and load all the data you need. then write each column of the selected row With Me.Listbox3 ActiveCell.Value = .List(.ListIndex,0) ActiveCell.Offset(0,1).Value = .List(.ListIndex,1) End With "jfcby" wrote in message oups.com... Hello, I've created a UserForm with listboxes: ListBox1 loads all my open workbooks. ListBox2 loads all worksheets in selected workbook. ListBox3 loads data from WorkbookA starting with Range("C5") through the complete column and when I click on data in listbox3 it inserts data in ActiveCell of Selected Workbook. This is what I need to do with ListBox3 load data begin with Range("C5") through rest of rangeC but when I click on a select item in ListBox3 I need it to insert data in activecell the data from RangeC and RangeD data needs to go to the right of activecell. Example: RangeC5 RangeD5 AHU AIR HANDLING UNIT AIR COMP AIR COMPRESSOR AIR CURTAIN AIR CURTAIN AIR DRYER REFRIGERATED AIR DRYER AUTO WINDOW AUTOMATIC WINDOW OPENER AUTOCLAVE-EL AUTOCLAVES-ELECTRIC AUTOCLAVE-ST AUTOCLAVES-STEAM AUTO-DOOR-EL AUTO-DOOR UNITS-ELECTRIC AUTO-DOOR-HY AUTO-DOOR UNITS-HYDRAULIC BOILER, HW BOILERS HOT WATER BOILER, ST BOILER STEAM CAB HEATER CABINET HEATER If I select Air Comp it will insert in activecell then I need Air Compressor to insert to the right of activecell. Thnk you for your help, jfcby- Hide quoted text - - Show quoted text - Hello Tom, When I put part of your code in Userform it still only inserts the RangeC5 data in activecell only. How can I modify it to insert RangeC data in activecell and RangeD data in right cell? This is the code you provided if you need to see my complete UserForm code let me know. Private Sub UserForm_Initialize() Dim r As Range, c As Range Dim wb As Workbook With ListBox1 For Each wb In Workbooks 'If wb.Name < ThisWorkbook.Name Then .AddItem (wb.Name) 'End If Next wb .ListIndex = 0 End With With Sheets("Major_Category_MODIFY") Set r = .Range(.Range("C5"), .Range("C" & Rows.Count).End(xlUp)) For Each c In r ListBox3.AddItem c Next c End With End Sub Private Sub ListBox3_Click() With Me.ListBox3 ActiveCell.Value = .List(.ListIndex, 0) ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1) End With End Sub Thank you for your help, jfcby |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm List Box, Excel 2000 & 2003
On Feb 13, 3:40 pm, "Tom Ogilvy" wrote:
I explained the conditions for each - you can't just do whatever and then select whichever solution. You chose the solution for a two column Listbox, but you are not using a two column listbox. Private Sub UserForm_Initialize() Dim r As Range With Sheets("Major_Category_MODIFY") Set r = .Range(.Range("C5"), _ .Range("C" & Rows.Count).End(xlUp)) End With With ListBox3 .ColumnCount = 2 .ColumnWidths = .Width - 1 & ";0" .List = r.Resize(, 2).Value End With End Sub Private Sub ListBox3_Click() With Me.ListBox3 ActiveCell.Value = .List(.ListIndex, 0) ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1) End With End Sub worked fine for me. (as advertised) work in the code from my initialize event to your existing code (replace your code to populate listbox3) -- regards, Tom Ogilvy "jfcby" wrote in message ups.com... On Feb 13, 9:00 am, "Tom Ogilvy" wrote: How do you load Listbox3? If you use rowsource, then you can use the listindex to determine what row has been selected. With Workbooks("WorkbookA").Worksheets("1") set rng = .Cells(me.Listbox3.ListIndex + 5,"C") End with ActiveCell.Value = rng ActiveCell.offset(0,1).Value = rng.offset(0,1).Value If you use additem you will have to find where in your list the value is located. With Workbooks("WorkbookA").Worksheets("1") set rng = .Range(.Range("C5"),.Range("C5").End(xldown)) End with res = Application.Match(me.Listbox1.Value,rng.0) ActiveCell.Value = rng(res,1) ActiveCell.Offset(0,1).Value = rng(res,2) Easier might be to have a two column listbox (set the column width so the second column isn't visible) and load all the data you need. then write each column of the selected row With Me.Listbox3 ActiveCell.Value = .List(.ListIndex,0) ActiveCell.Offset(0,1).Value = .List(.ListIndex,1) End With "jfcby" wrote in message groups.com... Hello, I've created a UserForm with listboxes: ListBox1 loads all my open workbooks. ListBox2 loads all worksheets in selected workbook. ListBox3 loads data from WorkbookA starting with Range("C5") through the complete column and when I click on data in listbox3 it inserts data in ActiveCell of Selected Workbook. This is what I need to do with ListBox3 load data begin with Range("C5") through rest of rangeC but when I click on a select item in ListBox3 I need it to insert data in activecell the data from RangeC and RangeD data needs to go to the right of activecell. Example: RangeC5 RangeD5 AHU AIR HANDLING UNIT AIR COMP AIR COMPRESSOR AIR CURTAIN AIR CURTAIN AIR DRYER REFRIGERATED AIR DRYER AUTO WINDOW AUTOMATIC WINDOW OPENER AUTOCLAVE-EL AUTOCLAVES-ELECTRIC AUTOCLAVE-ST AUTOCLAVES-STEAM AUTO-DOOR-EL AUTO-DOOR UNITS-ELECTRIC AUTO-DOOR-HY AUTO-DOOR UNITS-HYDRAULIC BOILER, HW BOILERS HOT WATER BOILER, ST BOILER STEAM CAB HEATER CABINET HEATER If I select Air Comp it will insert in activecell then I need Air Compressor to insert to the right of activecell. Thnk you for your help, jfcby- Hide quoted text - - Show quoted text - Hello Tom, When I put part of your code in Userform it still only inserts the RangeC5 data in activecell only. How can I modify it to insert RangeC data in activecell and RangeD data in right cell? This is the code you provided if you need to see my complete UserForm code let me know. Private Sub UserForm_Initialize() Dim r As Range, c As Range Dim wb As Workbook With ListBox1 For Each wb In Workbooks 'If wb.Name < ThisWorkbook.Name Then .AddItem (wb.Name) 'End If Next wb .ListIndex = 0 End With With Sheets("Major_Category_MODIFY") Set r = .Range(.Range("C5"), .Range("C" & Rows.Count).End(xlUp)) For Each c In r ListBox3.AddItem c Next c End With End Sub Private Sub ListBox3_Click() With Me.ListBox3 ActiveCell.Value = .List(.ListIndex, 0) ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1) End With End Sub Thank you for your help, jfcby Hello All Responsers, Thank you for your help. Problem was solved with Tom's response. Tom your explaination was helpful but with my limited knowledge I was not able to get my listbox to work until your last response. Thank you for your help, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does Xcel 2000 have a list function like that in 2003 | Excel Worksheet Functions | |||
UserForm Question, Excel 2000 & 2003 | Excel Programming | |||
Does Excel 2000 have the "List" data function like in Excel 2003 | Excel Discussion (Misc queries) | |||
is it possible list box to a chart in excel 2003 as was in 2000? | Charts and Charting in Excel | |||
Linking userform to userform in Excel 2003 | Excel Programming |