Fill worksheet textbox from vba

[Martin1962]

Hi,

I have a worksheets called "Sheet1" with multiple embedded textboxes.
in a second worksheet ("Sheet2" I keep my data)

In my code I create 'Public objSheetIn, objShootOut as worksheet'
In the section "Workbook_open" I have this :

DIm shp as Shape

Set objSheetIn = Worksheets("Sheet1")
Set objSheetOut = Worksheets("Sheet2")

I check if the worksheet contains Shapes (If objSheetIn.Shapes.Count > 0 then ...)

Then I run through all shapes :

For Each shp In objSheetIn.Shapes
If Left(shp.Name, 4) = "Txt_" Then

the next instruction goes wrong (assign some text)

objSheetIn.Shapes(shp.Name).TextFrame.Characters.Text = objSheetOut.Cells(1,1)

This retuns error 438 : Object doesn't support this property or method.

Can someone please indicate what's going wrong ?

Kind regards,

Martin

 

[Fluff]

Are the textboxes ActiveX or Form controls?

 

[Martin1962]

Hi Fluff,

They are just Form controls.
When I go into Design Mode, they all are referenced as "=EMBED("Forms.TextBox.1","")", with each a name that starts with "Txt_"

Regards,

Martin,

 

[Fluff]

In that case they are ActiveX controls, not form controls.
Try

   Dim Obj As Variant
   For Each Obj In objSheetIn.OLEObjects
      If Obj.Name Like "Txt_*" Then
         Obj.Object.Text = objSheetOut.Cells(1, 2)
      End If
   Next Obj

 

[Martin1962]

Works like a charm !
Thanks.

Martin

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Martin1962]

I hope I'm allowed to add this question :

Onces all 40 activeX Textboxes are 'loaded', how do I position the cursor in Textbox "Txt_date" , as if I click on it ?

Regards,

Martin.

 

[Fluff]

How about

objSheetIn.Txt_Date.Activate

Obviously the sheet will need to be the active sheet

 

[Martin1962]

Hi Fluff,

That was my initial idea :

objSheetIn.Activate
objSheetIn.Txt_date.Activate

But i'm getting a compiler error on the last line:
Method or data member not found

Even replacing Txt_date by the name of another (exisiting) textbox, like Txt_name is giving the same result/error.

Regards,

Martin

 

[Fluff]

ok, try it like

   objSheetIn.OLEObjects("Txt_date").Activate