OnTime Fuction

Aug 17, 2009

I am trying to get the following macro to run every half hour from 9am until 4pm.

I did try entering the following statement within the macro but I get the error message "compile error - arguement not optional"

TimeValue ("16:31:00"), "Mail_Selection_Range_Outlook_Body"

So here is my full macro. Thanks in advance for the help. (Using excel 2003)

Sub Mail_Selection_Range_Outlook_Body()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Sheets("Sheet1").Range("A1:C41").SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub

View 9 Replies


If(And()) Fuction (getting The "#NAME?)

Aug 20, 2009

I am getting the "#NAME?" in the sell containing the formula.

=IF(AND(B5="standard",B6="standard",B7="standard"),"standard",IF(AND(B5="Level 1",B6="Level 1",B7="Level 1"),"Level 1",IF(AND(B5="Level 2",B6="Level 2",B7="Level 2"),"Level 2",IF(AND(B5="Level 3",B6="Level 3",B7="Level 3"), "Level 3", IF(AND(B5="Level 4",B6="Level 4",B7="Level 4"),"Level 4", Custom)))))

Cell B5 contains "standard"
Cell B6 contains "Level 1"
Cell B7 contains "standard"
Cell B8 contains "standard"

View 2 Replies View Related

Fuction Combined With Sumproduct

Apr 2, 2009

I have a sumproduct function picking up data from a different worksheet. Because i have to much data it takes too much memory and it doesnt run that smoothly. Therefore i have hardcoded the function and tried to evaluate it using the EVAL() function from the morefunc add-in. However i don't get it to work. It continuously returns #VALUE.

This is the code i am trying to evaluate

SUMPRODUCT(--('Retrieve Depletions'!$A$4:$A$5000=$D4),--('Retrieve Depletions'!$B$4:$B$5000=$E4),--('Retrieve Depletions'!$C$4:$C$5000=$F4),--('Retrieve Depletions'!$D$4:$D$5000=$G4),--('Retrieve Depletions'!$E$4:$E$5000=$I4),--('Retrieve Depletions'!$F$4:$F$5000=$J4),'Retrieve Depletions'!H$4:H$5000)

View 9 Replies View Related

Count And Large In One Fuction

Jul 14, 2009

i'd like to get larget repeated number in one cell

not to make column for count and then take largest number, i need this in one cell only

View 9 Replies View Related

User Defined Fuction Is Being Called Several Times

May 4, 2007

When I use Excel's UI to create a function, the fn is being called several times. If I enter manually there is no problem.

Add this code in any module and goto menu, Insert, Function and then select
dosomething from the list and click OK. you will the msgbox is being displayed several times.

Public Function dosomething(aa As String, bb As String, cc As String, dd As String, ee As String, ff As String, gg As String, hh As String)
MsgBox "a"
End Function

View 9 Replies View Related

Left Fuction In VBA Code Getting Run Time Error 1004

Dec 25, 2009

I am trying to use the Left function in my VBA code but continue to receive run time error 1004 - Method 'range' of object '_global' failed. Not sure if the error originates for code being written incorrectly or if the left function is not available in VBA. I am basically trying to take the first 9-characters for each cell in column K and copy the values to column G.

View 3 Replies View Related

OnTime Method

Oct 3, 2007

with opening a workbook at a specific time and/or running a macro at a specific time.

View 12 Replies View Related

Scheduling Ontime

Feb 15, 2007

Is there a way to set the ontime application to run even when excel is turned off? I'd like to be able to refresh a data table every morning at 6:30, even if excel is not on. Ideally, if even my computer isn't on. The code below works, but only if the sheet is open to the exact worksheet.

Option Explicit
Public RunWhen As Double
Public Const cRunWhat = "The_Sub"
Sub StartTimer()
RunWhen = TimeValue("09:54")
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
End Sub

Sub The_Sub()
'my code here
End Sub

View 2 Replies View Related

Add An Ontime Function To A Macro

Oct 17, 2008

I'm using this to unhide rows one at a time:

View 4 Replies View Related

OnTime Method Error

Dec 23, 2009

I need a macro to run at a certain time on Wednesday's, and then a certain time on the other days.

I would like to use a cell to reference that time, but right now, I am just hard coding it, but I an not get it to run.

Ultimately what I am looking for is something like this: ....

View 12 Replies View Related

OnTime Procedure 'Not Found'

Feb 13, 2009

In trying to set up a structure for a bigger and better worksheet, I've come up against a problem that doesn't make sense to me: When the Worksheet is activated, the startTimer sub is in fact run with the OnTime application. But I keep getting the error message "Macro MakeNote not found," when that macro is in fact right there.

The code is very simple:

Sub Worksheet_Activate()
End Sub

Sub startTimer()
Application.OnTime Now + TimeValue("00:00:01"), "MakeNote"
End Sub

Sub MakeNote()
ActiveSheet.Cells(6, 5).Value = "YES"
End Sub

Sub TimerExpired()
End Sub

Sub checkNewBar()
ActiveSheet.Cells(4, 1).Value = "123"
End Sub

A second question: witll the TimerExpired macro run all by itself when the Timer expires, or do I need to do something special ?

View 9 Replies View Related

Application.OnTime Not Stopping

Nov 3, 2006

I am running a timer in XL using the Application .OnTime Method.

Everywhere I've looked tells me that the procedure to stop the timer is

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
End Sub

I have this code in with the timer, but when I hit F5 or go to Tools--->Macros--> and Run the StopTimer Macro the timer just keeps running.

how I can properly call this procedure and stop the timer?

View 9 Replies View Related

Ontime Run Macro On The First Of The Month

Mar 1, 2007

I have 80 worksheets in my workbook, each worksheet contains a table which must be updated on the first day of each month. On each sheet I need vba to duplicate the value in cell A10 to replace the next zero value in column E; duplicate the value in cell F10 to replace the next zero value in column J. Then, continue the same operation on the next sheet.

View 3 Replies View Related

Repeat Application.ontime

Apr 26, 2007

how to repeat the following line?

Private Sub Workbook_Open()
Application .OnTime TimeValue("22:02:00"), "Email"

so that everyday it runs at the same time Everyday? At the moment i have to close the workbook at somepoint during the day then relaunch so that it picks this line up, when this eventually "goes live" it will not be monitored as closely as it does now during testing.

View 2 Replies View Related

Application Ontime - Stopping It?

May 25, 2007

I'm using Application OnTime to fire a procedure every 30 mins. It works great, but the workbook tries to reopen itself when i close it (but not if i close excel entirely). I assume this is because I haven't stopped it anywhere. So I put a call to a procedure that has: Application.OnTime RunWhen, cRunWhat, , False

In the "Workbook_BeforeClose" event. This doesn't solve the problem. What am I missing?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_Open()
End Sub


Public RunWhen As Double
Public Const cRunIntervalSeconds = 1800 ' thirty mins
Public Const cRunWhat = "dothis"...............................

View 8 Replies View Related

Run OnTime Method If Condition Met

Sep 6, 2007

currently i have this:

Sub StartCycleB()
ActiveWorkbook.UpdateLink Name:="c:/a/b/c/test.xls", Type:=xlExcelLinks
RunWhen = Now + TimeSerial(0, 15, 0)
Application .OnTime RunWhen, "MyMacro", , True
End Sub

Works fine. Its updating and refreshing a file every 15 minutes. When a cell in the test.xls changes to TRUE, my sheet picks it up and changes to TRUE also.
So here is the problem: Once my sheet changes to TRUE, another macro activates in my sheet. This macro will automatically activate every 15 minutes if value is TRUE. BUT the problem is i ONLY WANT TO ACTIVATE THIS MACRO ONCE a day IF the value in my sheet is TRUE. So in summary how do i activate a macro based on a TRUE value on a cell, to happen ONCE a day and then stop?

View 3 Replies View Related

Using OnTime Schedule To Set Or Reset A Cell Value

Jul 24, 2013

I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function. It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.

Here is my code:


Sub RunOnTime()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "RunOnTime"
If Cells(1, 4).Value <= Cells(6, 2).Value Then
Cells(1, 4).Value = Cells(1, 4).Value + 1


View 1 Replies View Related

Close Workbook With Application.OnTime

Jul 28, 2009

I didn't receive a response to a previous post where I was trying to use a batch file to close and open a workbook at a scheduled time everyday. I was thinking about this problem over the weekend, and thought that it would probably be easier to just use a macro to close the file at a certain time everyday (if it is open) and then use a batch file to open the file a couple of minutes later.

I'm having problems with what I thought should be pretty simple though. I tried this to close the file at 5:15pm

View 14 Replies View Related

Cancelling OnTime When Workbook Is Closed?

Feb 9, 2012

How to correct the code below so that OnTime is cancelled when the workbook is closed?

Error on bold line is Run-time error '1004': Method 'On Time' of object'_Application' failed

Public RollTime As Date, CalculateTime As Date, RefreshTradesTime As Date

Sub CancelOnTimeProcess()
With Application
.OnTime RollTime, "RollMacro", , False
.OnTime CalculateTime, "AutoCalculation", , False
.OnTime RefreshTradesTime, "UpdateLinks", , False

[Code] .......

CancelOnTimeProcess is being called when the user closes the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

View 2 Replies View Related

Using OnTime Schedule To Set Or Reset Cell Value

Jul 23, 2013

I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function.

It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.

Here is my code:


Sub RunOnTime()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "RunOnTime"
If Cells(1, 4).Value

View 5 Replies View Related

Can OnTime Execute Sub In UserForm Module

Aug 26, 2008

I have a splash screen (user form) which displays fo a number of seconds. The UserForm_Activate routine uses Application.OnTime to run the KillSplash subroutine, which is defined in Module1. Thing is, I would like to move the "KillSplash" routine to the UserForm module. What should be the full name of KillSplash I need to pass to OnTime? What "trick" do I use to make "KillSplash" visible to Application.OnTime?

View 9 Replies View Related

Application.ontime With A Macro That Needs Parameters

Jun 25, 2009

Is it possible to use a macro that needs parmeters as part of an application.ontime instruction? - I am getting a macro cannot be found error message.
If I change the called macro so that parameters are not required, it is found OK (but obviously isn't doing what I want anymore!).

I reckon it is something to do with the quotations around the macro name

View 9 Replies View Related

Ontime Calling Class Method

Apr 29, 2006

I am trying to use the application .ontime procedure to run a class method, but having a little trouble with what to use as my procedure name. Do I have to include the name of the class, or the name of the instantiation of the class, or what? Here is kind of what I have in the "myClass" class module:

Option Explicit
Private WithEvents m_cmdButton As MSForms.CommandButton
Private m_iTimer As Double
Private Sub m_cmdButton_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' Set the time with the long delay
m_iTimer = Now + TimeSerial(0, 0, I_TIMER_DELAY_LONG)
' Start the timer
Call Application.OnTime(earliesttime:=m_iTimer, procedure:="ClickAgain", schedule:=True)
End Sub

Private Sub ClickAgain()
Call MsgBox("ClickAgain successfully called.", , "Boo yeah!")
End Sub

I have checked and the MouseDown event is being registered: it will work if I use a procedure in a standard module rather than the class method"ClickAgain". (I am only including the code relevant to the "ontime" procedure. My actual class has other methods to deal with setting the control source for the command button & everything.)

View 6 Replies View Related

OnTime Not Firing Via Scheduled Tasks

Dec 27, 2006

I scheduled a task to open excel and when it does (at a certain time), a little sub is supposed to run (couple minutes later) via the OnTime Method located in the Workbook Open Event. There is one weird thing though....

1. When Excel opens automatically through Scheduled Task (and you see the clean white sheet WITH gridlines), Sub does NOT run (that is, my file is NOT even opening).

2. When I myself open Excel manually WITHOUT opening a new workbook (in other words, in front of you there will be grey area without gridlines), Sub runs perfectly (in other words, my file opens automatically and Sub performs whatever it is supposed to do).

View 7 Replies View Related

Ontime Causes Workbook To Reopen After Close

Jun 18, 2007

i want vba to show a msgbox every 5 sec & it should start as soon as the file is opened. Below is the code & it works. Problem is, that when i close the file it immediately reopens and starts the "msgbox proess". Only if i close excel completely, this problem goes away (& thats not a very proffessional way to do it!!). Can someone pl look at this one & help ?

Private Sub workbook_open()
Call Macro4
End Sub

Sub Macro4()
Dim i As Integer
MsgBox "test!!"
NextTick = Now + TimeValue("00:00:05")
Application .OnTime NextTick, "Macro4"
End Sub

View 6 Replies View Related

Stop OnTime Method Macro

Aug 5, 2007

I have 2 macros, each called by the other to keep updates fast as per below,

Sub Macro1()
Cells.Replace What:=",29", Replacement:=",30", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Application .OnTime Now() + TimeValue("00:00:01"), "Macro2"
End Sub

Sub Macro2()
Cells.Replace What:=",30", Replacement:=",29", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Application.OnTime Now() + TimeValue("00:00:01"), "Macro1"
End Sub

I need to create a separate macro I can run to permanently stop these two cycling. And allow me to use Excel. Then, when I wish for these to cycle again, I simply run that macro.

View 4 Replies View Related

OnTime Method Opening Workbook

Aug 13, 2007

my wonderous spreadsheets have had a number of errors on them. One of them seems that the "autosave" VBA that we have been using seems to be hidden in memory. By this I mean that if I am working on this book and close it, on occassion the workbook tries to open itself again (it prompts for the password). Therefore I think something gets stuck "in memory" but I am not sure why it would act in this way?

The code is as follows:-

Public g_blnDate As Boolean

Public vartimer As Variant
Const TimeOut = 60 'in minutes

Sub Salva()
Call Tempo
End Sub

View 9 Replies View Related

Multiple OnTime In Workbook_Open Event

Dec 6, 2007

the following workbook open event which repeats a macro called "Refresh" every 30 minutes. However, there is another macro "tame_blph" that i wanna call at 12 midnight everyday.

Private Sub Workbook_Open()

Call tame_blph

NextTick = Now + TimeValue("00:30:00")

Application .OnTime NextTick, "Refresh", , True

End Sub

Is it even possible to have multiple Workbook open events and/or multiple NextTick or something?

View 3 Replies View Related

Stopping Ontime Macro At Fixed Time

Sep 15, 2014

I have the following macro that runs every time the system clock's second changes to 07 seconds. I however want it to stop running at 09:47 am. I have the code to stop the timer also (given below) but I am unable to stop the loop at 09:47 am. How do I do that?

Sub Timer()With Workbooks("book3.xlsm").Sheets("Sheet1")
End With
Application.OnTime DateAdd("n", 1, CDate(Left(Now, Len(Now) - 2) & "07")), "Timer"
End Sub

[Code] .....

View 2 Replies View Related

OnTime Event To Automate Excel Sheet?

Jun 20, 2014

how to automate my excel sheet i have got.

It has code which when you click the "Save" button it saves the document and then converts a copy as CSV with the same name.

I have been trying to automate this to run every 2 minutes but everything i have tried does not work.

I have tried using OnTime events but again does not work.

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved