VBA Code Required

[hrayani]

Hello Friends,

I need a VBA code for worksheet change event for the following

Cell B4: Show Quantity, Show Value or Show Value & Quantity

Columns E,G & I Contains Quantity
Columns F,H,& J Contains Value


If cell B4 is set to Show Quantity
Then Only Columns E,G & I should be shown if there is any value > "0" in E102, E102 & I102

If cell B4 is set to Show Value
Then Only Columns F,H & J should be shown if there is any value > "0" in F102, H102 & J102

If cell B4 is set to Show Quantity & Value
Then All Columns From E to J should be shown if there is any value > "0" E102 to J102


Any help would be appreciated,

Thanks

Humayun

 

[JustynaMK]

Hi Humayun,

Hopefully this code is a good starting point. Open your VBA (Alt+F11), double-click on a given sheet and paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngLastRow          As Long
    
    If Not Intersect(Target, Range("B4")) Is Nothing Then
        lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row
        If lngLastRow >= 102 Then
            Range("E:J").EntireColumn.Hidden = True
            Select Case Range("B4")
                Case "Show Quantity"
                    If Application.Sum(Sgn(Range("E102").Value), Sgn(Range("G102").Value), Sgn(Range("I102").Value)) >= -1 Then
                        Range("E:E,G:G,I:I").EntireColumn.Hidden = False
                    End If
                Case "Show Value"
                    If Application.Sum(Sgn(Range("F102").Value), Sgn(Range("H102").Value), Sgn(Range("J102").Value)) >= -1 Then
                        Range("F:F,H:H,J:J").EntireColumn.Hidden = False
                    End If
                Case "Show Quantity & Value"
                    If Application.Sum(Sgn(Range("E102").Value), Sgn(Range("F102").Value), Sgn(Range("G102").Value), _
                        Sgn(Range("H102").Value), Sgn(Range("I102").Value), Sgn(Range("J102").Value)) >= -4 Then
                        Range("E:J").EntireColumn.Hidden = False
                    End If
            End Select
        End If
    End If
End Sub

 

[hrayani]

Thanks Justyna for your reply,

I tried the code you provided.... Its only working with the change in Cell B4 Like Show Quantity, Value or Both.
But the code is not looking at the values in row 102... Where it should hide the column where the total which is in row 102 is equal to 0..


I have found one solution myself.....

I have used these two formulas in Quantity & Value Columns in row 103

=IF(AND(E102>0,$B$4="SHOW QUANTITY"),TRUE,IF(AND(E102>0,$B$4="SHOW QUANTITY & VALUE"),TRUE))

=IF(AND(F102>0,$B$4="SHOW VALUE"),TRUE,IF(AND(F102>0,$B$4="SHOW QUANTITY & VALUE"),TRUE))

Then i have used this VBA Code.....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("E103:AL103")
If xRg.Value = False Then
xRg.EntireColumn.Hidden = True

Else
xRg.EntireColumn.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
End Sub

Problem Solved.....

Dear, Thanks for your time and effort....

 

[JustynaMK]

Ahh sorry, I wrote my code to check if there is any value which is > "0" (positive - thus the Sign formula) in a given range of cells.
Glad you could solve it! Take care.

 

[hrayani]

thanks