Select all data in column and list in form combo box

[burl_rfc]

I'm having a spot of trouble with the following code, I would like to
list all the records in column A I tried to do the following,
ListItems = SourceWB.Worksheets(1).Range(.Range("A2"), _
.Range("A65536").End(xlUp)).Value
to select all the records in column A but it doesn't work, any idea
why.

Once I've selected the record of choice from the combo box, would it be
relatively easy to look at the same row in the source workbook, but
offset 1 column (column B) and depending upon it's data do a Do Case
for further processing.

For example lets say that column A contains part numbers, the part
numbers will be populated into the combo box, once I select the part
number of choice from the combo box, I want to look at the
corresponding row but offset 1 column (column B). In column B would be
product types, now depending upon the product type I would likely do a
Do Case to run further processing.

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook

With Me.ComboBox1
.Clear ' remove existing entries from the combobox
' turn screen updating off,
' prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("C:\Folder Name\Source
Workbook.xls", _
False, True)

ListItems = SourceWB.Worksheets(1).Range("A1:A5").Value

' get the values you want
SourceWB.Close False ' close the source workbook without saving
changes
Set SourceWB = Nothing
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the
first item
Application.ScreenUpdating = True
End With
End Sub

Thanks
burl_rfc

 

[Dave Peterson]

This worked ok for me:

Option Explicit

Private Sub CommandButton1_Click()
Dim SourceWB As Workbook
If Me.ComboBox1.ListIndex > -1 Then
Set SourceWB = Workbooks.Open("book3.xls", False, True)
MsgBox SourceWB.Worksheets(1).Range("A1") _
.Offset(Me.ComboBox1.ListIndex, 1)
SourceWB.Close savechanges:=False
End If
End Sub

Private Sub UserForm_Initialize()
Dim SourceWB As Workbook
With Me.ComboBox1
.Clear
Set SourceWB = Workbooks.Open("book3.xls", False, True)
.List = SourceWB.Worksheets(1).Range("A1:A5").Value
SourceWB.Close False
End With
End Sub

I think I'd think about keeping that file open.

 

[burl_rfc_h]

Dave,

Thanks for your reply.

I'm confused with the first sub routinue, what does listindex do and
also what does the msgbox line do?

In the second sub routine it looks like the list will only be a1:a6,
how can this be expanded to inclue all records in column A.

Thanks
brl_rfc_h

 

[Dave Peterson]

#1. From your original code:

..ListIndex = -1 ' no items selected, set to 0 to select the first item

When you select an item from the combobox, .listindex will give you the index
into that list (0 for the first item, 1 for the second, ... .listcount -1 for
the last).

From your code, you were using A1:A5.

I'm not sure I'd use the whole column (64k options!), but maybe something like
this that stops at the last used cell in column A:

Option Explicit
Private Sub CommandButton1_Click()
Dim SourceWB As Workbook
If Me.ComboBox1.ListIndex > -1 Then
Set SourceWB = Workbooks.Open("book3.xls", False, True)
MsgBox SourceWB.Worksheets(1).Range("A1") _
.Offset(Me.ComboBox1.ListIndex, 1)
SourceWB.Close savechanges:=False
End If
End Sub

Private Sub UserForm_Initialize()
Dim SourceWB As Workbook
Dim myRng As Range

With Me.ComboBox1
.Clear
Set SourceWB = Workbooks.Open("book3.xls", False, True)
With SourceWB.Worksheets(1)
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
.List = myRng.Value
SourceWB.Close False
End With
End Sub


The msgbox was just my way of showing that the value could be retrieved based on
your selection in the combobox.

You could use a variable and do whatever you want with it:

Private Sub CommandButton1_Click()
Dim SourceWB As Workbook
Dim myVar As Variant 'String/Long/double???
If Me.ComboBox1.ListIndex > -1 Then
Set SourceWB = Workbooks.Open("book3.xls", False, True)
myVar = SourceWB.Worksheets(1).Range("A1") _
.Offset(Me.ComboBox1.ListIndex, 1)
SourceWB.Close savechanges:=False
End If

'do whatever you want with myvar
MsgBox myVar
End Sub

 

[burl_rfc_h]

Dave,

Again thanks for your reply, your explaination of listindex helps
greatly.

Please correct me if my next assumption is incorrect.
Once the item is selected in the combo box, I would then press the
command button this should display the item selected as myVar in the
MsgBox.
When I ran this I got the basic Excel Message with an Okay button, is
this correct, should it not display the item selected?

Lastly, once I've selected the item from the combo box, how can I then
offset by 1 column to see whats in column B, I need this to determine
what the product type would be for the corresponing part number from
the same row in column A. Ultimately I'll then use the Do Case scenario
to initialize a specific form for the product type, this would allow me
to then re-populate the specific user form with data from the
corresponding columns on the row returned by the listindex for the part
number selected..

Ex. (I've over simplfied things blow to explain further)

Column A (A1 = Part Number)
a2 = apples
a3 = oranges
a4 = potatoes
a5 = carrots

Column B (B1 = Product Type)
b2 = fruit
b3 = fruit
b4 = vegatable
b5 = vegatable


Thanks
burl_rfc_h

 

[Dave Peterson]

Not quite right.

MyVar retrieves the value in the column to the right.

myVar _
= SourceWB.Worksheets(1).Range("A1").Offset(Me.ComboBox1.ListIndex, 1).value

(I forgot the .value portion--but it's the default property, so it didn't matter
in this case.)

The .offset(x,y) from A1 consists of two parts.

X=listindex--the index into the list.
(0 is the top of the list (a1), 1 is the 2nd (A2), ...

The y=1 tells excel to move one column to the right.

So you should be able to use:

Select case lcase(myvar)
case is = "fruit"
'do something
case is = "vegetable"
'do something else
end select

==========
But even better, you can put two columns in your combobox (and hide the
rightmost):

Option Explicit
Private Sub CommandButton1_Click()

Dim myVar As Variant 'String/Long/double???

With Me.ComboBox1
If .ListIndex > -1 Then
myVar = .List(.ListIndex, 1) '<-- second column!
MsgBox myVar 'for testing only
Select Case LCase(myVar)
Case Is = "fruit"
'do fruit
Case Is = "vegetable"
'do veggies
End Select
End If
End With
End Sub

Private Sub UserForm_Initialize()
Dim SourceWB As Workbook
Dim myRng As Range

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
Set SourceWB = Workbooks.Open("book3.xls", False, True)
With SourceWB.Worksheets(1)
Set myRng = .Range("A1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
.List = myRng.Value
SourceWB.Close False
End With
End Sub