Conditionally Hide Columns

**Marcol** · 03-14-2010, 12:38 AM

Hi Gos

Sorry about the delay. Got called away.

Try this out.

I don't know what cells to lock and what your list drop downs rules are (I may have lost some while testing)

1/. I have added column "Q" to the macro activation, it should hide the columns based on "D" in the same row, as does column "D" with "Q".

Don't know if you need this, but it seemed logical to me. It is fairly obvious how to cancel this.

2/. I have made your new button toggle hide/unhide columns "S:CQ"

3/. The sheet will be protected when it is opened. To make changes to the sheet use unprotect (Password = "test") don't protect it again. Save/Close/Re-open to check changes. Same with changing the password in ThisWorkbook

4/. You should also Password protect the VBa to prevent any adventurous user going into the code and finding sheet Password.

Let me know how you get on.

Regards
Alistair

**Gos-C** · 03-14-2010, 10:41 AM

Looks good! Thanks a million!

I will test it thoroughly and get back to you later.

Gos-C

**Gos-C** · 03-17-2010, 10:37 PM

Hi Marcol,

I had some users test the sheet and they found that I made a few errors in parts of the logic.

I modified the code a bit, but I can’t get it to work like it should. If you can help me fix it, that would be great.

1. The Toggle Columns button should hide/unhide column Q together with columns U:CQ. Note: Columns S:T must always be shown when the toggle button is used. (Can you please verify that I coded it correctly.)

2. For column D, there should be three cases – each with different columns being hidden: (1) CM; (2) LR, MG and NR; and (3) CR, GN, PA and SC. (Can you please verify that I modified the code correctly.)

3. Columns S:T should be hidden when CM, LR, MG or NR is selected, and unhidden when any other code is selected. (Can you please verify that I modified the code correctly.)

4. If the user deletes the contents of a cell in column D, the contents of the cells in columns Q and S:CQ for that row should be deleted, and all columns unhidden.

5. Instead of:
‘if delete is used (in column Q) then unhide all columns and exit,

can it be:

' if delete is used (in column Q) then unhide the columns that were hidden (in other words, show the columns that were shown) when that selection was originally made and exit

In other words, when the user deletes the contents of a cell in column Q, the macro should unhide the columns that were hidden when that selection was made – effectively, reverting to the columns that were shown when the log code in column D (for that row) was selected. For example, if the user selects LR in column D, and “Exceeds plan limit” in column Q, but later deletes ”Exceeds plan limit” in column Q, the columns that were hidden when “Exceeds plan limit” was selected, should be unhidden.

Also, can you please explain “isect”? It’s the first time I am seeing it in macros.

Thanks again for your help.

Regards,
Gos-C

**Marcol** · 03-18-2010, 09:22 AM

1/. The Toggle Columns button should hide/unhide column Q together with columns U:CQ.

Changes
I have added "Q" to the if statement to define the exact condition to toggle under.
I have changed the Toggle Button caption to suit.

2/. For column D, there should be three cases

Changes
None - Your code modification is fine.

3/. Columns S:T should be hidden when CM, LR, MG or NR is selected, and unhidden when any other code is selected.

Changes
I have added Columns("S:T").Hidden = False to Case "CR", "GN", "PA", "SC" to fullfill the unhidden condition.

4/. If the user deletes the contents of a cell in column D, the contents of the cells in columns Q and S:CQ for that row should be deleted, and all columns unhidden.

Changes
I have added code to handle this condition. It will require testing to cover possible user situations I am unaware of
Note the use of Application.EnableEvents = False/True in this additional code.

5/. Instead of: ‘if delete is used (in column Q) then unhide all columns and exit,.........

Changes
I have added code to handle this condition.
Code in 4/. will override this on occassions , this is unavoidable, but it is probably correct to do so.

In most of the above, I cannot say for sure that the result is correct, I cannot verify the required conditions, only you can do this.
The methods used, however, should put you on the right track.

6/. Also, can you please explain “isect”? It’s the first time I am seeing it in macros.

This is the variable name Set by the Intersect function.
In this line

Code:

Set isect = Application.Intersect(Target, Range("D:D,Q:Q"))

Highlight Intersect press f1 to see an explaination by microsoft

In my experience Worksheet_Change is seldom used without it.

Help in Excel VBa is invaluable it contains a mine of information. Use it!!!

I have attached an amended workbook for your evaluation.

Please let me know how you get on with it.

Regards
Alistair

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

**Gos-C** · 03-18-2010, 12:59 PM

Thank you, Marcol. I have to run out to clear my pool table at Canada Customs so I will get back to you later.

Gos-C

**Gos-C** · 03-23-2010, 02:17 PM

Hi Marcol,

It is working fine now. Thank you very much for you help. I greatly appreciate it.

Kindest regards,
Gos-C

**Marcol** · 03-23-2010, 02:44 PM

Glad to have been of some help

Enjoy your pool ....... with beer?.....

Cheers
Alistair

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.