Hide columns with certain coditions

[moosa Abrahams]

Hi there
I need to hide some columns in excel 2003

example

question in row A1 answer in Row A5
iF THE ANSWER IN a5 is positive then columns d-f must be visible or active
If the answer is negative row D-F should be hidden

My excel knowhow is medium

Any help will be appreciated

Mo


EggHeadCafe - Software Developer Portal of Choice
..NET Remoting - Marshal Objects ByRef
http://www.eggheadcafe.com/tutorial...030-2743e473b8ac/net-remoting--marshal-o.aspx

 

[JLGWhiz]

This should do it. Assuming of course, you mean by positive and negative =
Yes and No, Respectively. If not then see the second procedure.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If UCase(sh.Range("A5")) = "NO" Then
Columns("D:F").Hidden = True
Else
Columns("D:F").Hidden = False
End If
End Sub

If you mean numeric negative and positive.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If sh.Range("A5").Value >= 0 Then
Columns("D:F").Hidden = True
ElseIf sh.Range("A5") < 0 Then
Columns("D:F").Hidden = False
Else
MsgBox "The value is not numeric"
End If
End Sub

 

[JLGWhiz]

I had the symbols reversed for the numeric version. This corrects it.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If sh.Range("A5").Value < 0 Then
Columns("D:F").Hidden = True
ElseIf sh.Range("A5") >= 0 Then
Columns("D:F").Hidden = False
Else
MsgBox "The value is not numeric"
End If
End Sub

 

[JLGWhiz]

This will now give you the message if a non numeric value is entered in A5.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If Not IsNumeric(Range("A5").Value) Then
MsgBox "The value is not numeric"
ElseIf sh.Range("A5").Value < 0 Then
Columns("D:F").Hidden = True
ElseIf sh.Range("A5").Value >= 0 Then
Columns("D:F").Hidden = False
End If
End Sub

I overlooked the fact that an alpha character is considered by VBA to be a
value greater than a numeric character and therefore, greater than zero.