OLE Object on Sheet updates Value in Code but not its Display
 

This is pretty simple, but puzzling. I placed a Label control on a
worksheet. I read in a range of text values that I want to display as
the Label caption after a brief delay. The delay time is read in
seconds. The For Each loop is supposed to update the caption.
However, the caption value does indeed update because I tested it by
putting it out in a MsgBox. But the caption graphic does not update.
Code below. Any ideas appreciated.

Thanks,

SteveM

Sub PlayAff()

Dim affRng As Range
Dim affCell As Range
Dim startTime As Variant
Dim delayTime As Integer
Dim ws As Worksheet

Range("AffAnchor").Select
Range(Selection, Selection.End(xlDown)).Select

Set affRng = Selection
delayTime = Range("DelayTime")
Set ws = Sheets("Play")
ws.Activate
ws.OLEObjects("AffLabel").Object.Caption = ""

For Each affCell In affRng
startTime = Timer
ws.OLEObjects("AffLabel").Object.Caption = affCell
MsgBox ws.OLEObjects("AffLabel").Object.Caption ' Shows
correct text value
Do While Timer <= startTime + delayTime ' caption display
not updated.
Loop
Next

End Sub

OLE Object on Sheet updates Value in Code but not its Display
 

Try adding a DoEvents statement within your Do loop:

Do While Timer <= startTime + delayTime ' caption display
DoEvents
Loop

As a general principle, using a tight loop to wait an elapsed time isn't the
best way. You should probably use Application.Wait or the Win32
Sleep/SleepEx function.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"SteveM" wrote in message
...
This is pretty simple, but puzzling. I placed a Label control on a
worksheet. I read in a range of text values that I want to display as
the Label caption after a brief delay. The delay time is read in
seconds. The For Each loop is supposed to update the caption.
However, the caption value does indeed update because I tested it by
putting it out in a MsgBox. But the caption graphic does not update.
Code below. Any ideas appreciated.

Thanks,

SteveM

Sub PlayAff()

Dim affRng As Range
Dim affCell As Range
Dim startTime As Variant
Dim delayTime As Integer
Dim ws As Worksheet

Range("AffAnchor").Select
Range(Selection, Selection.End(xlDown)).Select

Set affRng = Selection
delayTime = Range("DelayTime")
Set ws = Sheets("Play")
ws.Activate
ws.OLEObjects("AffLabel").Object.Caption = ""

For Each affCell In affRng
startTime = Timer
ws.OLEObjects("AffLabel").Object.Caption = affCell
MsgBox ws.OLEObjects("AffLabel").Object.Caption ' Shows
correct text value
Do While Timer <= startTime + delayTime ' caption display
not updated.
Loop
Next

End Sub

OLE Object on Sheet updates Value in Code but not its Display
 

On Feb 14, 11:35 am, "Chip Pearson" wrote:
Try adding a DoEvents statement within your Do loop:

Do While Timer <= startTime + delayTime ' caption display
DoEvents
Loop

As a general principle, using a tight loop to wait an elapsed time isn't the
best way. You should probably use Application.Wait or the Win32
Sleep/SleepEx function.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

"SteveM" wrote in message

...

This is pretty simple, but puzzling. I placed a Label control on a
worksheet. I read in a range of text values that I want to display as
the Label caption after a brief delay. The delay time is read in
seconds. The For Each loop is supposed to update the caption.
However, the caption value does indeed update because I tested it by
putting it out in a MsgBox. But the caption graphic does not update.
Code below. Any ideas appreciated.

Thanks,

SteveM

Sub PlayAff()

Dim affRng As Range
Dim affCell As Range
Dim startTime As Variant
Dim delayTime As Integer
Dim ws As Worksheet

Range("AffAnchor").Select
Range(Selection, Selection.End(xlDown)).Select

Set affRng = Selection
delayTime = Range("DelayTime")
Set ws = Sheets("Play")
ws.Activate
ws.OLEObjects("AffLabel").Object.Caption = ""

For Each affCell In affRng
startTime = Timer
ws.OLEObjects("AffLabel").Object.Caption = affCell
MsgBox ws.OLEObjects("AffLabel").Object.Caption ' Shows
correct text value
Do While Timer <= startTime + delayTime ' caption display
not updated.
Loop
Next

End Sub

Chip,

Thanks for the response. I tried this three second delay:

Application.Wait (Now + TimeValue("0:00:03"))

But it did not work. I interrupted code execution as a test and the
display Label did update to the interrupt value.

I'm not a programmer, but I have been able to update Label controls on
Forms with ShowModal set to False as things happened in the
background. So I guess I could choose that cowardly way out. But I
sure would like to know what's happening here.

Thanks Again,

SteveM

OLE Object on Sheet updates Value in Code but not its Display
 

Did you notice that Chip add the DoEvents line in your loop?

Did you try that?

if that doesn't work, maybe turning screenupdating off, then back on would be
worth a try???

SteveM wrote:

On Feb 14, 11:35 am, "Chip Pearson" wrote:
Try adding a DoEvents statement within your Do loop:

Do While Timer <= startTime + delayTime ' caption display
DoEvents
Loop

As a general principle, using a tight loop to wait an elapsed time isn't the
best way. You should probably use Application.Wait or the Win32
Sleep/SleepEx function.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

"SteveM" wrote in message

...

This is pretty simple, but puzzling. I placed a Label control on a
worksheet. I read in a range of text values that I want to display as
the Label caption after a brief delay. The delay time is read in
seconds. The For Each loop is supposed to update the caption.
However, the caption value does indeed update because I tested it by
putting it out in a MsgBox. But the caption graphic does not update.
Code below. Any ideas appreciated.

Thanks,

SteveM

Sub PlayAff()

Dim affRng As Range
Dim affCell As Range
Dim startTime As Variant
Dim delayTime As Integer
Dim ws As Worksheet

Range("AffAnchor").Select
Range(Selection, Selection.End(xlDown)).Select

Set affRng = Selection
delayTime = Range("DelayTime")
Set ws = Sheets("Play")
ws.Activate
ws.OLEObjects("AffLabel").Object.Caption = ""

For Each affCell In affRng
startTime = Timer
ws.OLEObjects("AffLabel").Object.Caption = affCell
MsgBox ws.OLEObjects("AffLabel").Object.Caption ' Shows
correct text value
Do While Timer <= startTime + delayTime ' caption display
not updated.
Loop
Next

End Sub

Chip,

Thanks for the response. I tried this three second delay:

Application.Wait (Now + TimeValue("0:00:03"))

But it did not work. I interrupted code execution as a test and the
display Label did update to the interrupt value.

I'm not a programmer, but I have been able to update Label controls on
Forms with ShowModal set to False as things happened in the
background. So I guess I could choose that cowardly way out. But I
sure would like to know what's happening here.

Thanks Again,

SteveM

--

Dave Peterson