Loop Thru Textboxes - Help....

[Velvetphog]

Pulling my hair out....

I have 8 sheets in a project and each has its own textbox named htmlbox1, htmlbox2, etc.

Want code to check which sheet is active and then send output to the appropriate textbox.

Tried this:

If ActiveSheet.codename = "Sheet1" Then
i = 1
End If
If ActiveSheet.codename = "Sheet2" Then
i = 2
End If
etc........then send the resultant output to the textbox associated with that active sheet.....

ActiveSheet.("htmlbox" & i).Text = "whatever"
I'm new and I've tried everything I know.

Thanks
Pete

[Bob Phillips]

Dim i As Long

With ActiveSheet

Select Case .Name

Case "Sheet1": i = 1
Case "Sheet2": i = 2
Case "Sheet3": i = 3
'etc
End Select

.OLEObjects("htmlbox" & i).Object.Text = "whatever"
End With

[Velvetphog]

Tried OLEObjects before - used your idea as well and keep getting:

Run Time Error '1004':
Unable to get the OLEObjects property of the Worksheet class.

[Bob Phillips]

How did you create these textboxes?

[Velvetphog]

Embarrassingly enough - I'm not sure. It was a code snippet I found that I have tried to massage for my own application.

I believe

=EMBED("Forms.TextBox.1","")

might help?

[Kenneth Hobs]

There are Form controls and ActiveX controls. Looks like an ActiveX control. In the Developer tab, select the Design view and make sure that your textbox name is of that naming convention and exists.

xld's code worked for me.

Sub Test()
ActiveSheet.OLEObjects("htmlbox" & NumberPart(ActiveSheet.Name)).Object.Text = "whatever"
End Sub

Function NumberPart(aString As String) As Long
Dim s As String, i As Integer, mc As String
For i = 1 To Len(aString)
mc = Mid(aString, i, 1)
If Asc(mc) >= 48 And Asc(mc) <= 57 Then s = s & mc
Next i
NumberPart = CLng(s)
End Function

[Velvetphog]

Textboxes are named htmlbox1, htmlbox2, etc.

[redhunter]

try :

ActiveSheet.OLEObjects("htmlbox" & ActiveSheet.Index).Object.Value = "Whatever"

[Velvetphog]

redhunter:

Well that worked but I needed to have 6 lines of text as output so I had to do this:

ActiveSheet.OLEObjects("htmlbox" & ActiveSheet.Index).Object.Text = ActiveSheet.OLEObjects("htmlbox" & ActiveSheet.Index).Object.Text + vbCrLf + "whatever"
And do it 6 times, each with different info. But it works fine now.

Thanks!

[Kenneth Hobs]

Use & rather than + as a concatenation operator.

If you told us what you wanted, we could probably show you a loop or another method to build a concatenated string. Now that you have 5 posts, you can attach a workbook. Simple example workbooks help us help you more readily.