C#: Adding OLEObject--how to set object properties?

  • Thread starter Jeff Donkersgoed
  • Start date
J

Jeff Donkersgoed

I used the following line to add a label (or textbox, etc.) to my worksheet,
and I can see it in Excel.

Excel.OLEObject ole =
((Excel.OLEObjects)wsTemplate.OLEObjects(Missing.Value)).Add("Forms.Label.1",
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, 15, 15, 100, 20);

The problem is that it just says "Label1" for the text. How can I go about
changing it and the font as well? I can't figure out how to cast the
ole.Object to anything which is what I think I need to do.

I've see lots of VBA code that just uses "With" as below, but can I do
something similar in C#?

Dim Lbl As OLEObject
Set Lbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1",
Link:=False, _
DisplayAsIcon:=False, Left:=232.5, Top:=241.5, Width:=72, Height:=18)

With Lbl
.Name = "ttt"
.Object.Caption = "p.sub new control"
.Object.Font.Bold = True
.Object.BackColor = RGB(255, 0, 0)
End With

Any help would be appreciated.
 
J

Jeff Donkersgoed

Hi Tim,

There is a C# "using" keyword, but I'm not sure it works that way. Even if
it did, I don't think it would use the proper type because OLEObject doesn't
have the properties I need to use, where VB probably treats everything as a
var. I did finally figure it out last night though what the proper cast is.
If I used "Forms.Textbox.1" as my class, I needed to cast as below. BTW,
MSForm is part of the "Microsoft Forms 2.0 Object Library" for those who have
trouble finding it. The only remaining issue I'm having a problem with is
that a textbox added this way seems to ignore the BackStyle property. It
always remains opaque even when toggling it manually through Excel--I'm still
trying to figure that one out. (I'm using Office 2007 and VS2008).

using MSForm = Microsoft.Vbe.Interop.Forms;
Excel.OLEObject ole =
((Excel.OLEObjects)wsTemplate.OLEObjects(Missing.Value)).Add("Forms.Textbox.1",
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, 15, 15, 100, 20);
MSForm.TextBox tb = (MSForm.TextBox)(ole.Object);
tb.Text = "High Risk";
tb.Font.Name = "Arial";
tb.Font.Size = 16;
tb.SpecialEffect =
Microsoft.Vbe.Interop.Forms.fmSpecialEffect.fmSpecialEffectFlat;
tb.BackStyle = Microsoft.Vbe.Interop.Forms.fmBackStyle.fmBackStyleTransparent;
ole.PrintObject = true;
 
J

Jeff Donkersgoed

Ok, this is really starting to drive me crazy. My objective is simply to add
either a textbox or label to a chart through code, and have it be able to
print in both Office 2007 and Office 2003. Ideally, the code below will
work, but for some reason the Print Object property doesn't get set, so it
won't print in 2003 without physically changing it (which isn't an option).
It does print in 2007.

Excel.Shape label =
chartobj.Chart.Shapes.AddLabel(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 15, 15, 100, 20);
label.Select(Type.Missing);
label.TextFrame.Characters(Missing.Value, Missing.Value).Text = "High Risk";
label.TextFrame.AutoSize = true;
label.ControlFormat.PrintObject = true;

For the test code below (using the Forms.Textbox.1 object), I've found out
that if I go into Design mode, right-click the added textbox and go to Format
Object, the Transparency level is set to 0%. When it's set to 100%, I get
the desired effect. Can anyone tell me how to access that property? Is
there a different cast needed?
 
Top