Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MenuBar & Event problems | Excel Discussion (Misc queries) | |||
Disable SelectionChange Event | Excel Discussion (Misc queries) | |||
Unable to Start Excel Event ID 2001 | Excel Discussion (Misc queries) | |||
Using excel to manage event - ANY input deeply appreciated! :-) | Excel Discussion (Misc queries) | |||
Worksheet Row Change event | Excel Discussion (Misc queries) |