Create Forms without VBA?

[ExcelExcelExcel]

Is it possible to create forms in Excel without VBA? More specifically, I'd like a way or a tool to create robust Access-like input/view forms automatically (like in Access) but still use Excel.

I know there's the Data>Form 'autoform' built into Excel, good for 32 fields in Access, and J-Walk's free Enhanced Data Form, good for an unlimited number of fields. I'm looking for something like that, but which permits customization, such as permitting check boxes for Y/N fields and the ability to move the field names and field values around on the form so they make more sense visually.

In Access, it's really easy to create forms automatically. Is there such a way, maybe a third-party tool, to do it with Excel? (That is, WITHOUT needing to code with VBA, which I cannot do.) If there were some built in wizard, like in Access, that's be GREAT!

Why not use Access? It's just too complicated for me. Plus, I get the feeling it's harder to go back and forth from Outlook to Access and vice versa, whereas with Outlook and Excel, one can more easily update changes in each by sending data to the other and overwriting the old data, thus ensuring each has the most updated data of the other.

My goal is to create a database in Excel, one which would manage my small business. (Creating a legal case management software program, similar to programs like AbacusLaw, TimeMatters, etc.) The ability to have forms to remind what data needs to be inputed, and also to see the data in a 'big picture' way so as to make sense as to what's going on (and what data is still missing) is important. I also want to use Outlook more, and have Outlook 'visually' display the data in Excel (such as when appointments are, and have Outlook pop up the appointment reminders).

Thanks for listening to my long question!

 

[VoG]

Take a look at UserForms - straightforward. http://www.contextures.com/xlUserForm01.html

 

[Smitty]

Welcome to the Board!

Nope, if you want to get beyond the native Data Form, you'll need VBA. But it's not that hard to do.

As for moving data between Outlook and Excel or Access it's all relatively the same, so if you think Access will be more beneficial in the future I'd start there instead of having to recreate an Excel solution somewhere down the line.

On the other hand if you're doing this to manage a small business, I'd look at the Small Business Contact Manager add-in for Outlook.

It's got robust series of tools in it, and you can also customize forms directly in Outlook. What most people don't realize is that Outlook is itself a database, and a powerful one. If Outlook will be your primary tool, then I'd think about sticking to that before trying to rebuild tools that are already there.

HTH,

 

[ExcelExcelExcel]

Smitty,

Thanks for the suggestion. I'm wondering, though, what the schema is for Outlook BCM and whether it would suit my needs.

This is the very simplified schema I'm trying to produce (in Excel for simplicity, using VLookUp's, but wishing I had the form wizards in Access):

CASES CONTACTS
\ /
PLAYERS
|
ACTIONS

Basically, it's a Y.

I have multiple CASES, multiple CONTACTS, and a given contact is related to a given case with a given role (thereby defined as a PLAYER). All ACTIONS (phone call, letter, email, etc.) is related to a given player, thus relating it back to the case and/or contact. All other 'tables' are related to the these basic tables, specifically, to a given case.

It really is best suited for Access (as I want a simple way of creating robust input and viewing forms), but Excel is so much easier to work with. I'm thinking Outlook BCM may be too limited for me and force me into its way of doing things.

I've struggled with this issue for years, as I'm looking for a robust, complete solution, whereas perhaps even a few simple sheets in Excel would actually let me get things done! (Excel seems almost like an open source program, with sites such as http://orlando.mvps.org/ (with its stopwatch utility for tracking time spent) and http://spreadsheetpage.com/index.php/dataform/home (enhanced data form). This is one reason I lean toward Excel over Access.)

This is why I was hoping there was some sort of wizard or utility, as in Access, that would enable the creation of robust forms *without* VBA, as the Contextures page is just beyond my understanding (as my pages would have tons more fields, etc.). Someone built a programs, http://www.datamanagerforexcel.com/features.htm, which is similar to what I'm wanting, but there's no support, it's old, and it won't work beyond Excel 2003. If there was a modern utility like Access form wizard, that'd be exactly what I'm looking for.

As for going back and forth between Access and Outlook, my experiments showed that one can overwrite all the data in Outlook and the correct Excel sheets easily enough (always keeping the two synced to the most recent data), but that the same really can't be done with Access (as it has key numbers, which prevent old data from being overwritten).

 

[Norie]

Why couldn't you do this in Access?

If you were try to set the whole thing up from scratch it would probably be a lot of work but there's a lot of database templates out there.

You probably won't find one that fits your needs exactly but you should find something to get you started.