Hi all
Hoping someone will be able to point me in the right direction.

Am creating a spreadsheet that a records new client data and enters it on sheet 1,via a userform.
From sheet 2 onwards are individual client acounts.
Question 1
on userform2 i need to create a list box from the client info on sheet 1, as this is a dynamic range, i am not sure of the coding for this, is this possible?
Question 2
On completion of data on form2, all data to be posted out to respective client accounts,not sure of the coding for this?
Question 3
Is it possible that on entry of a new client, a new client accound sheet could be created?

Many thanks in advance for any direction with this
Steve

use a defined name for the source for the listbox.

Insert = Name = Define

Name: List1
Refersto: =Offset(ClientInfo!A1,0,0,countA(ClientInfo!A:A),1 )


http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data




http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel
--
Regards,
Tom Ogilvy

"steve_doc" wrote in message
...
Hi all
Hoping someone will be able to point me in the right direction.

Am creating a spreadsheet that a records new client data and enters it on
sheet 1,via a userform.
From sheet 2 onwards are individual client acounts.
Question 1
on userform2 i need to create a list box from the client info on sheet 1,
as this is a dynamic range, i am not sure of the coding for this, is this
possible?
Question 2
On completion of data on form2, all data to be posted out to respective
client accounts,not sure of the coding for this?
Question 3
Is it possible that on entry of a new client, a new client accound sheet
could be created?

Many thanks in advance for any direction with this
Steve

thanks Tom, but all i get when trying to enter that is "Invalid Property Value"

"Tom Ogilvy" wrote:

In the properties window of the Listbox or Combobox, under the rowsource
property, type in List1

that works for me.

--
Regards,
Tom Ogilvy


"steve_doc" wrote in message
...
Thanks for the responce Tom
had already defined the list, my problem comes in when refering to the
dynamic range of the list on the form.
I am not at all clued up on VB scripting, and although i have managed to
adapt other code to work on this form, the list refeering to a dynamic range
seems to be beyond me, atm. i have no problems population the listBox from a
limited(unchangeable) list.

Is it possible for the RowSource to refer to a openended list?

"Tom Ogilvy" wrote:

use a defined name for the source for the listbox.

Insert = Name = Define

Name: List1
Refersto: =Offset(ClientInfo!A1,0,0,countA(ClientInfo!A:A),1 )


http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data




http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms
in
Microsoft Excel
--
Regards,
Tom Ogilvy

"steve_doc" wrote in message
...
Hi all
Hoping someone will be able to point me in the right direction.

Am creating a spreadsheet that a records new client data and enters it
on
sheet 1,via a userform.
From sheet 2 onwards are individual client acounts.
Question 1
on userform2 i need to create a list box from the client info on sheet
1,
as this is a dynamic range, i am not sure of the coding for this, is
this
possible?
Question 2
On completion of data on form2, all data to be posted out to
respective
client accounts,not sure of the coding for this?
Question 3
Is it possible that on entry of a new client, a new client accound
sheet
could be created?

Many thanks in advance for any direction with this
Steve

apparently you haven't successfully defined your range, so it isn't being
seen as a valid range reference. As I said, I have tested it and it works
fine.

--
Regards,
Tom Ogilvy

"steve_doc" wrote in message
...
thanks Tom, but all i get when trying to enter that is "Invalid Property
Value"

"Tom Ogilvy" wrote:

In the properties window of the Listbox or Combobox, under the rowsource
property, type in List1

that works for me.

--
Regards,
Tom Ogilvy


"steve_doc" wrote in message
...
Thanks for the responce Tom
had already defined the list, my problem comes in when refering to the
dynamic range of the list on the form.
I am not at all clued up on VB scripting, and although i have managed
to
adapt other code to work on this form, the list refeering to a dynamic
range
seems to be beyond me, atm. i have no problems population the listBox
from a
limited(unchangeable) list.

Is it possible for the RowSource to refer to a openended list?

"Tom Ogilvy" wrote:

use a defined name for the source for the listbox.

Insert = Name = Define

Name: List1
Refersto: =Offset(ClientInfo!A1,0,0,countA(ClientInfo!A:A),1 )


http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data




http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control
UserForms
in
Microsoft Excel
--
Regards,
Tom Ogilvy

"steve_doc" wrote in message
...
Hi all
Hoping someone will be able to point me in the right direction.

Am creating a spreadsheet that a records new client data and
enters it
on
sheet 1,via a userform.
From sheet 2 onwards are individual client acounts.
Question 1
on userform2 i need to create a list box from the client info on
sheet
1,
as this is a dynamic range, i am not sure of the coding for this, is
this
possible?
Question 2
On completion of data on form2, all data to be posted out to
respective
client accounts,not sure of the coding for this?
Question 3
Is it possible that on entry of a new client, a new client accound
sheet
could be created?

Many thanks in advance for any direction with this
Steve

Hiyee..

First of all, u need to define ur dynamic range in ur worksheet.
Choose
Insert Name Define
Give a name to ur range(for eg:ClientInfo), and key in this formula i
the empty space:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Then, click on your listbox, and in the rowsource there type the nam
that u gave to ur dynamic range:

Sheet1!A1:ClientInf

--
Message posted from http://www.ExcelForum.com

sheet1!A1:ClientInfo

produces inproper property assignment.




--
Regards,
Tom Ogilvy




"unplugs " wrote in message
...
Hiyee..

First of all, u need to define ur dynamic range in ur worksheet.
Choose
Insert Name Define
Give a name to ur range(for eg:ClientInfo), and key in this formula in
the empty space:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Then, click on your listbox, and in the rowsource there type the name
that u gave to ur dynamic range:

Sheet1!A1:ClientInfo


---
Message posted from http://www.ExcelForum.com/

ClientInfo is defined in the name range, and insert:
sheet1!A1:ClientInfo
into the rowsource in the properties for listbox. For me, this metho
works great...is it really produces inproper property assignment

--
Message posted from http://www.ExcelForum.com

In Excel 97 it did for me but I may not have tested it thoroughly enough.
In any event, it appears an obfuscation and redundant.

In Excel 2000, it is legitimate, but totally unnecessary when ClientInfo by
itself works fine. The question is what your definition of clientinfo is
and what you are trying to achieve with this notation. This uses the union
operator to build a union of cells from A1 to ClientInfo. If that is what
you intended to achieve, I suppose it is legitimate, but it seems the long
way around the block to me and your explanation did not show that to be your
intent or why you would need it. In fact your definition of ClientInfo
already started in A1 on Sheet1, so there is no reason to create a redundant
union. Except for the "unique" notation you used, the remainder of your
post repeated what already had been posted, so I am not sure what your
intent was.

--
Regards,
Tom Ogilvy





"unplugs " wrote in message
...
ClientInfo is defined in the name range, and insert:
sheet1!A1:ClientInfo
into the rowsource in the properties for listbox. For me, this method
works great...is it really produces inproper property assignment?


---
Message posted from http://www.ExcelForum.com/