Help with Auto run macro , Multiple Columns in one code? I have been at this for 2 days please help

[jayagoglia]

Simply put I want a macro to auto run upon launching excel 2014, to create Auto Time stamps in cells , when data is WRITTEN or PASTED into adjacent Column cells. I want to do this in multiple "column pairs ". The columns are paired but rows function indepently.

Ex: Column A and B is a Pair: when ever data is written/copied into ANY cell in column B a time stamp gets created in the adjacent cell in Column A. data written B1 - time stamp A1, Data written B2 - timestamp A2.

Column F and G are a pair: when ever data is written/copied into ANY cell in column F a time stamp gets created in the adjacent cell in Column G.

having the ability to pair up columns in any fashion I want will be helpful. Also to have the Time stamp erase automatically when the adjacent cell data gets deleted too.


I have been trying to learn this on my own but was only able to achieve three of the four goals.
1 - auto run macros on opening excel 2013
2 - Autotime stamps
3 - achieving 1 & 2 above on one set of columns (A and B)
**4 - need to include more column pairs

here is the code I used and as far as I got:


Private Sub Workbook_Open()


End Sub
Sub Worksheet_Change(ByVal Target As Range)
Const ColumnsToMonitor As String = "B"
Const DateColumn As String = "A"
Application.EnableEvents = False
If Not Intersect(Target, Columns(ColumnsToMonitor)) Is Nothing Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = Now
End If
Application.EnableEvents = True
End Sub


Const ColumnsToMonitor As String = "F"
Const DateColumn As String = "G"
Application.EnableEvents = False
If Not Intersect(Target, Columns(ColumnsToMonitor)) Is Nothing Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = Now
End If
Application.EnableEvents = True
End Sub

The last section in italics was not recognized and I have error messages with ambiguous naming.

I would like a welcome message to appear upon application launch as well if possible.
Please help ...and thank you
Jay Agoglia

 

[scott_n_phnx]

As a rule, you can only have a single Worksheet event per worksheet. However, there is a work-around for it. In order to run more than one event, you must Call each from within the Worksheet event. The example below should give you an idea on how to accomplish this.

Private Sub Worksheet_Change(ByVal Target As Range)
	Call Macro1(Target)
	Call Macro2(Target)
End Sub

Sub Macro1(ByVal Target As Range)
	Your First code
End Sub

Sub Macro2(ByVal Target As Range)
	Your Second code
End Sub

In the case of your code, because you are monitoring different columns, you should break the code up into two. Once done, it <i>should</i> work. The error about ambiguous naming is due to your Const having the same ColumnsToMonitor names, but the columns are changed. Even breaking the code into two, I would suggest naming one of them <b>ColumnsToMonitor1</b> and then other <b>ColumnsToMonitor2</b>. And doing the same with your DateColumn.

 

[jayagoglia]

Hi ,

thank you, What about the auto run feature so this code is in effect when excel is launched? how do I add that into this solution you provided?

thank you again!

 

[jayagoglia]

Here is the code i put together based on your suggestions:

Private Sub Workbook_Open()


End Sub
Sub Worksheet_Change(ByVal Target As Range)
Call Macro1(Target)
Call Macro2(Target)
End Sub


Sub Macro1(ByVal Target As Range)
Const ColumnsToMonitor1 As String = "B"
Const DateColumn As String = "A"
Application.EnableEvents = False
If Not Intersect(Target, Columns(ColumnsToMonitor)) Is Nothing Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = Now
End If
Application.EnableEvents = True
End Sub


Sub Macro2(ByVal Target As Range)
Const ColumnsToMonitor2 As String = "F"
Const DateColumn As String = "G"
Application.EnableEvents = False
If Not Intersect(Target, Columns(ColumnsToMonitor)) Is Nothing Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = Now
End If
Application.EnableEvents = True
End Sub