Auto change of data in a cell like a Slide show in ppt
 

Dear All,
Recently I have seen a exccel file. In that data of cell (contains proverbs
& quotation's) were auto changing to a new one with a time interval of 45 to
60 secs. How is it possible to change a data in a cell automatically. Is it
possible?
--
Regards

Auto change of data in a cell like a Slide show in ppt
 

It is possible using VBA OnTime code.

See Chip Pearson's site for instructions and code.

http://www.cpearson.com/excel/OnTime.aspx

You would need a list of the proverbs on a sheet and call them up in turn
using the OnTime code.


Gord Dibben MS Excel MVP

On Thu, 8 Apr 2010 20:59:01 -0700, Vital_ar
wrote:

Dear All,
Recently I have seen a exccel file. In that data of cell (contains proverbs
& quotation's) were auto changing to a new one with a time interval of 45 to
60 secs. How is it possible to change a data in a cell automatically. Is it
possible?

Auto change of data in a cell like a Slide show in ppt
 

Sir,
I don't have so much knowledge on VBA. If possible can you give me a example
of the code.
Thank you very much.
--
Regards
Vital

"Gord Dibben" wrote:

It is possible using VBA OnTime code.

See Chip Pearson's site for instructions and code.

http://www.cpearson.com/excel/OnTime.aspx

You would need a list of the proverbs on a sheet and call them up in turn
using the OnTime code.


Gord Dibben MS Excel MVP

On Thu, 8 Apr 2010 20:59:01 -0700, Vital_ar
wrote:

Dear All,
Recently I have seen a exccel file. In that data of cell (contains proverbs
& quotation's) were auto changing to a new one with a time interval of 45 to
60 secs. How is it possible to change a data in a cell automatically. Is it
possible?

.

Auto change of data in a cell like a Slide show in ppt
 

Do you want to be able to do other things in Excel while this slide show is
running?


Gord

On Sat, 10 Apr 2010 05:20:04 -0700, Vital_ar
wrote:

Sir,
I don't have so much knowledge on VBA. If possible can you give me a example
of the code.
Thank you very much.
--
Regards
Vital

"Gord Dibben" wrote:

It is possible using VBA OnTime code.

See Chip Pearson's site for instructions and code.

http://www.cpearson.com/excel/OnTime.aspx

You would need a list of the proverbs on a sheet and call them up in turn
using the OnTime code.


Gord Dibben MS Excel MVP

On Thu, 8 Apr 2010 20:59:01 -0700, Vital_ar
wrote:

Dear All,
Recently I have seen a exccel file. In that data of cell (contains proverbs
& quotation's) were auto changing to a new one with a time interval of 45 to
60 secs. How is it possible to change a data in a cell automatically. Is it
possible?

Auto change of data in a cell like a Slide show in ppt
 

Sir,
Thankyou Very much for the revert sir, No I don't want to do anyother works
in Excel while this file is open.
--
Regards


"Gord Dibben" wrote:

Do you want to be able to do other things in Excel while this slide show is
running?


Gord

On Sat, 10 Apr 2010 05:20:04 -0700, Vital_ar
wrote:

Sir,
I don't have so much knowledge on VBA. If possible can you give me a example
of the code.
Thank you very much.
--
Regards
Vital

"Gord Dibben" wrote:

It is possible using VBA OnTime code.

See Chip Pearson's site for instructions and code.

http://www.cpearson.com/excel/OnTime.aspx

You would need a list of the proverbs on a sheet and call them up in turn
using the OnTime code.


Gord Dibben MS Excel MVP

On Thu, 8 Apr 2010 20:59:01 -0700, Vital_ar
wrote:

Dear All,
Recently I have seen a exccel file. In that data of cell (contains proverbs
& quotation's) were auto changing to a new one with a time interval of 45 to
60 secs. How is it possible to change a data in a cell automatically. Is it
possible?

.

Auto change of data in a cell like a Slide show in ppt
 

OK

We can use Application.Wait in that case.

Assumes your list of proverbs/phrases is in Column A

This code will cycle through the cells from bottom to top.

Needs to be restarted at end of cycle.

Sub Slide_Show()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

FirstRow = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3 '3 for testing..........adjust
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

If Application.Wait(Now + TimeValue("0:00:03")) Then '03 for test
Range("G1").Value = Cells(iRow, 1).Value
End If
Next iRow
End Sub

If you wanted to be able to use Excel between slides and have a continuously
looping cycle you would use OnTime code.

This would be my preference.

Assumes the proverbs start in A2 in column A

See below................

Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 '5 secs test adjust to suit
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=True
End Sub


Sub TheSub()
Dim iRow As Long
Dim iCounter As Long
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
iCounter = Range("H1").Value
With Range("H1")
If .Value < 2 Then
.Value = LastRow
Else
.Value = iCounter - 1
End If
End With
For iRow = iCounter To FirstRow Step -1
Range("G1").Value = Cells(iCounter, 1).Value
Next
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=False
End Sub


Gord


On Mon, 12 Apr 2010 03:03:01 -0700, Vital_ar
wrote:

Sir,
Thankyou Very much for the revert sir, No I don't want to do anyother works
in Excel while this file is open.

Auto change of data in a cell like a Slide show in ppt
 

Dear Sir,
Thank you very much. Very Very thanks once again.
--
Regards
A. R. Vital

"Gord Dibben" wrote:

OK

We can use Application.Wait in that case.

Assumes your list of proverbs/phrases is in Column A

This code will cycle through the cells from bottom to top.

Needs to be restarted at end of cycle.

Sub Slide_Show()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

FirstRow = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3 '3 for testing..........adjust
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

If Application.Wait(Now + TimeValue("0:00:03")) Then '03 for test
Range("G1").Value = Cells(iRow, 1).Value
End If
Next iRow
End Sub

If you wanted to be able to use Excel between slides and have a continuously
looping cycle you would use OnTime code.

This would be my preference.

Assumes the proverbs start in A2 in column A

See below................

Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 '5 secs test adjust to suit
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=True
End Sub


Sub TheSub()
Dim iRow As Long
Dim iCounter As Long
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
iCounter = Range("H1").Value
With Range("H1")
If .Value < 2 Then
.Value = LastRow
Else
.Value = iCounter - 1
End If
End With
For iRow = iCounter To FirstRow Step -1
Range("G1").Value = Cells(iCounter, 1).Value
Next
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=False
End Sub


Gord


On Mon, 12 Apr 2010 03:03:01 -0700, Vital_ar
wrote:

Sir,
Thankyou Very much for the revert sir, No I don't want to do anyother works
in Excel while this file is open.

.

Auto change of data in a cell like a Slide show in ppt
 

Happy to help.

Gord

On Mon, 12 Apr 2010 23:21:01 -0700, Vital_ar
wrote:

Dear Sir,
Thank you very much. Very Very thanks once again.