Looking For Alternatives to VBA/Maccro to AUTOMATE Data Entry

.
.
Take a look at this first ... then come back for more assistance if needed:

http://www.exceltrick.com/formulas_m...-if-statement/

Originally Posted by Logit

.
.
... then come back for more assistance if needed.

THANKS for the prompt reply. I'm familiar with, and frequently use IF statements. Just in case I've been missing something, I checked out the link. Pretty much the way I use IF statements.

Don't think I can get the "flow" by using a series of IF statements.... (remember, I'm trying to get a "turbo tax" type experience for the user.).

My vision is (much like "turbo tax"), the user is presented with a series of simple, narrative questions, each of which the user knows the answer to. When the user responds to question 1 (by entering a number in the field), that number is inserted into the appropriate place in the spreadsheet computation, and the user is then presented with narrative question 2... etc. etc.....
Basically, I want to shield them from having to search around in the spreadsheet to find the field where then need to enter specific data.

So... my quest continues....

Duplicate post. The Forum is acting up.

Originally Posted by Logit

..............................................................................
......and see what you think.

Well....I'm NOT familiar with "message box"... so, will check it out. (My initial thought, just from looking at the logic of the code here, is that it would not provide the user experience I'm looking for. May have to get more into coding.....

THANKS.

(BTW.... tried to leave you another "reputation" note, but got message saying I'd have to leave same for others b4 able to leave u another.).

Most likely you will have to get into coding. The type of Interface you're referring to is called a 'Wizard' where changes on one section affect the display/availability of options on other sections - Wizards are not something that Excel can generate without VBA code.

Have a look at the attached - it's a sample by John Wakenbach of a simple Wizard interface.

Hi Logit- a few typos in your code:

Originally Posted by Logit

..............................................................................

Please Login or Register  to view this content.

Seems like many, many version ago of Excel, I was able to do a (relatively) simple macro where:
1. You START the macro... like with hotkey sequence.
2. The macro would individual-letter-type-in the question (e.g. How many students will be in the class?).
3. Then, the macro would go to the appropriate field and WAIT FOR USER INPUT/RESPONSE.
4. Then, the user would enter the number (say "25" for example), and then hit ENTER.
5. That would cause the macro to CONTINUE.....
6. Generating the NEXT narrative question (e.g. "How many times will the class be taught in a year?").
7. Predictably, the macro would then go to the appropriate field for THAT data point, and PAUSE, awaiting the user to enter the data.
8. Then, the user would enter the number of classes for the year (say "12"), and when the user hit ENTER, the macro would continue to the NEXT data point needed.
9. This would continue and basically "walk the user" through each needed response, one at a time.
10. When the last question was asked, the macro would TERMINATE.
11. All the while, the spreadsheet has been doing the required calculations on the fly.

Is this NOT possible now with macros in Excel? (i.e. without having to do VB scripting etc.).
Thanks.

Originally Posted by cytop

The type of Interface you're referring to is called a 'Wizard'.....

Ah...yes... I've heard "turbo tax" and similar things referred to as "wizard-driven" solutions.

leelnich

As I understand ....

Excel 2018 will have pre-installed a morph Scribe who sits behind your monitor auto correcting spelling and logic mistakes.

Can't wait !

Originally Posted by Logit

leelnich

As I understand ....

Excel 2018 will have pre-installed a morph Scribe who sits behind your monitor auto correcting spelling and logic mistakes.

Can't wait !

...............................MorphScribe.jpg

777merlin777

I've use TurboTax for many years. It has progressed to be an excellent program, very user intuitive and simply fun to use. Also more
accurate than my Tax Return Preparer !

The closest comparison I can relate to their format and design is a combination of Power Point slides and a "database". The new
screens (partial screen) slides in or morphs into view. The question answers are held somewhere and inputted into the final tally.

I would not recommend using Power Point (my personal preference) cuz there is a learning curve there in addition to VBA.

It would seem, as others have suggested here, additional knowledge of VBA is required to obtain the same 'look and feel' of TurboTax.
The MsgBox simply isn't going to provide the same although it will accomplish your goal to obtain user responses and entering those
to a worksheet or final compilation.

Yours is the Student version.

For $99.95 more you can get the Professional Version :

attractive-business-woman-character-free-vector.jpg

A wizard can be a complex interface to design - or maybe that's the wrong word but it can get convoluted. A simpler way is to select cells depending on what has been entered before. I've no idea of the scale (numbr of columns/sections or whatnot) you are thinking of using, but you can write up a fairly simple macro that checks which column has been edited and decides which cell in the same row should be the next to receive input.

In the attached, selecting 'Female' in the first column will skip the DOB column (I believe you're of an age that will understand the reason ). Selecting 'Male' will skip the size column and move directly to the 'Waist' column - silly examples, I know, but just something cobbled together quickly to show how.

Originally Posted by cytop

.....something cobbled together quickly to show how.

Thanks, I'll check it out.

Originally Posted by dflak

.... I would attack this issue is to have an excel table that contains question number, question text, possible answers and then follow-on questions based on those answers......

Thanks for the suggestion... I kind of follow what you're saying... BUT, how would the responses to the questions in the table get "automatically" entered into the appropriate fields in the spreadsheet, in order for the spreadsheet to do the CALCULATIONS?

I'll put up a sample spreadsheet tomorrow.... I can tell I'm not successfully communicating the concept of what I'm looking for via just narrative.

It's all very simple - it will grow but will essentially keep the same structure

Please Login or Register  to view this content.

As you've found out, the code is in the Worksheet_Change event. This event gets fired automatically every time a cell is changed by the user (but bear in mind NOT when a cell changes as the result of a recalculation).

It gets passed 1 parameter called Target. This represents the cell, or cells, that have been changed. For simplicity, just assuming only 1 cell is changed at a time.

Target is actually a Rangeand a Range has properties like Row number, Column number, Full Address (A1, for example)

There's a simple test to check which column was changed

Please Login or Register  to view this content.

If 2 or 3 then simply select one cell to the right of the changed cell

If 4 then it checks the contents of the cell in Col 2 of the same row as the changed cell

Please Login or Register  to view this content.

If Female' then it skips over 2 columns to pass the DOB column, otherwise it skips over 1.

Can't really be any simpler.

Originally Posted by cytop

It's all very simple -...Can't really be any simpler.

Well... I'm certainly not THERE (quote above) yet... BUT I really can see the POWER of being able to "automate" data entry that way....

....to be continued.....