Listbox

[Greg Brow]

I am trying to setup a database/client file for my work and I am looking to
use a listbox or combo box to select the clients name and when selected to
open up their particular workbook.

Thanks Greg

 

[Leith Ross]

Hello Greg,

I prefer to use ComboBoxes with Database applications due to thei
flexibility and built in routines. The macro I have written loads th
Company and its workbook information from two columns on a worksheet
This way editiing stays simple. When the macro runs, it counts th
entries on the worksheet automatically and loads the ComboBox. Ther
are 2 parts to this code the macro and ComboBox_Click() event code.
have included both for you.

_________________________________________________________________

'ComboBox Click Event Code

Private Sub ComboBox1_Click()

Dim WkbName As String

If ComboBox1.ListIndex <> -1 Then
WkbName = ComboBox1.List(ComboBox1.ListIndex, 1)
Workbooks.Open (WkbName)
End If

End Sub
_________________________________________________________________

'Macro - Add a Module to your Project and paste this code in

Public Sub LoadComboBox()

'Set Properties
With ComboBox1
.BoundColumn = 1
.Clear
.ColumnCount = 2
.ColumnHeads = False
.ColumnWidths = "60;0"
.TextColumn = 1
End With

'Load Data into ComboBox from Worksheet Columns A and B
For I = 1 To R
C = I - 1
ColumnA = Worksheets("Sheet1").Cells(I, 1).Value
ColumnB = Worksheets("Sheet1").Cells(I, 2).Value
ComboBox1.AddItem
ComboBox1.Column(0, C) = ColumnA
ComboBox1.Column(1, C) = ColumnB
Next I
'Show First Entry
ComboBox1.ListIndex = 0

End Sub
_________________________________________________________________

Hope this helps,
Leith Ros

 

[Dick Kusleika]

Greg

First you need to get the data into the combo/listbox. Where is it? I'd
guess you want two columns in your control, one that contains the client's
name and one that contains the name/path of the workbook. See here for how
to populate multicolumn controls
http://www.dicks-blog.com/archives/2004/05/10/populating-multi-column-listboxcombobox/

Once you have that, the code to open a workbook is pretty simple

With Me.ListBox1
If .ListIndex > -1 Then
Workbooks.Open .List(.ListIndex-1,1)
Unload Me
End If
End With