CODEWITHSUNDEEP
excelvba
babozo
babozo

Reputation: 81

How to launch macro from a cell value

is it possible to launch a macro from the value on a cell ? In other word I want to convert that:

Sub Batch()

'Execute les macros BatchRbk,BatchR, BatchAbk et BatchA à differentes heures
Application.OnTime TimeValue("08:33:00"), "Batch.BatchRbk"
Application.OnTime TimeValue("08:37:00"), "Batch.BatchR"
Application.OnTime TimeValue("10:29:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("10:34:00"), "Batch.BatchA"
Application.OnTime TimeValue("10:39:00"), "Batch.BatchRbk"
Application.OnTime TimeValue("10:43:00"), "Batch.BatchR"
Application.OnTime TimeValue("11:29:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("11:32:00"), "Batch.BatchA"
Application.OnTime TimeValue("12:29:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("12:34:00"), "Batch.BatchA"
Application.OnTime TimeValue("13:29:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("13:32:00"), "Batch.BatchA"
Application.OnTime TimeValue("13:39:00"), "Batch.BatchRbk"
Application.OnTime TimeValue("13:44:00"), "Batch.BatchR"
Application.OnTime TimeValue("14:14:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("14:16:00"), "Batch.BatchA"
Application.OnTime TimeValue("14:30:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("14:32:00"), "Batch.BatchA"
Application.OnTime TimeValue("14:40:00"), "Batch.BatchAbk"
Application.OnTime TimeValue("14:43:00"), "Batch.BatchA"

End Sub

To that:

enter image description here

I a way that the user can setup himself that at hour "x" he wants to launch macro "y". Tell me if something is not clear enough, I'll rexplain it better. Thanks !

Upvotes: 1

Views: 48

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next way:

  1. Besides the data you show us in columns "D:E", you need another column with the real name of the Sub to be called ("Batch.BatchRbk", "Batch.BatchR" etc.). Let us put it in the F:F column, which may be hidden, or not.

  2. Then proceed in the next way:

Sub Batch()
 Dim ws as Worksheet,lastR as Long, i as long

 Set ws = ActiveSheet  
 lastR = ws.Range("D" & ws.rows.count).End(xlup).row
 For i = 2 to lastR
    Application.OnTime TimeValue(ws.Range("D" & i).value), ws.Range("F" & i).value
 Next i
End Sub

In F:F column you must place: "Batch.BatchRbk", "Batch.BatchR", "Batch.BatchAbk" and so on...

Upvotes: 3

Related Questions