Userform vs controls in worksheet

[dattagal]

I recently started a project where I am using the first sheet in a workbook as my "Questions" sheet to build a form (not a userform) in sheet 2. I hide or unhide rows and insert text into the English worksheet/form based on the answers to the questions on the Questions worksheet.

Aside from having to select several individual controls to move a group of them and wondering how I am going to keep users from moving them once I roll out my nifty little program, this approach seems to be working fine.

After getting about a 1/3 of the way through the project however, I discovered the userform and took off on a tangent recreating the project with a userform with a tabbed control that is really cool.

I had done about a 1/3 of the work in this project when I started second guessing myself - my boss is excited about the prospect of having a program like this but part of what she really likes about it is that it mimics the look of an online system we use, so the users will be familiar with the look of this program. I started wondering if she is going to have issues with me changing horses in the middle of the stream.

I guess my question is this...

Is there any good reason to use a userform to input responses into a worksheet or is using controls in worksheet just as good?

Will a worksheet get bogged down with too many controls? (There will be a lot of them by the time I am done.)

btw: sorry for the long post - I hope I haven't been too long winded

and thanks so much in advance for any advice you can offer

I am sure I will be posting more as I get further into this project

can't wait huh?

[geekgirlau]

If your sheet is going to end up being really busy, with lots of controls my personal preference would be to go with a userform - as you've already mentioned, the ability to break up your controls onto separate tabs makes for a much easier user interface.

I find if I only need to capture a handful of variables from the user, I will use a protected sheet with specific cells unlocked for user entry, data validation where I need a drop-down list, and steer clear of having controls on the sheet at all if possible. In your case, go with the user form.

[Bob Phillips]

Aside from having to select several individual controls to move a group of them and wondering how I am going to keep users from messing with those controls once I roll out my nifty little program, this approach seems to working fine.
What can they do that is so bad if you provide basic good practices?


After getting about a 1/3 of the way through the project however, I discovered the userform and took off on a tangent recreating the project with a userform with a tabbed control that is very nifty.
You can simulate a form in a worksheet, with tabbing etc. Be aware that with a userform you are on you own. There is no built-in data masking, no data validation, etc., you have to do it all yourself. Generally, it takes far more work.


I had done about a 1/3 of the work in this project when I started second guessing myself - my boss is excited about the prospect of having a program like this but part of what she really likes about it is that it mimics the look of an online system we use, so the users will be familiar with the look of this program. I started wondering if she is going to have issues with changing horses in the middle of the stream.
You can do that with a worksheet if it is important, but one has to ask, if you don't want it to look like Excel, why use Excel?


I guess my question is this...

Is there any good reason to use a userform to input responses into a worksheet or is using controls in worksheet to build a "form" in another worksheet just as good?
Userforms come into there own IMO when the project is complex, multiple forms, multiple situations, where you need to manage all the possibilities. Code gives you that control probably more easily. A single form; I tend to prefer using a worksheet where I can utilise Excel's rich, built-in functionality.



Will a worksheet get bogged down with too many controls? (There will be a lot of them by the time I am done.)
How many is a lot? Could you re-design to reduce them?

[JonPeltier]

I find it much harder to design these interfaces within the worksheet, because it is much harder to keep things out of the hands of the users. I find it easier to focus a user in a userform, I find it easier to validate inputs in a userform, I find the arrangement of controls on a userform to be much more flexible. I've also designed Excel-based systems which do not look like Excel, and do not display the Excel environment at all (these are probably better done in Access, but for various reasons these remained in Excel).

[dattagal]

Thanks for your response geekgirlau - I truly appreciate your help.


What can they do that is so bad if you provide basic good practices?

Probably nothing as long as I provide those good basic practices; it's just that I am not at al sure what those good basic practices are. I will assume that you are talking about protecting a sheet but I have never quite figured out how that is done (while allowing certain cells to be written that is). Of course it is entirely possible you are not talking about worksheet protection but if not, I would be interested in what you are referring to.



You can simulate a form in a worksheet, with tabbing etc. Be aware that with a userform you are on you own. There is no built-in data masking, no data validation, etc., you have to do it all yourself. Generally, it takes far more work.

Good point!



You can do that with a worksheet if it is important, but one has to ask, if you don't want it to look like Excel, why use Excel?

It's not so much that we don't want it to look like Excel so much as we want it to look like the online system which is basically just a white back ground with numbered questions and controls to click or input data. We have been using it for sometime and thought it would be helpful to give users something they would already be familiar with.



Userforms come into there own IMO when the project is complex, multiple forms, multiple situations, where you need to manage all the possibilities. Code gives you that control probably more easily. A single form; I tend to prefer using a worksheet where I can utilise Excel's rich, built-in functionality.

Another good point, but, while this is simple enough that I was confident I could do it, it turns out it is not quite as simple as I had thought. I have very limited skills in writing code and I am certain the way I do things could be done much easier with much less code to write (I probably go about things in an awkward way) but I do what I can and it generally works.

I figured I would at least get something going and then do a "version 2" later on with a little tighter code (when I learn it of course).


Only you can answer that, we don't know her, you and your abilities, and what the impact will be. Personally, I would think hard about it.

Excellent point. I will certainly think hard about it but I have to make a decision and get going on it soon here too as it is to be done soon. (one of those wanted-it-yesterday things)


How many is a lot? Could you re-design to reduce them?

Well, I have nearly 50 now and easily end up with 3x that so approx 150. I don't think there is a way to cut down; one thing I do consider myself pretty good at is designing a user interface and using the best control for the job is my rule whether or not that means more controls. May 150 is no big deal for a worksheet - I have no idea. (Which is of course why I came here.)

Anyway, thanks so much for your feedback. Somtimes I feel I bit of more than I can chew on this project and other times I can't tear myself away from it.

I wish I could work on it at home too but I have 2003 at home and 2000 at the office and sometimes they play well together.

[dattagal]

I see I addressed the wrong respondent. Thanks to xld and geekgirlau ;o)

[dattagal]

thanks geekgirlau I will certainly take your thoughts into consideration.

thing is, I am going to need to ask a lot of questions to get the information I need to build the form, so in that repsect, it seems that the userform is the way to go, but again, I don't know that much about Excel which begs the question why I would take on such a project anyway huh?

Oh well, I am one to get excited about something and jump in.

[dattagal]

thanks Jon

as time permits I will be perusing these posts again and making a decision one way or another soon. thanks so much for the feedback and advice

btw - I am generally much more comfortable in access but persuading the decision makers to use Access for projects that will be made available to others is definitely more than I want to tackle

then again maybe Excel is best for this task - either way - it will be Excel that is used - at least at this point. and yes, somehow projects end up in one program or another for no solid reason

[lucas]

It's not so much that we don't want it to look like Excel so much as we want it to look like the online system which is basically just a white back ground with numbered questions and controls to click or input data. We have been using it for sometime and thought it would be helpful to give users something they would already be familiar with.

You can change your userform background to white, etc.