Currently, I have an Excel spread sheet that has in the first column the name of a person with the last name first. I use the next column for that entry as a text box in which I first type in the date ( e.g. 10/09/04 ) and then write a paragraph – perhaps a long paragraph -- discussing what I did on the file associated with the named person. In the third column I type in the time I worked on the file in 6 minute increments ( e.g. 1’42 ). The company I work for uses Office 97 so that is what I use at work and at home. I’m eagerly awaiting an upgrade because I typically have 100 to 200 different names in the first column and the auto-complete feature of Excel only works for some of the entries because I have too many names. Currently, I have about 2000 rows in this spreadsheet dating back about two years.
I’m trying to build an application around this spreadsheet that might include an Access database and word documents. With the help of tips from this bulletin board, I have already made progress towards this goal. I can search the document for the first use of a particular name in the document and then run a macro that will make a list on a new sheet of the workbook that is just one column with all of the text entries in the order they are in the column. I can strip out all of the entries for a file I’m closing and save those entries to an archive file. And I have one macro that first sorts the entire list alphabetically by last name and coverts it automatically into a word document with the date the document was created in the title, then the first file name in blue, then all entries for that name, then the next name, etc. On my computer, that file is currently about 200 pages long. I download that to my Ipac and wherever I am, I can use the find feature of Word to read all entries on any file. This capability astounds the people I work with away from the office.
Here is where I need help. I want to make this spread sheet look more professional and easier to use. What I want is some kind of form for entering new data. I want a box of some type towards the left side of the screen in which I can start typing the name of a file and have the application AutoComplete the name or permit me to type in a new name. On the right side of the screen there would be a large text window. Once the correct name is specified on the left side, I would click a button and my macro would sort out all the text entries for that file and place that column of the new worksheet in the text box on the right with a scrollbar so I can review all entries. A button would permit me to print the list – perhaps as a Word document – or E-mail the list to someone using Outlook.
There would be a large box on the right beneath the place for the entry of the name. If I want to make a new entry, I could click a button and a macro would start the entry by putting in the current date. When I complete the text entry, I would like to be able to push one button and run spell check. I want to be able to push a button and send the file with an appropriate introductory header to someone using Outlook. When I finish with the entry, I could push a button and be prompted to enter the time for the third column. Then I would be asked if I wanted to save the entry and the program would go to the end of my long file, add the name, the note, and the time in the appropriate columns, save the file, delete the file that I had created for the window on the right side, and clear all text boxes.
I can do much of the above but what kind of form should I use? How can I make the window on the right side that would permit me to scroll through the Excel column with all chosen files? If I can’t do that with an Excel column, could I scroll through a Word document? What kind of box should I for the text entry on the right? Some of my entries approach the limit that Excel will display in one cell. I think this limit is about a 1000 characters. Can I run spell check on the text entry box?
Ultimately, I would like to add to this application an Access Database. I would like to have rectangles -- windows? -- on the form that could be replaced with other rectangles. For example, the right side of the screen might be a large rectangle that has an outline around its border and might include the text box for displaying the column of notes from the worksheet. I’d like to be able to click a button and have the rectangle replace by a data entry form from Access or a report from Access or a Word document. I would have a rectangle on the right of identical size that would have the boxes mentioned previously. I could also replace this box with an Access form or report etc. I might have a long thin box on the bottom. My main objection with many applications is that the user has to toggle from one screen to another to read or manipulate date. By having these three rectangles (windows?), I could mix and match from many rectangles to have everything I needed on the screen at the same time. Is there a way to make these interchangeable windows?
Thanks for any help you can provide.
****
I’m trying to build an application around this spreadsheet that might include an Access database and word documents. With the help of tips from this bulletin board, I have already made progress towards this goal. I can search the document for the first use of a particular name in the document and then run a macro that will make a list on a new sheet of the workbook that is just one column with all of the text entries in the order they are in the column. I can strip out all of the entries for a file I’m closing and save those entries to an archive file. And I have one macro that first sorts the entire list alphabetically by last name and coverts it automatically into a word document with the date the document was created in the title, then the first file name in blue, then all entries for that name, then the next name, etc. On my computer, that file is currently about 200 pages long. I download that to my Ipac and wherever I am, I can use the find feature of Word to read all entries on any file. This capability astounds the people I work with away from the office.
Here is where I need help. I want to make this spread sheet look more professional and easier to use. What I want is some kind of form for entering new data. I want a box of some type towards the left side of the screen in which I can start typing the name of a file and have the application AutoComplete the name or permit me to type in a new name. On the right side of the screen there would be a large text window. Once the correct name is specified on the left side, I would click a button and my macro would sort out all the text entries for that file and place that column of the new worksheet in the text box on the right with a scrollbar so I can review all entries. A button would permit me to print the list – perhaps as a Word document – or E-mail the list to someone using Outlook.
There would be a large box on the right beneath the place for the entry of the name. If I want to make a new entry, I could click a button and a macro would start the entry by putting in the current date. When I complete the text entry, I would like to be able to push one button and run spell check. I want to be able to push a button and send the file with an appropriate introductory header to someone using Outlook. When I finish with the entry, I could push a button and be prompted to enter the time for the third column. Then I would be asked if I wanted to save the entry and the program would go to the end of my long file, add the name, the note, and the time in the appropriate columns, save the file, delete the file that I had created for the window on the right side, and clear all text boxes.
I can do much of the above but what kind of form should I use? How can I make the window on the right side that would permit me to scroll through the Excel column with all chosen files? If I can’t do that with an Excel column, could I scroll through a Word document? What kind of box should I for the text entry on the right? Some of my entries approach the limit that Excel will display in one cell. I think this limit is about a 1000 characters. Can I run spell check on the text entry box?
Ultimately, I would like to add to this application an Access Database. I would like to have rectangles -- windows? -- on the form that could be replaced with other rectangles. For example, the right side of the screen might be a large rectangle that has an outline around its border and might include the text box for displaying the column of notes from the worksheet. I’d like to be able to click a button and have the rectangle replace by a data entry form from Access or a report from Access or a Word document. I would have a rectangle on the right of identical size that would have the boxes mentioned previously. I could also replace this box with an Access form or report etc. I might have a long thin box on the bottom. My main objection with many applications is that the user has to toggle from one screen to another to read or manipulate date. By having these three rectangles (windows?), I could mix and match from many rectangles to have everything I needed on the screen at the same time. Is there a way to make these interchangeable windows?
Thanks for any help you can provide.
****
