Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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 -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=34582 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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/2...stboxcombobox/ 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 -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Greg Brow wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |