Input data form with drop down list

[Guest]

Dear All,

I have data at sheet1 as follows

Material Date Qty UnitCost Total
AAA 1/01/2006 2 3 9
BBB 3/01/2006 3 4 16

I try to create the data input form with the following VB Code

Sub Inputform()

ActiveSheet.ShowDataForm

End Sub

The result if I run this macro ==> as Data Input FORM

-----------------------------------------------------------------------
Sheet 1
-----------------------------------------------------------------------

Material _______ New Record
Date: _______ New
Qty : _______ Delete
Cost : ________ Restore
Total : ________ Find Prev
Find Next
Criteria
Close

all ______ fields are to be input manually.If I wish to have Field Materail
at this data input form as a "Dropdown field" instead of we need input it
manually, but still as part of this data input form, is there any way by
using any VB code to create it?

Many thanks for any advise and help,

Best Regards

PA

 

[Ron de Bruin]

Hi Pa

See
http://www.j-walk.com/ss/dataform/index.htm

 

[martin]

Or try something like this. Sets the combo box's RowSource and
ControlSource properties to refer to named ranges in the workbook
"Book1". Interestingly, it appears you can't have RowSource and
ControlSource refer to ranges in an addin, only a regular workbook.

Private Sub UserForm_Initialize()

Dim Wb As Workbook
Dim CBRS, CBCS As String

Set Wb = Workbooks("Book1")
CBRS = Wb.Names("CBColors").RefersTo
CBRS = Right(CBRS, Len(CBRS) - 1)
CBCS = Wb.Names("CBColorValue").RefersTo
CBCS = Right(CBCS, Len(CBCS) - 1)
With Me.ComboBox1
.RowSource = CBRS
.ControlSource = CBCS
End With

End Sub

 

[Guest]

Dear Ron and Martin

Many thanks.It solve my problem.

Best Regards

PA