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
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.
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
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.
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, _ schedule:=True End Sub
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() startTimer 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() checkNewBar 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 ?
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.
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.
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) StopTimer End Sub
Private Sub Workbook_Open() StartTimer End Sub
(module1)
Public RunWhen As Double Public Const cRunIntervalSeconds = 1800 ' thirty mins Public Const cRunWhat = "dothis"...............................
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?
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:
VB:
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
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
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:
Code:
Sub RunOnTime() dTime = Now + TimeValue("00:00:10") Application.OnTime dTime, "RunOnTime" If Cells(1, 4).Value
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?
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
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.)
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).
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
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.
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() ActiveWorkbook.Save Call Tempo End Sub
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?
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?
VB: Sub Timer()With Workbooks("book3.xlsm").Sheets("Sheet1") Range("B10:E10").ClearContents End With Application.OnTime DateAdd("n", 1, CDate(Left(Now, Len(Now) - 2) & "07")), "Timer" End Sub
