indirect adressing a TextBox on a sheet

[dixiematic]

I have i-textboxes on a worksheet. By some procedure I calculate a pointer p, 0< p <=i. Now I would like to change the text of the TextBox(p). How could I adress it?
m777182

 

[TonyJollans]

Perhaps ...

[i]sheet_ref[/i].TextBoxes(p).Text = "new text"

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[dixiematic]

to TonyJollans
thanks for your help but sheet_ref.TextBoxes(p) or sheet_ref.TextBoxes.item(p)does not work:RUN TIME ERROR 438.
M777182

 

[GlennUK]

And are you actually typing sheet_ref, or the sheet reference ... show your actual code.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[dixiematic]

TO GlennUK

this is a part of the code: I create an array (4 x j)of TextBoxes adjacent to the already created boxes (6) and then I would like to fill them with the values of an array of variables x(i). Neither of labels 100, 200 or 300 work.

Label_create_Textboxes:
MypositionLeft = 372
Mytop = 48
MyWidth = 60
MyGap = 2
Myleft = MypositionLeft - MyWidth
Worksheets("vesa").Activate
For i = 1 To 4
Mytop = 48 + (i - 1) * 19.9
For j = 1 To n
Myleft = MypositionLeft + MyWidth * (j - 1) + MyGap
Worksheets("vesa").OLEObjects.Add ClassType:="Forms.TextBox.1", _
Left:=Myleft, Top:=Mytop, Height:=19.5, Width:=60
Next j
Next i

Line_fill_in_data:
With Worksheets("vesa")
Rem : there are 6 TextBoxes previously created , therefore j=i+6
For i = 1 To n
j = i + 6
line100:
.TextBox.Item(j)=x(i) : rem ERROR
line200:
.TextBox. (j)=x(i) :rem ERROR
line300:
Worksheets("vesa").TextBox.Item(j)=x(i) :ERROR

Next i
End With



Regards
m777182

 

[SkipVought]

hi,

here's on way...

If .Shapes(j).OLEFormat.progID = "Forms.TextBox.1" Then
   .Shapes(j).OLEFormat.Object.Object.Text = x(i)

Check the object properties as you drill down. faq707-4594

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dixiematic]

To SkipVought

Thanks, the code as such does not work, perhaps I should trim it a bit...
I'll investigate chances with ObjPtr(MyObject) though I have not got the slightest idea how to go further. Will continue on Monday.
m777182

 

[SkipVought]

"the code as such does not work" does not work!!!

In order to help, YOU must help, by providing clear, concise and complete information that can enable anyone to ascertain what the problem might be.

So please explain what you mean by "the code as such does not work."

Do you mean that you got an error message? In that case, post the code in context, the statement in error and the error messege in its entirety.

Do you mean that the results were not as expected? If so, exactly what were the results and what did you expect and post the code in context?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dixiematic]

To SkipVougth

I am sorry, I was already by my granddaughter in my mind.
It was error 438 like in a short test code on a newly opened worksheet, where I inserted 6 TextBoxes there. I present the code here :
label_10 is passed OK, label_20 does not work. If I skip to label_30 it is again OK. So the code from label_40 on seems a solution to my problem so far but very frankly I do not get the idea why so.

Sub MyTest()
Dim MyTextbox As Object
k = 5
With Worksheets("Sheet1")
label_10:
.TextBox5.Text = "BLABLA": Rem this is OK
label_20:
.TextBox.Item(2).Value = 1: Rem error 438
.TextBox(k).Text = "blabla": Rem error 438
.TextBox.Item(3).Text = "blabla": Rem error 438
label_30:
Set MyTextbox = Worksheets("List1").TextBox1
MyTextbox = "TB" & Str(3)
MyTextbox.Text = "BLABLA": Rem OK
label_40:
MyTextbox = "TB" & k
MyTextbox.Text = "BLABLA": Rem OK

End With
End Sub

Thanks for your help. You made me thinking more intensively.
regards M777182

 

[dixiematic]

To SkipVought

I have to correct my statement: your solution works fine in a short demo as follows. The statement after Line_10 finds all TextBoxes and fills them with some value. The very same statement somewhere else in my program (with another worksheet) stops with error 438. Few days I was googling arround but still I do not know what to do. Could you give me another hint?

