Hide a column "if"?

[tgcali]

Hello,

I have a workbook with 250 sheets. I need to be able to hide column F unless
cell E2 has the value "Level 1". If there is another value in E2 or it is
blank, I need column F to be hidden. I need this on all 250 sheets. Is this
possible?

Thanks in advance, you are all always to helpful!

tgcali

 

[Mike H]

Hi,

Alt +f11 to open VB editor, right click 'This Workbook' and insert module
and paste this in.

Sub sonic()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If Range("E2").Value <> "Level 1" Then
Columns("F:F").EntireColumn.Hidden = True
End If
Next
End Sub

Mike

 

[Mike H]

on reflection try this instead

Sub sonic()
Dim ws As Worksheet
For x = 1 To Worksheets.Count
If Worksheets(x).Range("E2").Value <> "Level 1" Then
Worksheets(x).Columns("F:F").EntireColumn.Hidden = True
End If
Next
End Sub

Mike

 

[tgcali]

Mike,

Thank you for your reply. I can't get it to work. I get a run time error on
the line right above End If.

Where/how am I supposed to right click 'this workbook' after I do altF11?
Maybe that's the trouble?

tgcali

 

[tgcali]

I get the error in the same place for both of them. Any suggestions?

tgcali

 

[Mike H]

Hi,

I've tried to replicate the error but can't so lets try again and a bit
simpler maybe.
It will work as worksheet code so right click the sheet tab of any sheet and
in the pop-up menu click 'View code'.

Paste this in on the right hand side and still in VB editor with the cursor
in the sub tap F5

Sub sonic()
For x = 1 To Worksheets.Count
If Worksheets(x).Range("E2").Value <> "Level 1" Then
Worksheets(x).Columns("F:F").EntireColumn.Hidden = True
End If
Next
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

After you press Alt+F11, you are taken into the VBA editor. In the upper
left portion of the editor window is the Project Explorer window (its title
bar starts with the word "Project")... if you don't see it, press Ctrl+R. In
the Project Explorer window is an item labeled "ThisWorkbook"... right-click
it and click on Insert/Module on the popup menu that appeared. A code window
will be displayed in the right-hand pane when you do this... paste the code
you were given in it. You can now call the 'sonic' subroutine from your own
code that you place in the worksheet's code window.

Rick

 

[Per Jessen]

Hi

Mike's code is working fine here.

It's difficult to figure out what is the problem, as you do not state the
entire error message.

Is any of the sheets protected?

Regards,
Per

 

[tgcali]

Thanks! I almost got it. The column is hidden for all the sheets, but it's
not displaying when the value "Level 1" is in cell E2. You've been very
helpful and I really appreciate it.

tgcali

 

[Mike H]

Now thta the problem I anticipated. The text for 'Level 1' in the worksheet
must be exactly the same text in the code.

If you've ended up hiding lots of columns you don't want to then this
routine will inhide them

Sub marine()
For x = 1 To Worksheets.Count
Worksheets(x).Columns("F:F").EntireColumn.Hidden = False
Next
End Sub

Paste it in belwo the other code and run it

Mike

 

[tgcali]

Ok, I made sure the text was the same. On the worksheets it is all caps, so I
changed it accordingly in the code. All the sheets have column F hidden
regardless of the entry in E2. I ran the second code. All the sheets have
column F displayed.

Any thoughts?

tgcali

 

[tgcali]

Ignore that last post of mine. Problem solved. You're a genius! Thank you so
much!

tgcali