Combo box reference in VBA 1

[gcole]

I am an old VBA user in Access, but I have to start using it in Excel. How do I reference my combobox to load data in VBA?

 

[JustinEzequiel]

may help you get started...
from Excel help

The following example uses a range of worksheet cells in a ListBox and, when the user selects a row from the list, displays the row index in another worksheet cell. This code sample uses the RowSource, BoundColumn, and ControlSource properties.
To use this example, copy this sample code to the Declarations portion of a form. Make sure that the form contains a ListBox named ListBox1. In the worksheet, enter data in cells A1:E4. You also need to make sure cell A6 contains no data.

Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 5
ListBox1.RowSource = "a1:e4"

ListBox1.ControlSource = "a6"
ListBox1.BoundColumn = 0 'Place the ListIndex into cell a6
End Sub

Copyright(c) 1996 Microsoft Corporation.

 

[gcole]

Thanks. Can that also be done with an ADO data object. I would like to tie the fields to an access mdb. Also, do you know where I get the Calendar control files? I appreciate the help, this nudge should get me up and running.

 

[JustinEzequiel]

Will check if control could be bound to an access table. a bit busy now.

for the calendar control files, you could try

http://www.miesoftware.com/mscal.ocx

or

ftp://ftp.winswim.com/mscal.ocx

 

[kevinclark]

Hi gcole

Depending on how you wish to distribute your completed Workbook you may want to try using the controls from the Control Toolbar. Example; from your View ... ToolBars menu select Control ToolBox. Try using its ComboBox control. You can also customize this toolbar to include any Calendar Controls installed on your computer. Although I have never bound the ComboBox control to a RecordSet it can be populated directly with out need of first inserting the RecordSet values into cell ranges.

Example;

Sub Sample()
Dim dbSmpl as Database
Dim rsSmpl as Database
Dim strSQL as String
Dim WS as String 'WS = WorkSheet Name

WS = "Sheet1"
strSQL = "Select FName from Customers"
Set dbSmpl = OpenDatabase("Your Database Path & Name&quot
Set rsSmpl = dbSmpl.OpenRecordSet(strSQL)

Sheets(WS).ComboBox1.Clear

Do While rsSmpl.EOF = False
Sheets(WS).ComboBox1.Additem rsSmpl.Field("FName&quot.value
rsSmpl.MoveNext
Loop
End Sub


NOTE: Again if your planning to distribute the workbook among a variety of workstations that are running varying versions of Office then this solution may not be the best.