|
Two event Macros in one worksheet
I am really new to Macro. I have to event macro from other users that work
fine and have no idea how i could place them both into one worksheet. The first auto names the Tab to whatever is in cell d4. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Name = Range("A1").Text CleanUp: Application.EnableEvents = True End Sub The second time stamps cells in b12:b36,h12:h36 when a number greater than 0 is entered in d12:d36,j12:j36. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub How can I right click and "View Code" and have them work independantly on the same worksheet. Please and Thanks, and Excel 2003 is the product :) fryguy |
Two event Macros in one worksheet
Just combine both sets of code into one procedure... This should be close...
Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo CleanUp With Target If .Cells.Count 1 Then Exit Sub If not Intersect(Target, Me.Range("A1")) Is Nothing Then Application.EnableEvents = False Me.Name = Range("A1").Text end if If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If End If End With CleanUp: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "fryguy" wrote: I am really new to Macro. I have to event macro from other users that work fine and have no idea how i could place them both into one worksheet. The first auto names the Tab to whatever is in cell d4. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Name = Range("A1").Text CleanUp: Application.EnableEvents = True End Sub The second time stamps cells in b12:b36,h12:h36 when a number greater than 0 is entered in d12:d36,j12:j36. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub How can I right click and "View Code" and have them work independantly on the same worksheet. Please and Thanks, and Excel 2003 is the product :) fryguy |
Two event Macros in one worksheet
Wow yeah that was perfect! I am just really getting into event code and
editing references and had no idea thanx :) fryguy. "Jim Thomlinson" wrote: Just combine both sets of code into one procedure... This should be close... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo CleanUp With Target If .Cells.Count 1 Then Exit Sub If not Intersect(Target, Me.Range("A1")) Is Nothing Then Application.EnableEvents = False Me.Name = Range("A1").Text end if If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If End If End With CleanUp: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "fryguy" wrote: I am really new to Macro. I have to event macro from other users that work fine and have no idea how i could place them both into one worksheet. The first auto names the Tab to whatever is in cell d4. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Name = Range("A1").Text CleanUp: Application.EnableEvents = True End Sub The second time stamps cells in b12:b36,h12:h36 when a number greater than 0 is entered in d12:d36,j12:j36. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub How can I right click and "View Code" and have them work independantly on the same worksheet. Please and Thanks, and Excel 2003 is the product :) fryguy |
Two event Macros in one worksheet
This will not work if the sheet is locked, even if the cells effected are
not. HELP! "Jim Thomlinson" wrote: Just combine both sets of code into one procedure... This should be close... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo CleanUp With Target If .Cells.Count 1 Then Exit Sub If not Intersect(Target, Me.Range("A1")) Is Nothing Then Application.EnableEvents = False Me.Name = Range("A1").Text end if If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If End If End With CleanUp: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "fryguy" wrote: I am really new to Macro. I have to event macro from other users that work fine and have no idea how i could place them both into one worksheet. The first auto names the Tab to whatever is in cell d4. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Name = Range("A1").Text CleanUp: Application.EnableEvents = True End Sub The second time stamps cells in b12:b36,h12:h36 when a number greater than 0 is entered in d12:d36,j12:j36. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub How can I right click and "View Code" and have them work independantly on the same worksheet. Please and Thanks, and Excel 2003 is the product :) fryguy |
| All times are GMT +1. The time now is 12:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com