reteriving text form a text box in vb

[Dose87]

Im trying to write a progam that takes text out of a text box named data5 that is in a excel document that which I have defined as workbooks(fileName).worksheet(1).I want to take that string and save is a lastTextbox and put it in a another excel document that is called Clients1 at cell(i,3). no matter what I try I cant get the vb progam to see and read that data in textbox data5.


btw everything is in different workbooks

I have tried everything I can think of weather it is the .range method or
focus(). but there is always a problem getting the actual text form the text box in a different document. can someone plz help me

Do While fileName <> ""
i = i + 1
j = 2
jj = j + 1
Cells(i, 1) = fileName
Cells(i, 2) = lastTextbox
Application.ScreenUpdating = False
Workbooks.Open (directory & fileName)
' Workbooks(directory & fileName).Worksheets(1).data5.Focus()
firstChr = data5.selectionStart = 0
lastTextbox = data5.SelectionLength = data5.Text.Length

Workbooks.Open (directory & "clients1.xlsx")
For Each sheet In Workbooks(fileName).Worksheets

 

[SkipVought]

sorry, the textbox is on each sheet, right?

        With Workbooks.Open(directory & Filename)
            
            For Each Sh In .Worksheets
                ThisWorkbook.Sheets(1).Cells(i, j).Value = Sh.Name
                ThisWorkbook.Sheets(1).Cells(i, 1).Value = Filename
                ThisWorkbook.Sheets(1).Cells(i, 2).Value = [highlight #FCE94F]Sh.[/highlight]data5.Text
                j = j + 1
                jj = jj + 1
            Next Sheet
            .Close      'close without saving
        End With

 

[Dose87]

same problemish.... it says method or data member not found and if focuses on data5

 

[SkipVought]

So if you Debug at that point, can you see if you have a Textbox named data4 ON THAT SHEET?

 

[Dose87]

Debug.Print Data5.Text
Debug.Print sheet.Data5.Text
I cant get these to work... method or data member not found

 

[Dose87]

of I get object required if I take out the sheet.

 

[SkipVought]

Do you know which sheet the TextBox is on?

Debug.Print Sh.Name

If you actually (manually) select that sheet, 1) is there a Textbox and 2) is the Textbox.name data5?

 

[SkipVought]

That is SELECT the Textbox and look at the PROPERTIES a window for that Textbox for the name.

 

[Dose87]

the text box name is data5
if I click define name is gives me a refres to =Invoice!$E$12

 

[SkipVought]

Is this an ActiveX control or a Forms control?

 

[Dose87]

I dont know....how do i tell....sorry this is so complicated ...only worked with vb a little bit

 

[Dose87]

I went into about ... forms3: 14.0.7140.500 version 1628

 

[SkipVought]

I'm having trouble with my Forms controls, so this will be from memory.

First let me direct you to this FAQ. faq707-4594

I have used this technique to DISCOVER all kinds of things regarding variables and OBJECTS in my code. This can help you discover what's going on with data5.

I believe that this is the code for assigning a tect value from this Forms textbox according to my recollection.

MsgBox Sh.data5.OLEFormat.Object.Value

If that fails, then run your code, perhaps using the Step feature, or BREAK at the point of assigning the textbox. Use the Watch Window to discover the Object path to the value.

Let me know what happens.