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

  • Thread starter Thread starter jat
  • Start date Start date
J

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
 
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
 
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
 
Back
Top