Macro to selectively hide columns

[CFlack8472]

Hi all

I am preparing our departments budget, but it is now getting a bit cumbersome. So I'm spending a lot of time hiding and unhiding columns. I have next to no knowledge of VBA.

In the attached .xls you can see row 5 denotes the month, row 4 denotes whether the column contains "budgeted" "actual" the "variance" or "variance analysis" information.

I ideally need a tick box (but drop down will suffice) which will allow me to selectively hide the values for each month. So say for example I want to view the data for March. I select March and it displays the values for March only and hides all the other months and columns pertaining to them. Then a few minutes later I need to unhide them so it would just be a matter of selecting all the months. If a tick box or drop down can be placed in cell E1 that would be ideal.

See row 3 to see what I mean:
Then another one (drop down or tick box) which would enable me to just view the Budget and Actuals or any arbitrary selection of the 4 types of column ("budgeted" "actual" "variance" or "variance analysis") I have.
It would probably be best to segregated the selectors and place it separately to the month selector.

So you would end up with one tick box to select the months and one to select the "budgeted" "actual" "variance" or "variance analysis"
and another one to choose the months.

As time progresses I will be adding new columns as well. We're in April now but I don't have the figures for the month end so of course I can't calculate the variance or variance analysis. If the macro is fairly adaptable and able to take into account additonal columns and months as I add them that would be ideal. Otherwise I will have to learn how to customise the macro, but I guess that would be good.

[CFlack8472]

No one have any ideas?

[venkat1926]

for writing a macro by somebody else it would be better if the data is given in correct pristine configuration though not necessarily full data (extract would be enough)without any hidden rows or columns,without any merging, better without coloring etc. these cosmetic changes must be done only just before presentation to the user or to the boss.

I have unhidden all rows and columns.

I am attaching the file "cflack......xls"
there are two input boxes
for "bud" box I have entered budge
for mth box I have entered july 11
this means all other columns form C to Ak all columns other than c will be hidden (of coure A and B will not be hidden as they are row headings.)

(now remember your input boxes are filled the spelling should be exact as in rows 3 and five successively.
first input box from row 3
second input box from row 5

I have copied sheet 1 in sheet 2 also for safety reason
for additional safety save your original file somewhere else so that it can be retrieved if there is problem
for retesting
rerun the macro "test"
and give correct responses to input boxes.

the macros are given for general purposes(they are in the module of the attached file)

Please Login or Register  to view this content.

Please Login or Register  to view this content.