Solved: Run a macro by refernce to a cell

[Taggart]

I am trying to write a macro (A) which will run macro (B) at a specific time.
The time is indicated in a cell on the spreadheet

After macro (B) has run a new time will be indicated, I am happy to manually start the process again by reactivating macro (A) when the new time has been indicated.

The indicated time format is 00:00:00, the day is not relevant.

I already have macro (B) but after hours trawling thought forums and web sites I give up with macro (A)

Any help would be appreciated

Mike

[Aussiebear]

Try the following
[vba]
Private Sub TimeTrigger()
If Application.OnTime.TimeValue = Cells(1, 1).Value Then'<=== Change the location to suit
Call MacroB
End If
End Sub

[/vba]

[Taggart]

I have tried to adapt to my sheet but get the following message

Compile error
Argument not optional

Will the macro "marco2" keep running until the current time matches cell bv11 and my macro "system" is triggered?

Can you suggest a beginner's quide to learing VBA?

Mike

[VBA]
Sub Macro2()
'
' Macro2 Macro
'
'
Application.OnTime.TimeValue = Cells("bv11").Value
Application.OnTime Now + TimeSerial(0, 0, 5), "System"
End If

End Sub
[/VBA]

[Aussiebear]

I have tried to adapt to my sheet but get the following message

Compile error
Argument not optional

On which line does it error out on?

At the moment, from the code that you have provided, you are not actually testing the value in cell BV11, but rather you are instructing Excel to regard the cell BV11's value to equal the OnTime value. In the code sample that I provided, I was checking to see if the value in cell A1 equals the OnTime value then call MacroB. Please note that cells(1, 1) is Range ("A1"). Since you want the cell location to be BV11 then you should either use Range("BV11") or Cells(11, 74). When using cells as the reference it requires you to use it in the following syntax Cells( row #, column #)

Will the macro "marco2" keep running until the current time matches cell bv11 and my macro "system" is triggered?

Depends on what type of code is in macro 2. In your section of code the line Application.OnTime.TimeValue = Cells(bv11").Value is useless to you, not just because of the incorrect reference to BV11 but more importantly because you already know this, as this was true at some point to call Macro2 from Macro1

[vba]
Sub Macro2()
' Macro2 Macro
Application.OnTime.TimeValue = Cells("bv11").Value
Application.OnTime Now + TimeSerial(0, 0, 5), "System"
End If
End Sub
[/vba]
[quote]Can you suggest a beginner's quide to learing VBA?]/Quote]

There are a number of good methods for learning VBA. This forum has a instructional course. I find that I learn best by looking at the code that is provided in this forum and then following the assistance offered by the members. Please feel free to post lots of questions regarding what you are currently working with. Other than that there are numerous books available to purchase from good bookshops or online. Others here may well have a title or two for you to have a look at.

[Teeroy]

Hi Aussiebear,

Thanks for the tip about If Application.OnTime. I didn't know about it and could have definitely used this on a project 6 months ago . With some trial, lots of error, and reading MSDN I got it to work for me but it turned out different to your code. I couldn't get the Range.value to feed directly to the Timevalue (even though the cell was formatted as a date I had to format the value returned) and I separated Timevalue with a space rather than ".". I did find that I could feed the value to a variable that was explicitly a date.

Taggart,

Here's two methods that worked for me so I hope they can help you.

[vba]
Application.OnTime TimeValue(Format(Sheets("Sheet1").Range("A1").Value, "hh:mm")), "System"[/vba]

or

[VBA]
Dim dRun As Date

dRun = Sheets("Sheet1").Range("A1").Value
Application.OnTime TimeValue(dRun), "System"[/VBA]

[Taggart]

Aussiebear and Treeroy

It is so satisfying when a spreadsheet starts to work as intended.

Thnak you both for your assistance.

Mike