Designing an input form for non-Excel users

[StuLux]

I'm after some general thoughts on how to best design a form for completion by non-technical/Excel novices. I'm aware of the inbuilt Excel form function but I'm thinking of something even more simplistic along the lines of clicking on a 'start' button and then Excel asking the user questions, one at a time, until the form is completed. I would prefer that there is no VBA involved.
I was thinking of perhaps sepearte excel worksheets for each question with hyperlinks from an instructiosn page but this sounds like overkill - would love to hear what other users have done in this situation perhaps with postings of some examples?

 

[Joe4]

I'm aware of the inbuilt Excel form function but I'm thinking of something even more simplistic along the lines of clicking on a 'start' button and then Excel asking the user questions, one at a time, until the form is completed. I would prefer that there is no VBA involved.

I don't see how this is possible without VBA. You cannot really use Excel forms without VBA, and even if you didn't use forms, but wanted to just use Input Boxes for all your questions instead, that is still VBA.

I was thinking of perhaps sepearte excel worksheets for each question with hyperlinks from an instructiosn page but this sounds like overkill - would love to hear what other users have done in this situation perhaps with postings of some examples?

That really does sound like overkill, and I am not sure you could do this and not involve any VBA at all (as you still want to update cells). I don't think I would ever entertain an idea like this.

One option to do this that may be able to avoid VBA is to use Microsoft Access instead. I used to do this all the time at my old job for a bunch of users I had. I created a shortcut for them to run it, and they had no idea that they were using Access at all. Of course, this requires knowledge of Access database development for the person building this.

 

[StuLux]

Thanks Joe, yes the more I look at this the more I think that Excel perhaps isn't the best tool for gathering information as it is so easily 'broken' by non technical users (pasting information across multiple cells etc.). My client is using Cognito forms (similar to Microsoft Forms) and I think they may be best sticking to that as a data collection tool.

 

[Joe4]

Thanks Joe, yes the more I look at this the more I think that Excel perhaps isn't the best tool for gathering information as it is so easily 'broken' by non technical users (pasting information across multiple cells etc.). My client is using Cognito forms (similar to Microsoft Forms) and I think they may be best sticking to that as a data collection tool.

Yes, that is one reason why I usually prefer Access. It is much easier to control the data entry, and you can do it most of it without VBA.

You can do something very similar in Excel using Forms, but that is a bit more cumbersome, as you then have to use VBA to map the entries to your sheets (where Access's forms can be directly bound to the data tables, so no VBA mapping required). So it is just a bit more work to do it in Excel.