Help with Input Box dependent on the current date

[andyholford]

Hi all,

Apologies if this has been answered. I searched a lot but cannot find anything...

I'm filling in lots of data that will be updated monthly. The data is structured with the months of the year in columns across the top. As there are quite a few tabs and lots of automatic calculations and I don't want somebody inexperienced putting the latest monthly data in the wrong place.

Therefore, I want to create a button which when pressed by the user opens up a series of input boxes. Similar to this http://www.mrexcel.com/forum/excel-questions/512739-use-input-box-fill-cells.html.

However, because I have lots of monthly columns, it needs to only input data into the previous month's column. And even better, if data already exists in that box, it would be amazing if it could flag this up to the user...

It would also be good to have a cancel button there as well.

I'm hoping this is all pretty simple :S

Example form:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Website Visits[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Time on Site[/TD]
[TD]210[/TD]
[TD]207[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In this case I'd want a box that would enter details in Jun as its the last full month...


Thanks in advice for any advice or help
Andy

 

[ChrisM]

This kind of question raises all sorts of usability issues that you have to consider.

First, how many pieces of data will your users enter at once, a few, 10+, hundreds? If it's a lot of data, the normal Excel interface is the fastest, most user-friendly way to enter the data. If you start restricting that, and force your users to enter values by hand, one at a time (versus maybe they were copy/pasting before), they might not be too happy. If you use input boxes then this has to be done serially, one at a time. Will the user lose their way/place if they get distracted?

One option is to create a UserForm, and manually draw out all the input boxes all at once. Takes a lot more VBA code on your part, but you'll get the control you want.

Another option is to use Excel normally, but create an "input section" with just one column, let the user enter the data, then use VBA to assign that to the correct month column.

Another option is to use VBA to hide/unhide monthly columns that you don't want the user to see.

All of these options require one piece of data: the current month you are working on. So regardless, somewhere on an "admin" sheet you will need to specify the correct month #.

Can you clarify how many data entry operations we're talking about?

 

[andyholford]

This kind of question raises all sorts of usability issues that you have to consider.

First, how many pieces of data will your users enter at once, a few, 10+, hundreds? If it's a lot of data, the normal Excel interface is the fastest, most user-friendly way to enter the data. If you start restricting that, and force your users to enter values by hand, one at a time (versus maybe they were copy/pasting before), they might not be too happy. If you use input boxes then this has to be done serially, one at a time. Will the user lose their way/place if they get distracted?

One option is to create a UserForm, and manually draw out all the input boxes all at once. Takes a lot more VBA code on your part, but you'll get the control you want.

Another option is to use Excel normally, but create an "input section" with just one column, let the user enter the data, then use VBA to assign that to the correct month column.

Another option is to use VBA to hide/unhide monthly columns that you don't want the user to see.

All of these options require one piece of data: the current month you are working on. So regardless, somewhere on an "admin" sheet you will need to specify the correct month #.

Can you clarify how many data entry operations we're talking about?

Thanks Chris. There are approx 8 tabs with 10-20 inputs. Each will be completed by someone else potentially.

I have the month in as there are other calculations using it.

Thanks again

 

[ChrisM]

What do you think about hiding un-needed months, either showing just the one month in question, or maybe hiding the future months?

Regarding input boxes, since they are done one after another, to me, it doesn't sound like a good solution to ask your user to do this 10-20 times in a row. What if they mis-type item 15? Then they have to start back over at #1.

If it was me:
I would go with visually hiding/altering the worksheets first
Second choice I would have one single data entry sheet then use VBA to send that data to the correct sheets+cells
Third choice I would build a userform (lots of VBA code and complexity on your part)

But, if you REALLY like input boxes, then yes, that can be done.

What are you thoughts?

 

[andyholford]

What do you think about hiding un-needed months, either showing just the one month in question, or maybe hiding the future months?

Regarding input boxes, since they are done one after another, to me, it doesn't sound like a good solution to ask your user to do this 10-20 times in a row. What if they mis-type item 15? Then they have to start back over at #1.

If it was me:
I would go with visually hiding/altering the worksheets first
Second choice I would have one single data entry sheet then use VBA to send that data to the correct sheets+cells
Third choice I would build a userform (lots of VBA code and complexity on your part)

But, if you REALLY like input boxes, then yes, that can be done.

What are you thoughts?

Hi Chris,

Ive vey given it some thought and asked the end user and you Re right to be honest highlighting and protecting the cells for them to complete will be the best option.

Thanks for your time and consideration on this.

Andy

 

[ChrisM]

Definitely much easier to go with a less intrusive solution. Now, you can still use VBA to do the setup grunt work for you. How about when you set the month on your main sheet, it automatically protects and highlights the correct columns on each tab?

 

[andyholford]

I've done it via conditional formatting and EOMONTH formula's for now. Would it be complicated?

 

[ChrisM]

No, just tell me what you'd want to happen. It can be any combination of things: hiding columns/rows, protecting cells, etc.

Also specify what you want the trigger to be: a button, or every time you change your month in the main setting, etc

Next, we have to confirm your column layout on each sheet. Ideally they all are the same, maybe January always starts in cell B2 for example

Finally, we need to identify (by name), any sheets that don't need this formatting automatically applied