disable brian baulsom's worksheet change based on cell value.

[jat]

i am using the following worksheet change code in cell L5:

Private Sub Worksheet_Change(ByVal Target As Range)
'===========================================================
'- Brian Baulsom November 2008
'============================================================

'- Saves the value in this subroutine
Static L5value As Variant
'--------------------------------------------------------
'- check if the saved value is the same as it was
If L5value <> Range("L5").Value Then
L5value = Range("L5").Value
Application.ScreenUpdating = False
Sheets("Items").Select
Call DisplaySelected
Sheets("Purchase Order (Inventory)").Select
Application.ScreenUpdating = True
End If
End Sub

- the cell L5 uses a vlookup to return value and this works in every
instance. but on occassion, i need this code disabled, without actually
deleting the code.

the maco saves the file as a pdf without any issues, but when the cell L5
returns the value CLAIM, i need to save it as an excel file. as long as this
code is in the workbook, i keep getting various errors, but if i remove this
code it saves as an excel file fine.

any ideas would be appreciated.

thank you,

jat

 

[Jacob Skaria]

You can use Application.Enableevents = false to disable the events.. Please
make sure to set that to true after the code is executed..or once the change
is complete.


If this post helps click Yes

 

[OssieMac]

Hi Jat,

Another option is to Exit the sub if L5 = CLAIM

Private Sub Worksheet_Change(ByVal Target As Range)
'===========================================================
'- Brian Baulsom November 2008
'============================================================

If UCase(Range("L5").Value) = "CLAIM" Then
Exit Sub
End If

'- Saves the value in this subroutine
Static L5value As Variant

'--------------------------------------------------------
'- check if the saved value is the same as it was
If L5value <> Range("L5").Value Then
L5value = Range("L5").Value
Application.ScreenUpdating = False
Sheets("Items").Select
MsgBox "Called"
'Call DisplaySelected
Sheets("Purchase Order (Inventory)").Select
Application.ScreenUpdating = True
End If

End Sub