...
...
i = Worksheets("Sheet1").OLEObjects.Count
ReDim x(i)
p = 1
For j = 1 To i
x(j) = p ^ 2
line_10:
If Worksheets("Sheet1").Shapes(j).OLEFormat.progID _ = "Forms.TextBox.1" Then
Shapes(j).OLEFormat.Object.Object.Text = x(j)
p = p + 1
End If
Next j
...

regards
M777182

 

[SkipVought]

ALL OBJECTS must be referenced to it PARENT OBJECT...

If Worksheets("Sheet1").Shapes(j).OLEFormat.progID _ = "Forms.TextBox.1" Then
  [b]Worksheets("Sheet1").[/b]Shapes(j).OLEFormat.Object.Object.Text = x(j)
   p = p + 1
   End If

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dixiematic]

To SkipVought

Thank you that you keep answering me. I think I localised the problem but I do not know the solution. Let me repeat: On Sheet1 I generate i-TextBoxes (by Excel commands) and on Sheet2 j-TextBoxes the same way. Now from the Vba code under Sheet1 additional k-TBs are generated dynamically. Now I try to count them:

line_100:
i = Worksheets("Sheet1").OLEObjects.Count
p = 0
For j = 1 To i
If Worksheets("Sheet1").Shapes(j).OLEFormat.progID = "Forms.TextBox.1" Then
p = p + 1
End If
Next j
...
Both i and p are correct on Sheet1, but same code for Sheet2 fails with error 438 when counting TBs:

line_200:
ii = Worksheets("Sheet2").OLEObjects.Count: rem ii is correct
pp = 0
For jj = 1 To ii
If Worksheets("Sheet2").Shapes(jj).OLEFormat.progID _ = "Forms.TextBox.1" Then
pp = pp + 1
End If
Next jj

As far as I understand the problem arose because the parent object for j-TBs is Sheet2 and for additional k-TBs I am not sure. Is there a way around if I am right?
m777182

 

[SkipVought]

I can't spot a problem. Having said that, this is not the way that I would have coded this. The reason being that you're doing the same thing twice. What if you add a sheet? What if you had 20 sheets?

     Dim ws As Worksheet, sp As Shape, p() As Integer, i As Integer
    
     For Each ws In Worksheets
         ReDim Preserve p(i)
         For Each sp In ws.Shapes
             If sp.OLEFormat.progID = "Forms.TextBox.1" Then p(i) = p(i) + 1
         Next
         i = i + 1
    Next
    For i = 0 To UBound(p)
        Debug.Print Sheets(i + 1).Name, p(i)
    Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dixiematic]

To SkipVought

Thank you for your answer. I have done something similar, but in your code and in mine code the statement

If sp.OLEFormat.progID = "Forms.TextBox.1"

rises error 438. Additionaly, the statement (in my code)

i=ws("sheet2").TextBoxes.count

actually counts the number of TBs, that were created manualy on Sheet1 + those, that were created manualy on sheet2 but not those created dynamically and that is not correct.
This is rather too much for me and I will follow your advice to make it differently, these two sheets namely. This is a rather complex program on a mass and energy balance over the chemical process. Each sheet is an entry point for parametra of particular piece of equipment or properties of material flows through the process or displays the status of the proces. Wherever I enter the process I can change the parametra of equipment or energy and mass flows or material properties and it calculates the new operating point backwards and forward. I started it with material properties functions many years ago. Then I got the Excel and the program was growing like a tumor. I am aware I should switch to C or C+ but now I am four months to my retirement so there is not much enthusiasm left...
Thanks for your help.
M777182

 

[SkipVought]

1. There is no TextBoxes collection in the sheet object.

2. My code does run to completion. Use debug and the watch window to discover what is happening with your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dixiematic]

To SkipVought

I found a solution to how to address (p-k)TextBoxes generated by code: I have to address them by name

For i=k to p
MyTextBoxName="TextBox" & i
Worksheets("Mysheet").OLEObjects(MyTextBoxName).Object.Text="bla bla.."

Still I do not know why I cannot count my TextBoxes correctly- I get all other objects on a Sheet(even the number of comments- I did not know that comments are objects too) + the members of only the 1st row of an m*n array of TBs that I generate in a loop, but it really does not matter now because I do not want to count the TBs but rather write a text into them and that works fine.
regards
m777182

 

[SkipVought]

You're dealing with the Shapes collection -- LOTS of different shapes, even filters.

So you must find a property that ALL the shapes share and that can differentiate between the TYPE of shape.

I'm going to leave that up to you to discover, using faq707-4594. That way, you will also learn a bunch more really useful stuff about the object model and how to use it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]