Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using list box to edit or select data
Is it possible to have a user form or a list box on a form to select data and
if the data is correct then provide a command button that says data is correct (which in turn populates the appropriate fields), and if not allow the user to manually correct the fields that need to be updated or entered by allowing the user to edit the user form or list box? Example: I have a form that draws its data from a shared access database 'import external data-new database query'. Currently I placed a list box so that the user can select a ssn. Once selected several fields are populated (last name, first, etc...) The problem is that sometimes the ssn might not be there or the data in the sharable access database hasn't been updated properly. If it isn't updated, then the user has to manuallly correct the data in the form. Since I don't want to overwrite the formulae in those fields, is there a way to edit one or all of the fields using list or combo boxes and or user forms? Please help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using list box to edit or select data
Yep.
You may want to look at Data|form for the most basic stuff. But if you want validation: You may want to look at John Walkenbach's enhanced dataform: http://j-walk.com/ss/dataform/index.htm And if you want to create your own, then Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm BigPig wrote: Is it possible to have a user form or a list box on a form to select data and if the data is correct then provide a command button that says data is correct (which in turn populates the appropriate fields), and if not allow the user to manually correct the fields that need to be updated or entered by allowing the user to edit the user form or list box? Example: I have a form that draws its data from a shared access database 'import external data-new database query'. Currently I placed a list box so that the user can select a ssn. Once selected several fields are populated (last name, first, etc...) The problem is that sometimes the ssn might not be there or the data in the sharable access database hasn't been updated properly. If it isn't updated, then the user has to manuallly correct the data in the form. Since I don't want to overwrite the formulae in those fields, is there a way to edit one or all of the fields using list or combo boxes and or user forms? Please help! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using list box to edit or select data
Hi Dave,
Thankyou very much for your advice. What I ended up doing was: I had two spreadsheets: 1. Just the data that I pulled from a shared access database using data-get external data etc... In that I created named cell ranges for ssn, other key fields, and a named range for all of the data that I was using. 2. The form. In that I named cell ranges that referred to the 'data' sheet, ssn, other key fields and a named range for all of the data that I was using. I also named them the same as the 'data' sheet's named ranges. Also, I unchecked the checkbox labeled 'Show error alert after invalid data is entered'. Then I used data validation using the named ranges which referred to the other named ranges using index and match. There was some trial and error, but thanks to your advice I was able to work it out. Thanks again! Questions: 1. Is it possible to create a macro that will not only save the worksheet, but all of the cell formatting (borders, row heights, column widths, merged cells, and data)? I know I probably should have created the form using the design feature. 2. Is it possible to have a cell that has data validation in it to show a result in lieu of having to select the value? I have looked at some examples that show how to do something like this, but the formatting doesn't remain. 3. Would it be easier to save the raw data input into the form via macro into a worksheet/notepad/text file. And then be able to pull from that if necessary into the form? Like formflow. Thankyou again for you help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using list box to edit or select data
#1. You can save a workbook in code. If you record a macro when you do it
manually, you'll have the code. Make sure you save the workbook as a normal workbook--not a .csv and not a ..prn. It'll work just like saving any workbook. #2. Data|validation will help stop users. But your macros can put anything they want into those cells. But I'd be hesitant to do that. I don't understand the question about the formatting, though. If you put a formula in that cell, it has to evaluate to something that's considered valid by your data|validation rules. And formulas don't return colors/fonts/or that kind of formatting. They return values. #3. My personal opinion is that it's usually easier to load things into a form from an excel worksheet--but you could do anything you want. I don't know what flowform is. #4. Would it have been easier to use Access? Just create an Access userform and keep that data directly in Access? (From someone who doesn't speak the Access.) BigPig wrote: Hi Dave, Thankyou very much for your advice. What I ended up doing was: I had two spreadsheets: 1. Just the data that I pulled from a shared access database using data-get external data etc... In that I created named cell ranges for ssn, other key fields, and a named range for all of the data that I was using. 2. The form. In that I named cell ranges that referred to the 'data' sheet, ssn, other key fields and a named range for all of the data that I was using. I also named them the same as the 'data' sheet's named ranges. Also, I unchecked the checkbox labeled 'Show error alert after invalid data is entered'. Then I used data validation using the named ranges which referred to the other named ranges using index and match. There was some trial and error, but thanks to your advice I was able to work it out. Thanks again! Questions: 1. Is it possible to create a macro that will not only save the worksheet, but all of the cell formatting (borders, row heights, column widths, merged cells, and data)? I know I probably should have created the form using the design feature. 2. Is it possible to have a cell that has data validation in it to show a result in lieu of having to select the value? I have looked at some examples that show how to do something like this, but the formatting doesn't remain. 3. Would it be easier to save the raw data input into the form via macro into a worksheet/notepad/text file. And then be able to pull from that if necessary into the form? Like formflow. Thankyou again for you help. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using list box to edit or select data
Thanks for your input.
1. I'll do some more research on a macro that'll just save my worksheet etc... 2. That's what I figured, but I had to ask. The worksheet I was referring to had a macro that allowed the user via properties to change the font, etc...Unfortunately it didn't stay that way after exiting the list/combo box. What it did was after double clicking on a list box (data validation) a list box from control forms would appear in the cell tha you selected, and the data you typed in would be as you formatted it in properties. But afterward clicking on another cell it would revert to the original formatting found in data validation. It's not a biggy, just something that I'll have to play with some more. 3. The reason for the quesiton is that the form is about 300kb, which would be saved many times (once for each applicant). Since that kind of space would add up after awhile, I was trying to think of a better way of saving the file. But I think if I get rid of some of the control box list boxes, that will save some space. Form flow is a databased application that has thousands of forms, and allows the user to save the data not in the form but in its database files. All a user would have to do is to save the data, or if they wanted to review the form from information saved to the database they could do that too. 4. You are right about access. I should do that, it's just that I find excel much more user friendly, and easier to set up. I have made a couple of databases in access, and it takes forever and a day to set up. Plus I am a fan of numbers, and excel's ease of use (most of the time), and I want to learn more about excel before I move on to access. Nonetheless you are right about access. Thanks very much for your advice! ---Mike--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Data Validation and Blanks in List | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |