I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " & Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption, "Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original value.

How can I make it display the new value even when the label is not selected?

TIA
- Turtle

Sounds like you have something (possibly just a space " ") in the sell to the
right of the label. If so, delete it and the full caption should show.
HTH,
Gary Brown


"Turtle MacDermott" wrote:

I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " & Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption, "Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original value.

How can I make it display the new value even when the label is not selected?

TIA
- Turtle

Thanks a lot for the tip, Gary!

Unfortunately, that doesn't seem to have been the problem.
I've been playing around a bit, and have found the same thing with both
labels and textboxes.
If I change a label's caption, or a textbox's text or value in code, the
spreadsheet shows the old value until I click on the control. Then it shows
the new value.
I continue to see the new value as long as the focus stays on a control.
As soon as I set it back to a spreadsheet cell, the old value returns.
It's as if there's another property that has to be set.

Any ideas?
TIA!
- Turtle

"Gary Brown" wrote in message
...
Sounds like you have something (possibly just a space " ") in the sell to
the
right of the label. If so, delete it and the full caption should show.
HTH,
Gary Brown


"Turtle MacDermott" wrote:

I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " & Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption,
"Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original value.

How can I make it display the new value even when the label is not
selected?

TIA
- Turtle

No I don't. Sorry,
Gary Brown

"MacDermott" wrote:

Thanks a lot for the tip, Gary!

Unfortunately, that doesn't seem to have been the problem.
I've been playing around a bit, and have found the same thing with both
labels and textboxes.
If I change a label's caption, or a textbox's text or value in code, the
spreadsheet shows the old value until I click on the control. Then it shows
the new value.
I continue to see the new value as long as the focus stays on a control.
As soon as I set it back to a spreadsheet cell, the old value returns.
It's as if there's another property that has to be set.

Any ideas?
TIA!
- Turtle

"Gary Brown" wrote in message
...
Sounds like you have something (possibly just a space " ") in the sell to
the
right of the label. If so, delete it and the full caption should show.
HTH,
Gary Brown


"Turtle MacDermott" wrote:

I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " & Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption,
"Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original value.

How can I make it display the new value even when the label is not
selected?

TIA
- Turtle

I couldn't reproduce your description either.

But if you minimize the worksheet (or even the application), then resize, do you
still see the problem.

When I've added controlbuttons (also from the control toolbox toolbar), I
sometimes can't see them until I do this--I think it's a screen refreshing thing
(technical term!).

Maybe adding a DoEvents to your code would help (maybe not).

And I think I'd try the label from the Forms toolbar to see if that behaved
better.

=====
But I don't have a guess about what you describe in your follow up.

MacDermott wrote:

Thanks a lot for the tip, Gary!

Unfortunately, that doesn't seem to have been the problem.
I've been playing around a bit, and have found the same thing with both
labels and textboxes.
If I change a label's caption, or a textbox's text or value in code, the
spreadsheet shows the old value until I click on the control. Then it shows
the new value.
I continue to see the new value as long as the focus stays on a control.
As soon as I set it back to a spreadsheet cell, the old value returns.
It's as if there's another property that has to be set.

Any ideas?
TIA!
- Turtle

"Gary Brown" wrote in message
...
Sounds like you have something (possibly just a space " ") in the sell to
the
right of the label. If so, delete it and the full caption should show.
HTH,
Gary Brown


"Turtle MacDermott" wrote:

I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " & Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption,
"Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original value.

How can I make it display the new value even when the label is not
selected?

TIA
- Turtle




--

Dave Peterson

Thanks for your ideas!

I've tried the label from the Forms toolbar, and to my joy found that it
displayed a new text very well.
I'm a bit perplexed that I can't find anywhere to set properties like font
and textalign, but that's not a showstopper.
(not being able to reliably change the text was.)

Of course the situation isn't quite as simple as I reported.
Here are a couple of additional aspects which I had omitted, presuming they
weren't relevant.
1. I'm using Office XP for this work.
2. The label in question is on a template; I'm opening a new spreadsheet
based on that template.
3. The code is actually in Access; I'm using Automation to run code on an
Excel spreadsheet I open.

I can move forward with what I have now, but if anyone has any insights on
setting properties on a Forms label (on a spreadsheet - it's easy if it's on
a form), or thinks any of the above 3 points might have some relevance, I'd
be delighted to hear more.

- Turtle

"Dave Peterson" wrote in message
...
I couldn't reproduce your description either.

But if you minimize the worksheet (or even the application), then resize,
do you
still see the problem.

When I've added controlbuttons (also from the control toolbox toolbar), I
sometimes can't see them until I do this--I think it's a screen refreshing
thing
(technical term!).

Maybe adding a DoEvents to your code would help (maybe not).

And I think I'd try the label from the Forms toolbar to see if that
behaved
better.

=====
But I don't have a guess about what you describe in your follow up.

MacDermott wrote:

Thanks a lot for the tip, Gary!

Unfortunately, that doesn't seem to have been the problem.
I've been playing around a bit, and have found the same thing with both
labels and textboxes.
If I change a label's caption, or a textbox's text or value in code, the
spreadsheet shows the old value until I click on the control. Then it
shows
the new value.
I continue to see the new value as long as the focus stays on a control.
As soon as I set it back to a spreadsheet cell, the old value returns.
It's as if there's another property that has to be set.

Any ideas?
TIA!
- Turtle

"Gary Brown" wrote in message
...
Sounds like you have something (possibly just a space " ") in the sell
to
the
right of the label. If so, delete it and the full caption should
show.
HTH,
Gary Brown


"Turtle MacDermott" wrote:

I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " &
Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption,
"Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original
value.

How can I make it display the new value even when the label is not
selected?

TIA
- Turtle




--

Dave Peterson

Many (most???) properties that are available with the Control toolbox toolbar's
label aren't available in the Form toolbar's Label.


MacDermott wrote:

Thanks for your ideas!

I've tried the label from the Forms toolbar, and to my joy found that it
displayed a new text very well.
I'm a bit perplexed that I can't find anywhere to set properties like font
and textalign, but that's not a showstopper.
(not being able to reliably change the text was.)

Of course the situation isn't quite as simple as I reported.
Here are a couple of additional aspects which I had omitted, presuming they
weren't relevant.
1. I'm using Office XP for this work.
2. The label in question is on a template; I'm opening a new spreadsheet
based on that template.
3. The code is actually in Access; I'm using Automation to run code on an
Excel spreadsheet I open.

I can move forward with what I have now, but if anyone has any insights on
setting properties on a Forms label (on a spreadsheet - it's easy if it's on
a form), or thinks any of the above 3 points might have some relevance, I'd
be delighted to hear more.

- Turtle

"Dave Peterson" wrote in message
...
I couldn't reproduce your description either.

But if you minimize the worksheet (or even the application), then resize,
do you
still see the problem.

When I've added controlbuttons (also from the control toolbox toolbar), I
sometimes can't see them until I do this--I think it's a screen refreshing
thing
(technical term!).

Maybe adding a DoEvents to your code would help (maybe not).

And I think I'd try the label from the Forms toolbar to see if that
behaved
better.

=====
But I don't have a guess about what you describe in your follow up.

MacDermott wrote:

Thanks a lot for the tip, Gary!

Unfortunately, that doesn't seem to have been the problem.
I've been playing around a bit, and have found the same thing with both
labels and textboxes.
If I change a label's caption, or a textbox's text or value in code, the
spreadsheet shows the old value until I click on the control. Then it
shows
the new value.
I continue to see the new value as long as the focus stays on a control.
As soon as I set it back to a spreadsheet cell, the old value returns.
It's as if there's another property that has to be set.

Any ideas?
TIA!
- Turtle

"Gary Brown" wrote in message
...
Sounds like you have something (possibly just a space " ") in the sell
to
the
right of the label. If so, delete it and the full caption should
show.
HTH,
Gary Brown


"Turtle MacDermott" wrote:

I have a spreadsheet which I am filling from Access using VBA.
I put a label on it, using the Controls Toolbox.
The label's caption reads "Created:"

In VBA, I set
ActiveSheet.OLEObjects("MyLabel").object.caption=" Created: " &
Now()

When I step through the code, I see the caption being set correctly.

Now, when I look at the spreadsheet, I see the original caption,
"Created:"
without the date.
But if I click on the label, it shows the entire value.
Click off it to something else, and it returns to the original
value.

How can I make it display the new value even when the label is not
selected?

TIA
- Turtle




--

Dave Peterson

--

Dave Peterson