UserForm List Box, Excel 2000 & 2003

[jfcby]

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

 

[merjet]

Private Sub ListBox3_Click()
With ListBox3
ActiveCell = .List(.ListIndex, 0)
ActiveCell.Offset(0, 1) = .List(.ListIndex, 1)
End With
End Sub

Hth,
Merjet

 

[Tom Ogilvy]

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]

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











- 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

 

[Tom Ogilvy]

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)

 

[jfcby]

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)

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