If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
disable brian baulsom's worksheet change based on cell value.
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 |
#2
|
|||
|
|||
disable brian baulsom's worksheet change based on cell value.
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 --------------- Jacob Skaria "jat" wrote: 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 |
#3
|
|||
|
|||
disable brian baulsom's worksheet change based on cell value.
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 -- Regards, OssieMac |
Thread Tools | |
Display Modes | |
|
|