Macro to hide/unhide columns

[JEB85]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
All I’m wanting to do is hide columns X:Y if d7 = ""<o></o>
If it’s not = "" I would like the columns to be visible<o></o>
<o></o>
Here’s the code I have created;<o></o>
<o></o>
Sheet6.Select<o></o>
If Range("D7") = "" Then<o></o>
Columns("X:Y").Select<o></o>
Selection.EntireColumn.Hidden = True<o></o>
If Range("D7") <> "" Then<o></o>
Columns("X:Y").Select<o></o>
Selection.EntireColumn.Hidden = False<o></o>
End If<o></o>
End If<o></o>
<o></o>
End Sub<o></o>
<o></o>
It hides the columns ok but doesn’t unhide them when necessary.<o></o>
<o></o>
Can anyone help?<o></o>
<o></o>
Thanks<o></o>

 

[VoG]

Try

With Sheet6
    .Columns("X:Y").Hidden = .Range("D7").Value = ""
End With

 

[JEB85]

Thanks, works great.

Is it possible to get it to run automatically without the need to press 'run'?

 

[VoG]

What changes D7 - you or does it contain a formula?

 

[JEB85]

D7 houses: sum(e7:f22)

If the sum is 0 then I'd like column x:y to be hidden

I the sum is >0 then I'd like columns x:y to be unhidden

The reason I put "" before was because I was unsure on what I was going to house in D7 but I think the best wasy to go is using the sum (as opposed to showing a blank if the sum is = 0)

Thanks

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Columns("X:Y").Hidden = Range("D7").Value = 0
End Sub

 

[JEB85]

Thanks a lot Vog, i'd inserted a module and hadn't used the specific sheet

 

[JEB85]

Hey Vog,

I've tried protecting the worksheet with a password and as a consequence have oboviously encountered problems with getting the code to work.

What do I need to do to unprotect the sheet, get the code to run, and protect the sheet again

I tried....

With ActiveSheet<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
.Unprotect Password:="xxx"<o></o>
End With

before your code and ...

With ActiveSheet<o></o>
.Unprotect Password:="xxx"<o></o>
End With

after your code without any luck.

Thanks

 

[VoG]

Perhaps like this

Private Sub Worksheet_Calculate()
Me.Unprotect Password:="xxx"
Columns("X:Y").Hidden = Range("D7").Value = 0
Me.Protect Password:="xxx"
End Sub

 

[JEB85]

Works a charm Vog, thanks.

The only slight downside is that the screen blinks everytime i enter a new value. Is there anyway to get around this happening?