Excel controls from VB6 program

[_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 the control on the excel from VB...

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

j

[RobDog888]

You can acces any control on any sheet/workbook by accessing its Shapes collection.

ActiveBook.ActiveSheet.Shapes("Text1")  'etc.

[_Biff]

Thanks RobDog... someone from another forum told me that you would know the answer to this...

[_Biff]

I have just tried this and i get a "438" error

Object doesn't support this property or method

Heres the full code from VB:

Dim xlsApp As Excel.Application
Set xlsApp = New Excel.Application
xlsApp.Workbooks.Add

....
.....
xlsApp.ActiveBook.ActiveSheet.Shapes("Text1").Text = "HI there"


Any suggestions ?

[_joepsy_]

Hmm.

You can try accessing the textbox directly:
Sheet1.TextBox1.Text = "HI there"

But that will not help you if you are creating textboxes dynamically.

Did you try this:

Dim textbox1 as Object
Set textbox1 = .ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=227.25, Top:=66.75, Width:=473.25, Height _
:=66.75)
 
textbox1.Text = "HI there"

If this works, you can simply keep a reference to the textbox in memory.

[_Biff]

I get this error again for some reason.. hmmm


"438" error

Object doesn't support this property or method

[RobDog888]

Try this then.

ActiveSheet.OLEObjects("TextBox1").Object.Value = "Test"