Hi All,
I got a new project in which I am stuck up at one place. Rest all work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will be
added to the sheet called Production and will be processed further to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows on the
userform, then control the height of the userform so that only the number of
controls necessary are visible. You can certainly add controls at runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest all work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will be
added to the sheet called Production and will be processed further to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

This is a userfrom which works along with macros in the bas module and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it varies.
This is where the problem is. Also the height of form is limited to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message ...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows on the
userform, then control the height of the userform so that only the number of
controls necessary are visible. You can certainly add controls at runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest all work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will be
added to the sheet called Production and will be processed further to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

This is a userfrom which works along with macros in the bas module and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it varies.
This is where the problem is. Also the height of form is limited to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows on the
userform, then control the height of the userform so that only the number
of
controls necessary are visible. You can certainly add controls at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest all work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will be
added to the sheet called Production and will be processed further to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

Either you want to put 30 rows of controls on the userform or you don't. I
didn't suggest 30 rows of controls.

I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set of
one listbox and one text box for each record.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
This is a userfrom which works along with macros in the bas module and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it varies.
This is where the problem is. Also the height of form is limited to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows on the
userform, then control the height of the userform so that only the
number of
controls necessary are visible. You can certainly add controls at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest all work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will be
added to the sheet called Production and will be processed further to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

Tom,
May be i didn't explained my need properly. If it is one time effort, I will
do it but I dont know really how many records will be entered each time. So
is it possible to, say,
-add listbox and textbox controls for 5 records and a 2 command buttons
-so when there is more then 5 records each time user clicks on command
button1
- command button1 will hide the filled up boxes and puts blank boxes
- at the end, when user clicks command button2 to end his data entry, all
the boxes will be looped
and data will be added to the sheet.

Thanks for your reply.
Regards,
Madiya



"Tom Ogilvy" wrote in message
...
Either you want to put 30 rows of controls on the userform or you don't.
I
didn't suggest 30 rows of controls.

I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set
of
one listbox and one text box for each record.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
This is a userfrom which works along with macros in the bas module and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it varies.
This is where the problem is. Also the height of form is limited to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows on
the
userform, then control the height of the userform so that only the
number of
controls necessary are visible. You can certainly add controls at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest all
work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select
the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will
be
added to the sheet called Production and will be processed further
to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record
or
he may enter more then 30 recored at a time. I have to place a set
of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

the normal approach is to provide one set of input controls. The user enters
the data and clicks a button. The single record is entered and the controls
are cleared ready to accept the next record. When the user is done, then
they click a cancel or done button to drop the userform.

If you want to do something besides that, then perhaps John Walkenbach's
page on creating a userform dynamically will give you some hints.

http://j-walk.com/ss/excel/tips/tip76.htm

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
...
Tom,
May be i didn't explained my need properly. If it is one time effort, I
will
do it but I dont know really how many records will be entered each time.
So
is it possible to, say,
-add listbox and textbox controls for 5 records and a 2 command buttons
-so when there is more then 5 records each time user clicks on command
button1
- command button1 will hide the filled up boxes and puts blank boxes
- at the end, when user clicks command button2 to end his data entry, all
the boxes will be looped
and data will be added to the sheet.

Thanks for your reply.
Regards,
Madiya



"Tom Ogilvy" wrote in message
...
Either you want to put 30 rows of controls on the userform or you don't.
I
didn't suggest 30 rows of controls.

I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a set
of
one listbox and one text box for each record.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
This is a userfrom which works along with macros in the bas module and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it varies.
This is where the problem is. Also the height of form is limited to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows on
the
userform, then control the height of the userform so that only the
number of
controls necessary are visible. You can certainly add controls at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest all
work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to select
the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes will
be
added to the sheet called Production and will be processed further
to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one record
or
he may enter more then 30 recored at a time. I have to place a set
of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will
also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

