I've been handed an Excel spreadsheet with almost 80 unique fields, which filter out into nine separate tabs - with different sets of columns and filtered data. I'm comfortable creating code to filter out the various reports from a central spreadsheet with all the data.
Now I've been asked to create a user-entry system for this thing. (A huge percent of the fields are dates, so I think they should be running it through project management software; but that's not my call.)
I've done a fair bit of programming in MSAccess, where user forms are easy and straight forward to link to tables and queries. But I've never created user forms in Excel. I have found a great tutorial on how to program a form for new data entry (Pro tip: Add a UserForm to aid data entry in Excel - TechRepublic), but I still have a number of questions.At this point I'm just looking for a "Yes, possible", "No, dreaming" type response. I'm guessing it's going to take me a while to get around to the specific issues!
1. Can I create a user form that allows for both new record entry and editing of existing records? I tried the auto option provided by Excel, but obviously my 80 fields is too many.
2. Can I 'lock' the spreadsheet so users cannot directly modify it (i.e. they have to use the form)?
3. Given that there's 80 fields, can I create a tabbed user form, with different data sets on different tabs? e.g. all construction related data on one tab, all general project overview on another, etc.
4. The tutorial I found shows how to set up the options for a combo-box in the code behind the box. In Access, this would be kept in a table so it can be easily updated. Is it possible to keep combo box options in a range on a hidden sheet (or similar)?
5. Can I set "invalid data" type warnings on various boxes that refuse to allow a user to enter incorrect data? For example, if a user enters a completion data after today.
6. Can I set up a field that is a concatenation of various combo and text boxes? I have a field that is "Part A-#-Part B". Part A and Part B are both very small lists of options (1/2 dozen options), the number may or may not be required. Part A and Part B are both required as separate fields, so ideally, I'd like the information to only be entered once.
7. For any combo box, can I set it up so the user can (preferably with a warning) add their own text rather than choose an option.
8. Can I set a no-duplicates field? i.e. if a user tries to create a new record but certain data matches existing records it returns an error message
I think that's it for the moment.
Like I said, I don't need code or great details at the moment, just a general 'yes', 'no', 'easy', 'hard' sort of response to each question would be great. I know how to do all of these through Access, but the format of the data is not up to me. They are in the process of migrating data and apparently have real database boffins working on a long-term solution; so this is a stop gap measure for a couple of months (I'm guessing).
Now I've been asked to create a user-entry system for this thing. (A huge percent of the fields are dates, so I think they should be running it through project management software; but that's not my call.)
I've done a fair bit of programming in MSAccess, where user forms are easy and straight forward to link to tables and queries. But I've never created user forms in Excel. I have found a great tutorial on how to program a form for new data entry (Pro tip: Add a UserForm to aid data entry in Excel - TechRepublic), but I still have a number of questions.At this point I'm just looking for a "Yes, possible", "No, dreaming" type response. I'm guessing it's going to take me a while to get around to the specific issues!
1. Can I create a user form that allows for both new record entry and editing of existing records? I tried the auto option provided by Excel, but obviously my 80 fields is too many.
2. Can I 'lock' the spreadsheet so users cannot directly modify it (i.e. they have to use the form)?
3. Given that there's 80 fields, can I create a tabbed user form, with different data sets on different tabs? e.g. all construction related data on one tab, all general project overview on another, etc.
4. The tutorial I found shows how to set up the options for a combo-box in the code behind the box. In Access, this would be kept in a table so it can be easily updated. Is it possible to keep combo box options in a range on a hidden sheet (or similar)?
5. Can I set "invalid data" type warnings on various boxes that refuse to allow a user to enter incorrect data? For example, if a user enters a completion data after today.
6. Can I set up a field that is a concatenation of various combo and text boxes? I have a field that is "Part A-#-Part B". Part A and Part B are both very small lists of options (1/2 dozen options), the number may or may not be required. Part A and Part B are both required as separate fields, so ideally, I'd like the information to only be entered once.
7. For any combo box, can I set it up so the user can (preferably with a warning) add their own text rather than choose an option.
8. Can I set a no-duplicates field? i.e. if a user tries to create a new record but certain data matches existing records it returns an error message
I think that's it for the moment.
Like I said, I don't need code or great details at the moment, just a general 'yes', 'no', 'easy', 'hard' sort of response to each question would be great. I know how to do all of these through Access, but the format of the data is not up to me. They are in the process of migrating data and apparently have real database boffins working on a long-term solution; so this is a stop gap measure for a couple of months (I'm guessing).
