I currently have a sheet programed that when a button is pressed it erases a
sheet that is there and replaces it with pulled information.

I have programmed in a label to show up when the document is erased and
replaced with the code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label .1", Link:=False, _
DisplayAsIcon:=False, Left:=0.75, Top:=0.75, Width:=500.25, Height:=
_
17.25).Select

My question is how to I format the label? if I completely close it and use
the code:
Worksheets("Agent Summary").label1.caption ="test"

it will work only the once when the document first loads then after that if I
use the same button it reverts back to label1

I know its an embedded label so that could be where its getting difficult.
could really use some guidence on this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1

Have you thought about just adding the label manually.

Then you can just hide or show the label when you want. And you'll be able to
refer to the label as label1 whenever you want.

But if you're doing all this in code, you could add the label nicely:

Dim OLEObj As OLEObject

'remove any existing OLEObject with the same name
On Error Resume Next
Worksheets("agent summary").OLEObjects("mylabelnamehere").Delete
On Error GoTo 0

Set OLEObj = Worksheets("Agent Summary").OLEObjects.Add _
(ClassType:="Forms.Label.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=0.75, _
Top:=0.75, _
Width:=500.25, _
Height:=17.25)

OLEObj.Name = "myLabelNameHere"

'maybe add the caption right after you've added it.
OLEObj.Object.Caption = "test2"

or refer to it like this if you want to change something in a different
procedure.

Worksheets("Agent Summary").OLEObjects("mylabelnamehere").Object.Cap tion _
= "test"



"Darrell_Sarrasin via OfficeKB.com" wrote:

I currently have a sheet programed that when a button is pressed it erases a
sheet that is there and replaces it with pulled information.

I have programmed in a label to show up when the document is erased and
replaced with the code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label .1", Link:=False, _
DisplayAsIcon:=False, Left:=0.75, Top:=0.75, Width:=500.25, Height:=
_
17.25).Select

My question is how to I format the label? if I completely close it and use
the code:
Worksheets("Agent Summary").label1.caption ="test"

it will work only the once when the document first loads then after that if I
use the same button it reverts back to label1

I know its an embedded label so that could be where its getting difficult.
could really use some guidence on this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1

--

Dave Peterson