| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Event Macros Help Needed | Excel Discussion (Misc queries) | |||
| worksheet event macros | Excel Programming | |||
| event macros - xl 2003 | Excel Worksheet Functions | |||
| Event Procedures: Event on Worksheet to fire Event on another Worksheet | Excel Programming | |||
Hybrid Mode
