Conditionally Hide Columns

Hi all,

I am trying to hide all unnecessary columns to aid in the data entry process.

The data entry section is A4:CY1504.

For each record, column D would have one of several codes (CM, LR, MG, NR, etc.), and column Q would have one of several reasons (Excessive time charges, Failure to produce documentation, Not an eligible benefit, Overcharged, etc.) - drop-down lists.

When the user selects/enters either “CM”, “LR”, “MG” or “NR”, I want to hide columns U:AI, AP:BJ and BQ:CQ.

Then, for the same record, if the user selects/enters:

“Excessive time charges”, I want to hide columns AM:BP

“Failure to produce documentation”, I want to hide columns AJ:AL and BK:BP

“Not an eligible benefit”, I want to hide columns AJ:AO and BN:BP

“Overcharged”, I want to hide columns AJ:BM

When the user selects/enters any other code in column D, I want to hide columns U:CQ.

When the user saves the file or enters anything in column C, I want to unhide all columns.

So far, I have the following:

Please Login or Register  to view this content.

However, it is not working.

Any help, please?

Thank you,
Gos-C

I think your problem is with this line:

Please Login or Register  to view this content.

You cannot show and hide columns while the sheet is protected. Instead, make sure the sheet is unprotected (use the ActiveSheet.Unprotect command if necessary - you may need to test whether there's any protection first), then show and hide your columns, then protect the sheet.

Alternatively you could set up the different options as CustomViews and switch between them in code, but you would still need the sheet to be unprotected for the switch.

OK, haven't used the userinterfaceonly parameter before, so that's useful to know. Thanks for the heads-up.

Hi all,

Thanks for responding.

My sheet is protected, so I need to unprotect it in order to hide/unhide the columns.

Gos-C

Hi

That should not be the case when using the option UserInterfaceOnly with Protect

But I had not considered the implications of the user having to input for the macro to trigger

Macros will run provided they don't require user input through the sheet.

I am looking at that now - Give me an hour or so and look back then.

Thank you very much, Marcol. I appreciate it.

Gos-C

Hi Marcol,

I have attached a sample of the file (in Excel 97-2003 Workbook format) - I am using Excel 2007. The sheet protection password is "test". Thanks for you help.

Gos-C

Hi Allistair,

I am leaving the office now (I have to pick my wife up), so I will review your comments when I get home and then get back to you (tonight or tomorrow) all's well.

Thank you,
Gos

Hi Marcol,

Yes, all conditions should be tied to the active row.

Columns U:CQ are not requires for data entry when code CR, GN, PA or SC is entered.
Also, I had missed two other Columns:

S:T are only required when code CR, GN, PA or SC is entered. Therefore, they should be hidden when code CM, LR, MG or NR is entered.

(See the modified code.)

The first part of the code is working but the “Select Case Range (“Q”& Target.Row).Value” is not working. Can you give it another try?

I added a command button to unhide the columns when requires (for viewing the data). Would that be necessary? Also, I modified the Data Validation in column Q.

Thank you very much for you time.

I am in Toronto, Ontario

Looks good! Thanks a million!

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

Gos-C

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

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

Hi Marcol,

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

Kindest regards,
Gos-C