Other approaches along my original suggestion and your concept of having 30
sets of boxes, would be to use a multipage control with 5 records per page
(each page as a visible property) or use frames stacked on top of each other
(each with five records), then have only one frame visible at a time.

--
regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
the normal approach is to provide one set of input controls. The user
enters
the data and clicks a button. The single record is entered and the
controls
are cleared ready to accept the next record. When the user is done, then
they click a cancel or done button to drop the userform.

If you want to do something besides that, then perhaps John Walkenbach's
page on creating a userform dynamically will give you some hints.

http://j-walk.com/ss/excel/tips/tip76.htm

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
...
Tom,
May be i didn't explained my need properly. If it is one time effort, I
will
do it but I dont know really how many records will be entered each time.
So
is it possible to, say,
-add listbox and textbox controls for 5 records and a 2 command buttons
-so when there is more then 5 records each time user clicks on command
button1
- command button1 will hide the filled up boxes and puts blank boxes
- at the end, when user clicks command button2 to end his data entry,
all
the boxes will be looped
and data will be added to the sheet.

Thanks for your reply.
Regards,
Madiya



"Tom Ogilvy" wrote in message
...
Either you want to put 30 rows of controls on the userform or you
don't.
I
didn't suggest 30 rows of controls.

I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a
set
of
one listbox and one text box for each record.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
This is a userfrom which works along with macros in the bas module
and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it
varies.
This is where the problem is. Also the height of form is limited to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in rows
on
the
userform, then control the height of the userform so that only the
number of
controls necessary are visible. You can certainly add controls at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest
all
work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the date
against the predefined items. I have placed the listbox to
select
the
items and another textbox to enter the data. when user press the
button "Add records" the data entered by him in the textboxes
will
be
added to the sheet called Production and will be processed
further
to
create PO and work orders.

I have successfully run the macro for fixed no of records. But I
failed to do it dynamically. I mean user may enter only one
record
or
he may enter more then 30 recored at a time. I have to place a
set
of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions will
also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

Thanks Tom,
I will try both the options and will let you know the results.
Although, I never used frames, it may take a while to first understand how
it works.

Regards,
Madiya


"Tom Ogilvy" wrote in message
...
Other approaches along my original suggestion and your concept of having
30
sets of boxes, would be to use a multipage control with 5 records per page
(each page as a visible property) or use frames stacked on top of each
other
(each with five records), then have only one frame visible at a time.

--
regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
the normal approach is to provide one set of input controls. The user
enters
the data and clicks a button. The single record is entered and the
controls
are cleared ready to accept the next record. When the user is done,
then
they click a cancel or done button to drop the userform.

If you want to do something besides that, then perhaps John Walkenbach's
page on creating a userform dynamically will give you some hints.

http://j-walk.com/ss/excel/tips/tip76.htm

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
...
Tom,
May be i didn't explained my need properly. If it is one time effort,
I
will
do it but I dont know really how many records will be entered each
time.
So
is it possible to, say,
-add listbox and textbox controls for 5 records and a 2 command
buttons
-so when there is more then 5 records each time user clicks on command
button1
- command button1 will hide the filled up boxes and puts blank boxes
- at the end, when user clicks command button2 to end his data entry,
all
the boxes will be looped
and data will be added to the sheet.

Thanks for your reply.
Regards,
Madiya



"Tom Ogilvy" wrote in message
...
Either you want to put 30 rows of controls on the userform or you
don't.
I
didn't suggest 30 rows of controls.

I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place a
set
of
one listbox and one text box for each record.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
This is a userfrom which works along with macros in the bas module
and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the relative
data for the selected item. Morover the records are not 30, it
varies.
This is where the problem is. Also the height of form is limited
to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in
rows
on
the
userform, then control the height of the userform so that only
the
number of
controls necessary are visible. You can certainly add controls
at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place. Rest
all
work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the
date
against the predefined items. I have placed the listbox to
select
the
items and another textbox to enter the data. when user press
the
button "Add records" the data entered by him in the textboxes
will
be
added to the sheet called Production and will be processed
further
to
create PO and work orders.

