How to not execute Worksheet_Change while making changes to it via

[Adnan]

Hi everyone,

With the great help of Lenze (MrExcel MVP), I managed to modify his code to
suit my needs but the issue is that I only need it to work in GUI and not
while I am coping or manipulating via code (i.e.:
Workbooks("Book1.xls").Worksheets("").Cells(1,1).Value =
workbooks("Book1.xls").worksheets("").cells(1,1).value. Anywayes, my
questions is as to how do I prevent or stop this code execution when I am
extracting data into this sheet from another workbook i have a code that
extract automatically)...

I managed to do this via programming in VBE (assigning the following code
behinde the sheet after the extraction from other workbook is complete) but
then this requires all users to check ‘trust Visual Basic’ checkbox and I do
not know who is going to be using this workbook.

I also tried something to do with, if workbook... worksheet("Data").activate
= false then do not execute the code, otherwise, do. Had no luck thus far.

Any help/tip would be much appreciated.
Adnan



Option Explicit
Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 2 And Target.Column < 16 Then
ActiveSheet.Cells(Target.Row, 2) = Cells(Target.Row, 2).Value & Chr(10)
& "Previous Value was " _
& preValue & Chr(10) & "Revised " & Format(Now(), "m/d/yy h:mm;@") &
Chr(10) & "By " & Environ("UserName")
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If

End Sub

 

[Gary''s Student]

Dis-able events while the macro runs:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
' your code goes here
'
Application.EnableEvents = True
End Sub

 

[Mike H]

Hi,

Disable events in your code, do what you want then re-enable events

Application.enableevents=false
'do things
Application.enableevents=true

Mike

 

[Jacob Skaria]

You can disable the events...

Sub Macro()
Application.EnableEvents = False
'your other code

'/your other code
Application.EnableEvents = True
If this post helps click Yes

 

[r]

Private Sub Worksheet_Change(ByVal Target As Range)
'… declaration
Static SalvaLoop As Boolean

If SalvaLoop Then Exit Sub

SalvaLoop = True
If Target.Column > 2 And Target.Column < 16 Then
ActiveSheet.Cells(Target.Row, 2) = _
Cells(Target.Row, 2).Value & _
Chr(10) & _
"Previous Value was " & _
preValue & _
Chr(10) & _
"Revised " & _
Format(Now(), "m/d/yy h:mm;@") & _
Chr(10) & _
"By " & _
Environ("UserName")
End If
SalvaLoop = False

End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Adnan]

I tryied all four responses and it still is the same, makes no difference.

I may have not been clear in my previous note. The main sheet called "Data"
has to track changes that the end user makes. However, in this sheet I also
make changes (auto populate it via code that I have in module1. What I want
this code behind this sheet to do is to not track changes I makes when
transplanting data (which is done via code). Track only GUI (graphical User
Interface) changes made on sheet not those that are performed via vba.

Thanks all of you for all the help provided.
Adnan