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

jayagoglia

New Member
Joined
Jul 24, 2014
Messages
3
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
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.

Code:
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

New Member
Joined
Jul 24, 2014
Messages
3
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

New Member
Joined
Jul 24, 2014
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,131
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top