Accessing Controls in Excel sheet

[_Biff]

Hi All;

I have a little dilemna where i am attempting to alter a control in a worksheet from a Visual Basic 6 program (report generator). I have a chunk of code from a Macro in VB as follows:

.Application.CommandBars("Visual Basic").Visible = False
.ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=227.25, Top:=66.75, Width:=473.25, Height _
:=66.75).Select
.Range("A4:G11").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With


This creates a textbox on an excel spreadsheet... how do i add text from VB6 to this textbox on an excel spreadsheet? Basically i dont know how to reference a control from VB6 on an excel spreadsheet....

any help would be appreciated.. and thanks in advance.

j

[dglienna]

I'll do you one better... I recorded a Macro to see how it was done!

    ActiveSheet.Shapes("Text Box 1").Select
    Selection.Characters.Text = "this is a test" & Chr(10) & ""
    With Selection.Characters(Start:=1, Length:=15).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With

[_Biff]

Thanks for the response...!!

i added your suggestion and put it into my code but i get:

Runtime error : "438"

Object doesn't support this property or method


heres the updated code.. any ideas?:


.Application.CommandBars("Visual Basic").Visible = False
.ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=227.25, Top:=66.75, Width:=473.25, Height _
:=66.75).Select
.Selection.Characters.Text = "this is a test" & Chr(10) & ""

[dglienna]

try adding the with characters section. it may be necessary.
RobDog888 would know. You might want to post in the VBA section. He's the moderator