How to refresh a sheet automaticaly (without pressing F9)

[brunces]

-------------------------
Doubt:

How to refresh a sheet (F9 - Calculate) automaticaly, using a shor
period of time for that?
-------------------------

Dear friends,

I'm trying to create this simple operation in Excel, but up to now,
haven't achieved success, for I don't know much about VBA. My poin
is...

I've got a sheet, something like this...

A1 = (any date)
A2 = (any time)
A3 = If A1 = today's date and A2 >= now (time), it returns me the wor
"FREE". If not, it returns me the word "BUSY".

I made it. I've got this function and it works fine. The problem is th
function only gets refreshed when I press F9.

Até aí, tudo bem. Fiz a fórmula aqui e ela está funcionando

I've made a Sub code (VBA)...

Private Sub Worksheet_Calculate()
Cells(3, 1).Calculate
End Sub

I don't know if that's the better way to do it, but it works fine! But
it only works when I change something in my sheet, something lik
typing or deleting... Any kinda of change!

so, my doubt is... How to make this Sub code (which I created) to b
executed every 1 minute or every 30 seconds? Or maybe, independing o
this Sub code which I created, how to refresh the sheet automaticaly
every 30 seconds, for example?

If somebody knows it and can help me, I appreciate it very much!

Thanks a lot for the attention.

Sincerely,

Brun

 

[Guest]

there is a timer event but its quirky and i wouldn't suggest using it.
Try putting your code in the Workbook_SheetChange event of 'ThisWorkbook' in your project window, that way any changes made anywhere in the worksheet will trigger the calculation.

----- brunces > wrote: -----

-------------------------
Doubt:

How to refresh a sheet (F9 - Calculate) automaticaly, using a short
period of time for that?
-------------------------

Dear friends,

I'm trying to create this simple operation in Excel, but up to now, I
haven't achieved success, for I don't know much about VBA. My point
is...

I've got a sheet, something like this...

A1 = (any date)
A2 = (any time)
A3 = If A1 = today's date and A2 >= now (time), it returns me the word
"FREE". If not, it returns me the word "BUSY".

I made it. I've got this function and it works fine. The problem is the
function only gets refreshed when I press F9.

Até aí, tudo bem. Fiz a fórmula aqui e ela está funcionando

I've made a Sub code (VBA)...

Private Sub Worksheet_Calculate()
Cells(3, 1).Calculate
End Sub

I don't know if that's the better way to do it, but it works fine! But,
it only works when I change something in my sheet, something like
typing or deleting... Any kinda of change!

so, my doubt is... How to make this Sub code (which I created) to be
executed every 1 minute or every 30 seconds? Or maybe, independing on
this Sub code which I created, how to refresh the sheet automaticaly,
every 30 seconds, for example?

If somebody knows it and can help me, I appreciate it very much!

Thanks a lot for the attention.

Sincerely,

Bruno