Excel controls from VB6 program
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
Re: Excel controls from VB6 program
You can acces any control on any sheet/workbook by accessing its Shapes collection.
VB Code:
ActiveBook.ActiveSheet.Shapes("Text1") 'etc.
Re: Excel controls from VB6 program
Thanks RobDog... someone from another forum told me that you would know the answer to this...
Re: Excel controls from VB6 program
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 ?
Re: Excel controls from VB6 program
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:
VB Code:
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.
Re: Excel controls from VB6 program
I get this error again for some reason.. hmmm
"438" error
Object doesn't support this property or method
Re: Excel controls from VB6 program
Try this then.
VB Code:
ActiveSheet.OLEObjects("TextBox1").Object.Value = "Test"