Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
Excel 2000
I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? Thanks, Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
Read this KB Robert
http://support.microsoft.com/default...62&Product=xlw ShowDataForm Method Fails If Data Can't Be Found John Walkenbach his much better Dataform http://j-walk.com/ss/dataform/index.htm See this http://j-walk.com/ss/dataform/features.htm to see the differents between the Excel dataform and John's one -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? Thanks, Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
Ron,
Thanks for the leads, however I would like to write formula / code as this requirement is on a number of PCs and I don't want to buy the code for each machine. The VBA part that I can't get right is naming a range, using macro record, I select the first cell, then holding the shift key press End and Home keys. I then name the range. Looking at the macro this refers to R1C1 etc. etc. Any help most welcome. Robert "Ron de Bruin" wrote in message ... Read this KB Robert http://support.microsoft.com/default...62&Product=xlw ShowDataForm Method Fails If Data Can't Be Found John Walkenbach his much better Dataform http://j-walk.com/ss/dataform/index.htm See this http://j-walk.com/ss/dataform/features.htm to see the differents between the Excel dataform and John's one -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? Thanks, Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
See Tom's answer
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... Ron, Thanks for the leads, however I would like to write formula / code as this requirement is on a number of PCs and I don't want to buy the code for each machine. The VBA part that I can't get right is naming a range, using macro record, I select the first cell, then holding the shift key press End and Home keys. I then name the range. Looking at the macro this refers to R1C1 etc. etc. Any help most welcome. Robert "Ron de Bruin" wrote in message ... Read this KB Robert http://support.microsoft.com/default...62&Product=xlw ShowDataForm Method Fails If Data Can't Be Found John Walkenbach his much better Dataform http://j-walk.com/ss/dataform/index.htm See this http://j-walk.com/ss/dataform/features.htm to see the differents between the Excel dataform and John's one -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? Thanks, Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
You could create a dynamic range named 'Database'. There are
instructions he http://www.contextures.com/xlNames01.html Robert wrote: Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
Debra,
Thank you ever so much, dynamic range is just the thing. I tried =vlooup(A1,Customers!A:H,2) which seems to also work i.e. row number not entered - just don't understand why this method worked. Thanks, Robert "Debra Dalgleish" wrote in message ... You could create a dynamic range named 'Database'. There are instructions he http://www.contextures.com/xlNames01.html Robert wrote: Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
If you name your range database (not with a dynamic formula definition, but
refering to the current data) and add your information using the dataform, then the dataform will automatically extend the definition of database. -- Regards, Tom Ogilvy "Robert" wrote in message ... Debra, Thank you ever so much, dynamic range is just the thing. I tried =vlooup(A1,Customers!A:H,2) which seems to also work i.e. row number not entered - just don't understand why this method worked. Thanks, Robert "Debra Dalgleish" wrote in message ... You could create a dynamic range named 'Database'. There are instructions he http://www.contextures.com/xlNames01.html Robert wrote: Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Data Form
Tom,
Thanks for the tip, I never realised that the name "database" did this. I have several lists, each on a separate sheet within a workbook and I have found to my delight, that prefixing database with the sheet name allows for multiple database range names. Thanks, Robert "Tom Ogilvy" wrote in message ... If you name your range database (not with a dynamic formula definition, but refering to the current data) and add your information using the dataform, then the dataform will automatically extend the definition of database. -- Regards, Tom Ogilvy "Robert" wrote in message ... Debra, Thank you ever so much, dynamic range is just the thing. I tried =vlooup(A1,Customers!A:H,2) which seems to also work i.e. row number not entered - just don't understand why this method worked. Thanks, Robert "Debra Dalgleish" wrote in message ... You could create a dynamic range named 'Database'. There are instructions he http://www.contextures.com/xlNames01.html Robert wrote: Excel 2000 I have a list of customers which I add to each day and then have to redefine the range name i.e. extend to include the last row or rows which I entered. I have been using the built-in feature of Data Form and although I can run this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I can't seem to figure out how to extend the range. Do I need VBA code or a formula that expands? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula (off form) that completes form data based on a result | Excel Worksheet Functions | |||
excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
Create a form in excel so I can enter data using DataForm | Excel Discussion (Misc queries) | |||
how to get a data form to fill you own exel sheet (was data-form | Excel Worksheet Functions | |||
have 3 worksheets, 1 is a form, the other 2 data for the form-wan. | Excel Worksheet Functions |