Controling the Worksheet_Change Event?

[DCSwearingen]

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.

[Don Guillett]

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
[email protected]
"DCSwearingen" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Bob Phillips]

'-----------------------------------------------------------------
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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[DCSwearingen]

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