Add Condition to Worksheet Change Event

[hrayani]

Hello Friends,

I am using the below code in my file...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Address = Range("C3").Address Then

Select Case Target.Value

Case "1SHOW QUANTITY"
Call one_show_quantity

Case "1SHOW VALUE"
Call one_show_value

Case "1SHOW QUANTITY & VALUE"
Call one_show_quantity_value

Case "2SHOW QUANTITY"
Call two_show_quantity

Case "2SHOW VALUE"
Call two_show_value

Case "2SHOW QUANTITY & VALUE"
Call two_show_quantity_value

End Select

End If

End Sub

I would like to add second criteria in the code.

Cell C3 has numbers
Cell B4 has text (Show Quantity, Show Value, Show Quantity & Value)

I want the code to trigger as soon as value in either cell changes.

Any help would be appreciated.

Regards,

Humayun

 

[Michael M]

Like this ???

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B4,C3")) Is Nothing Then Exit Sub

 

[hrayani]

Thanks for the reply Michael

Its giving Compile error: End If without block If

Any Idea ???

 

[Michael M]

Take out the last end if...2nd last line in the macro

 

[hrayani]

Done That.

This time not giving any error but the code is not working.

 

[Michael M]

works for me...so your code is now ??

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B4,C3")) Is Nothing Then Exit Sub
Select Case Target.Value

Case "1SHOW QUANTITY"
Call one_show_quantity

Case "1SHOW VALUE"
Call one_show_value

Case "1SHOW QUANTITY & VALUE"
Call one_show_quantity_value

Case "2SHOW QUANTITY"
Call two_show_quantity

Case "2SHOW VALUE"
Call two_show_value

Case "2SHOW QUANTITY & VALUE"
Call two_show_quantity_value

End Select

End Sub

 

[hrayani]

Thanks for reply Michael,

It is still not working....

I found a solution.....

Here it is.... working just Perfect

Private Sub Worksheet_Change(ByVal Target As Range)


If ActiveSheet.Range("C3") = 1 And Range("B4") = "SHOW QUANTITY" Then
Call one_show_quantity
End If
If ActiveSheet.Range("C3") = 1 And Range("B4") = "SHOW VALUE" Then
Call one_show_value
End If
If ActiveSheet.Range("C3") = 1 And Range("B4") = "SHOW QUANTITY & VALUE" Then
Call one_show_quantity_value
End If
If ActiveSheet.Range("C3") = 2 And Range("B4") = "SHOW QUANTITY" Then
Call two_show_quantity
End If
If ActiveSheet.Range("C3") = 2 And Range("B4") = "SHOW VALUE" Then
Call two_show_value
End If
If ActiveSheet.Range("C3") = 2 And Range("B4") = "SHOW QUANTITY & VALUE" Then
Call two_show_quantity_value
End If
End Sub

The code does trigger as soon as any value changes in the range.

There is one thing i would like to ask that when i select cell C3 to change the value then the selection should remain at cell C3
and same for B4

here is the macro

Sub one_show_quantity()

Application.ScreenUpdating = False
Columns("E:AD").Select
Selection.EntireColumn.Hidden = False
Columns("F:AD").Select
Selection.EntireColumn.Hidden = True

End Sub

Sub one_show_value()

Application.ScreenUpdating = False
Columns("E:AD").Select
Selection.EntireColumn.Hidden = False
Range("E:E,G:AD").Select
Selection.EntireColumn.Hidden = True

End Sub

Sub one_show_quantity_value()

Application.ScreenUpdating = False
Columns("E:AD").Select
Selection.EntireColumn.Hidden = False
Range("G:AD").Select
Selection.EntireColumn.Hidden = True

End Sub

I can write this Range("C3").Select to every macro but the problem is when i select B4 to change then also it will go
to Cell C3.... Is there any way i can tell the code to keep the selection where it is ???

 

[Michael M]

remove all select.Selection from you macros.....like this

Sub one_show_quantity()

Application.ScreenUpdating = False
Columns("E:AD").EntireColumn.Hidden = False
Columns("F:AD").EntireColumn.Hidden = True

End Sub

 

[hrayani]

Thanks Michael,

Working Just perfect Now

 

[Michael M]