Adjustable macro delay (by entering time in cell)

Question

Anwser: Change Cell format from D3 into Text instead of General or Time

I'm trying to delay a macro by a variable amount of time.

And I'd like to be able to adjust the amount of delay in my excel workbook without having to open a macro.

I tryed the code below but got :

run time error 13 (Type mismatch).

Is this solvable?

Private Sub CommandButton1_Click()
Dim i As Integer
Dim Response As Integer
    t = Range("D3")
    Application.Wait (Now + TimeValue("t"))

-Cell Format: First Tryed Time (0:00:00), then changed it back to general.

-The data in Cell "D3" : 0:00:00, 0:00:01, 0:30:00 or higher values.

-Time format= General --> t turns out to be something like 0,00000453*E-5 When "D3" is 0:00:01

Time format = time --> t turns out to be empty (using the 37:23:23 time format)

Can you specify, the data you input in "D3" and the format of that cell? Also on which line do you get the error! ;) — R3uK, Oct 28 '15 at 09:35

R3uK · Accepted Answer · 2015-10-28T10:02:06.513

2

In facts, it is pretty simple mistake, Just changeTimeValue("t") to TimeValue(t)

Why is that?

You need to input a time value formatted as text into TimeValue() function, you are getting this value properly into the variable t.

The problem is when you reuse this, because "t" is a String only containing the t letter and not the variable t!

Private Sub CommandButton1_Click()
Dim i As Integer
Dim Response As Integer
    t = CStr(Range("D3"))
    Application.Wait (Now + TimeValue(t))

edited Oct 28 '15 at 10:02

answered Oct 28 '15 at 09:36

R3uK

1

Thanks for the very clear explaination, but unfortunatly I'm still getting te same error. - Changed ("t") into (t) -Changed the cell's Category back into General instead of time -Tryed to enter 0:00:01 instead of 0:00:00 But both changes didn't make any sence. (thanks for your anwser, although it didn't work it was at least explained very clear. "give a man a fish and he'll have food for a day, learn a man how to fish and he will never be hungry again") – Cornelis Oct 28 '15 at 09:57
1

Set the **format of the cell D3 to Text** and take a look at the edited code, I added `CStr()` function to convert your input as a `String` to avoid any misinterpretation from Excel! ;) – R3uK Oct 28 '15 at 10:01
Changing Cell "D3"into text format was sufficient to fix my problem! Thanks a lot. – Cornelis Oct 28 '15 at 10:19

1 Answers1