Controling the Worksheet_Change Event?
 

How can I control the worksheet change event? I only want it to
actually run if a value in column 7 (G) is selected.

I want the user to be able to enter data in column 7, run the
worksheet_change event to update values in other columns in the same
row.

However, as I step through my macro, each time the macro updates the
values in the other columns, the Worksheet_Change seems to run itself
as a nested routine.

If I update a dozen values based on the users entry, the change is
nested a dozen times.

I am sure there is a limit to how many times this can be down without
crashing Excel.


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=545561

Controling the Worksheet_Change Event?
 

As always, post YOUR coding efforts for comments

if target.column< 7 then exit sub
or
if target.address< "$A$7" then exit sub

you may also need
application.enableevents=false
code
application.enableevents=true

--
Don Guillett
SalesAid Software

"DCSwearingen"
wrote in message
news:DCSwearingen.28dfrb_1148574001.6331@excelforu m-nospam.com...

How can I control the worksheet change event? I only want it to
actually run if a value in column 7 (G) is selected.

I want the user to be able to enter data in column 7, run the
worksheet_change event to update values in other columns in the same
row.

However, as I step through my macro, each time the macro updates the
values in the other columns, the Worksheet_Change seems to run itself
as a nested routine.

If I update a dozen values based on the users entry, the change is
nested a dozen times.

I am sure there is a limit to how many times this can be down without
crashing Excel.


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=545561

Controling the Worksheet_Change Event?
 

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "G:G"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"DCSwearingen"
wrote in message
news:DCSwearingen.28dfrb_1148574001.6331@excelforu m-nospam.com...

How can I control the worksheet change event? I only want it to
actually run if a value in column 7 (G) is selected.

I want the user to be able to enter data in column 7, run the
worksheet_change event to update values in other columns in the same
row.

However, as I step through my macro, each time the macro updates the
values in the other columns, the Worksheet_Change seems to run itself
as a nested routine.

If I update a dozen values based on the users entry, the change is
nested a dozen times.

I am sure there is a limit to how many times this can be down without
crashing Excel.


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=545561

Controling the Worksheet_Change Event?
 

As suggested by Don Guillett, here is the code I ended up with:

Main module code:
Option Explicit
Declare Function GetUserName Lib "ADVAPI32.DLL" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Function LogonUserName()
Dim S As String
Dim n As Long
Dim Res As Long
Dim myName As String
S = String$(200, 0)
n = 199
Res = GetUserName(S, n)
myName = LCase(Left(S, n - 1))
LogonUserName = UCase(Left(myName, 3))
End Function


Worksheet code
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G:G"
Dim nRow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsEmpty(Target.Value) Then
Cells(Target.Row, 9).Value = Null
Cells(Target.Row, 8).Value = Null
Else
Cells(Target.Row, 9).Value = LogonUserName()
Cells(Target.Row, 8).Value = Now
nRow = Target.Row + 1
InsertRow nRow
End If
Target.Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'-----------------------------------------------------------------
Private Sub InsertRow(nRow As Long)
Worksheets("RMU").Unprotect password:="myPassword"
If IsEmpty(Range(Cells(nRow, 6).Address).Value) Then
If Not IsEmpty(Cells(nRow - 1, 7).Value) Then
Rows(nRow).Insert Shift:=xlDown
Rows(nRow - 2).Copy
Rows(nRow).PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, SkipBlanks:= False,
Transpose:=False
Cells(nRow, 6).FormulaR1C1 = "=R[-1]C+1"
Cells(nRow, 6).Copy
Cells(nRow, 6).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= False,
Transpose:=False
Application.CutCopyMode = False
End If
End If
Worksheets("RMU").Protect password:="myPassword"
End Sub


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=545561