PDA

View Full Version : Loop Thru Textboxes - Help....



Velvetphog
01-16-2012, 04:48 PM
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
01-17-2012, 01:18 AM
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
01-17-2012, 08:35 AM
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
01-17-2012, 08:42 AM
How did you create these textboxes?

Velvetphog
01-17-2012, 09:02 AM
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
01-17-2012, 09:15 AM
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
01-17-2012, 09:26 AM
Textboxes are named htmlbox1, htmlbox2, etc.

redhunter
01-17-2012, 01:01 PM
try :

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

Velvetphog
01-17-2012, 01:57 PM
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
01-17-2012, 02:15 PM
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.