I have successfully run the macro for fixed no of records. But
I
failed to do it dynamically. I mean user may enter only one
record
or
he may enter more then 30 recored at a time. I have to place a
set
of
one listbox and one text box for each record. How to do this?

While a sample code is more welcome, even plain directions
will
also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya

Just rubberband the frame on the form, then put the controls on the frame.
Draw another one on top of it and do the same and so forth. Or setup one,
then copy it.

--
Regards,
Tom Ogilvy


"Madiya" wrote in message
...
Thanks Tom,
I will try both the options and will let you know the results.
Although, I never used frames, it may take a while to first understand how
it works.

Regards,
Madiya


"Tom Ogilvy" wrote in message
...
Other approaches along my original suggestion and your concept of having
30
sets of boxes, would be to use a multipage control with 5 records per
page
(each page as a visible property) or use frames stacked on top of each
other
(each with five records), then have only one frame visible at a time.

--
regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
the normal approach is to provide one set of input controls. The user
enters
the data and clicks a button. The single record is entered and the
controls
are cleared ready to accept the next record. When the user is done,
then
they click a cancel or done button to drop the userform.

If you want to do something besides that, then perhaps John
Walkenbach's
page on creating a userform dynamically will give you some hints.

http://j-walk.com/ss/excel/tips/tip76.htm

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
...
Tom,
May be i didn't explained my need properly. If it is one time
effort,
I
will
do it but I dont know really how many records will be entered each
time.
So
is it possible to, say,
-add listbox and textbox controls for 5 records and a 2 command
buttons
-so when there is more then 5 records each time user clicks on
command
button1
- command button1 will hide the filled up boxes and puts blank boxes
- at the end, when user clicks command button2 to end his data
entry,
all
the boxes will be looped
and data will be added to the sheet.

Thanks for your reply.
Regards,
Madiya



"Tom Ogilvy" wrote in message
...
Either you want to put 30 rows of controls on the userform or you
don't.
I
didn't suggest 30 rows of controls.

I mean user may enter only one record or
he may enter more then 30 recored at a time. I have to place
a
set
of
one listbox and one text box for each record.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
This is a userfrom which works along with macros in the bas
module
and
code behind the sheets and code behind the userform and command
buttons placed on the userform. Your suggession for 30 rows of
controls in rows is difficult as i have other controls which is
connected to the listbox of item by vlookup and shows the
relative
data for the selected item. Morover the records are not 30, it
varies.
This is where the problem is. Also the height of form is limited
to
the screen height.
I hope there are other ways also.
Anyway, thanks for reply.

Regards,
Madiya

"Tom Ogilvy" wrote in message
...
Is this a userform or controls on a worksheet.

If a userform, you can place all your 30 rows of controls in
rows
on
the
userform, then control the height of the userform so that only
the
number of
controls necessary are visible. You can certainly add
controls
at
runtime,
but this seems more complex than necessary.

--
Regards,
Tom Ogilvy

"Madiya" wrote in message
om...
Hi All,
I got a new project in which I am stuck up at one place.
Rest
all
work
is complete and works fine. The problem is as below.

In my application (VBA Project), the user has to enter the
date
against the predefined items. I have placed the listbox to
select
the
items and another textbox to enter the data. when user press
the
button "Add records" the data entered by him in the
textboxes
will
be
added to the sheet called Production and will be processed
further
to
create PO and work orders.

I have successfully run the macro for fixed no of records.
But
I
failed to do it dynamically. I mean user may enter only one
record
or
he may enter more then 30 recored at a time. I have to place
a
set
of
one listbox and one text box for each record. How to do
this?

While a sample code is more welcome, even plain directions
will
also
be appreciated.
I am using win2000 with office2000 if that helps.
Thanks,
Madiya