lookup source for Macro from a dynamic Range

S

steve_doc

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
 
T

Tom Ogilvy

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.aspx?scid=kb;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel
--
Regards,
Tom Ogilvy

steve_doc said:
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,
Click to expand...
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
Click to expand...
client accounts,not sure of the coding for this?
 
S

steve_doc

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

Tom Ogilvy

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 said:
Thanks for the responce Tom
had already defined the list, my problem comes in when refering to the
Click to expand...
dynamic range of the list on the form.
I am not at all clued up on VB scripting, and although i have managed to
Click to expand...
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.
 
T

Tom Ogilvy

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

unplugs

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
 
U

unplugs

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
 
T

Tom Ogilvy

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