Auto run a macro

grizzlybear

New Member
Joined
Jul 26, 2007
Messages
21
Hi,

(Brand) New to VB so apologies in advance if this is a simple fix. I have a spreadsheet that monitors truck cycle times. I have it set to update every minute. One of the columns contains date/time info sorted last to first. Every time the first cell in this column updates, I want to run a macro that copies info from adjacent cells to another area of the sheet. I have seen the "byval target as range" posts, but don't really understand what it does, and whatever changes I make I can't get it to run.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
You can write macro on Worksheet_Change event

E.g. if A1 is the cell which u want to check for running macro then
(Right click the Tab and select "View Code" and paste the following macro)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    MsgBox "Change"
End If
End Sub
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,885
Office Version
  1. 365
Platform
  1. Windows
Hi, and welcome to Mr Excel

right_click the sheet's name tab, and selectect "view code."

There should be 2 dropdowns on the RHS.
Select "Worksheet" from the LH one, and "SelectionChange" from the RH one.
This is where you put any code you want to run when a particular range changes in your sheet.
Copy & paste so the whole lot looks thus:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Me.Range("B3") Then
        MsgBox "Hey, grizzlybear - Something's changed in B3!"
    End If
End Sub
This tests for a change in B3, and runs code accordingly.
Just change the reference of your "First cell in this column" from "B3" and obviously insert your code instead of the msgbox.

All the best
 

grizzlybear

New Member
Joined
Jul 26, 2007
Messages
21
Thanks for the suggestions guys, still not working though.

Sykes, I don't know how the me.range (B3) thing works. It throws up an error when it hits this line. I tried your code with my reference:

If Target = Me.Range("X2") Then
MsgBox "Hey, grizzlybear - Something's changed in B3!"
End If

I assume I need to change Me.range to something else, but I don't know what.

HSK,
I got your version to throw up the message box, but it won't run my macro. I tried your code with my reference:

If Target.Address = "$A$1" Then
Get_Cycle_Time
End If

The Get_Cycle_Time macro will run correctly by itself, but not when the cell changes. Note that this cell is a date/time cell (e.g. 26/07/2007 9:35:25 PM) so not sure if that is an issue.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

The Get_Cycle_Time macro will run correctly by itself, but not when the cell changes. Note that this cell is a date/time cell (e.g. 26/07/2007 9:35:25 PM) so not sure if that is an issue.

The Change event won't fire as the result of a formula calculation. For that you need to change it to a Calculate event. Note that it doesn't support the Target argument, but it shouldn't be too much of an issue as your date/time cells are in the same column, so you can use that event to sort and call your other macro.

Hope that helps,

Smitty
 

grizzlybear

New Member
Joined
Jul 26, 2007
Messages
21
Back again.

Unfortunately now whenever I change a linked cell in another worksheet the focus returns to the sheet with the calculations. I tried adding the application.screenupdating = false line as below but no luck.

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Get_Cycle_Time
Application.ScreenUpdating = True
End Sub

I also added it as the first and last lines of the macro, but still no joy
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I think what "Get_Cycle_Time" is doing may be the issue, because as it is there's nothing in your Calculate event that will activate the sheet.

Post that one if you want, especially if you 've got some selecting issues as I think you might.

Smitty

(Note I'll be in an off-site meeting all day tomorrow, so if I don't answer it's not to be rude...;))
 

grizzlybear

New Member
Joined
Jul 26, 2007
Messages
21
Smitty,

Good call. Rechecked the macro and added the "Application.EnableEvents = False" line. All good now...

Thanks again.
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

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