Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to not execute Worksheet_Change while making changes to it via
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).V alue = workbooks("Book1.xls").worksheets("").cells(1,1).v alue. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to not execute Worksheet_Change while making changes to it via
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 -- Gary''s Student - gsnu200856 "Adnan" wrote: 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).V alue = workbooks("Book1.xls").worksheets("").cells(1,1).v alue. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to not execute Worksheet_Change while making changes to it via
Hi,
Disable events in your code, do what you want then re-enable events Application.enableevents=false 'do things Application.enableevents=true Mike "Adnan" wrote: 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).V alue = workbooks("Book1.xls").worksheets("").cells(1,1).v alue. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to not execute Worksheet_Change while making changes to it via
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 --------------- Jacob Skaria "Adnan" wrote: 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).V alue = workbooks("Book1.xls").worksheets("").cells(1,1).v alue. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to not execute Worksheet_Change while making changes to it via
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/...ternative.html "Adnan" wrote: 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).V alue = workbooks("Book1.xls").worksheets("").cells(1,1).v alue. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to not execute Worksheet_Change while making changes to it via
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 "Adnan" wrote: 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).V alue = workbooks("Book1.xls").worksheets("").cells(1,1).v alue. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Execute Worksheet_Change before recalculation | Excel Programming | |||
Worksheet_Change event won't fire to execute Macro??? | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |