Input data form with drop down list

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

Hi Pa

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"PA" <PA@discussions.microsoft.com> wrote in message news:D76A125E-A2C1-415E-97DB-F5A4A6D6B4D9@microsoft.com...
> 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
>
>
>
>
>
>
>
>
>
>
>
>

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

Dear Ron and Martin

Many thanks.It solve my problem.

Best Regards

PA

"PA" wrote:

> 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
>
>
>
>
>
>
>
>
>
>
>
>