Thread: 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:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 4 Then Exit Sub 'only works in Columns D
        If Target.Count > 1 Then Exit Sub
            If Intersect(Target, Cells(4, 4)) Is Nothing Then Exit Sub
ActiveSheet.Protect Password:="test", userinterfaceonly:=True
Cells.Columns.Hidden = False
Select Case Cells(4, 4).Value
Case "CM", “LR”, “MG”, “NR”
   Columns("U:AI").Hidden = True
   Columns("AP:BJ").Hidden = True
   Columns("BQ:CQ").Hidden = True
End Select
Select Case Cells(4, 17).Value
Case "Excessive time charges"
   Columns("AM:BP").Hidden = True
Case "Failure to produce documentation"
   Columns("AJ:AL").Hidden = True
   Columns("BK:BP").Hidden = True
Case "Not an eligible benefit"
   Columns("AJ:AO").Hidden = True
   Columns("BN:BP").Hidden = True
Case "Overcharged"
   Columns("AJ:BM").Hidden = True
Case Else
    Columns("U:CQ").Hidden = True
End Select
End Sub

However, it is not working.

Any help, please?

Thank you,
Gos-C

I think your problem is with this line:

Code:

ActiveSheet.Protect Password:="test", userinterfaceonly:=True

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