TextBox

R

René Gagnon

Hi

I'm trying to loop through a series of "TextBox", where I
take the content of the box if there is something there
into another box on another sheet.

Basicly this is what I'm trying to do:

j = 1

For i = 1 To 5
If Sheet2.TextBox = "Not Applicable"
Else
Sheet3.TextBox[j].Value = Sheet2.TextBox
End If
Next i

Can someone show me how I can do this with MS Excel VBA

Many many THANKS ! ! !

Cheers

René
 
B

Bob Phillips

Something like

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "TextBox" Then
If ActiveSheet.OLEObjects(i).Object.Value = "Not Applicable"
Then
ActiveSheet.OLEObjects(j).Value =
ActiveSheet.OLEObjects(i).Value

End If
End If
Next i


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi

I'm trying to loop through a series of "TextBox", where I
take the content of the box if there is something there
into another box on another sheet.

Basicly this is what I'm trying to do:

j = 1

For i = 1 To 5
If Sheet2.TextBox = "Not Applicable"
Else
Sheet3.TextBox[j].Value = Sheet2.TextBox
End If
Next i

Can someone show me how I can do this with MS Excel VBA

Many many THANKS ! ! !

Cheers

René
 
D

Dave Peterson

Another way if you kept the names of the textboxes nice:

Option Explicit
Sub testme()

Dim i As Long
Dim j As Long

j = 1
For i = 1 To 5
If LCase(Sheet2.OLEObjects("textbox" & i).Object.Value) = "not
applicable" Then
'do nothing
Else
Sheet3.OLEObjects("textbox" & j).Object.Value _
= Sheet2.OLEObjects("textbox" & i).Object.Value
End If
Next i

End Sub

But what's going on with your code. You never vary j. So sheet3's textbox1
gets updated up to 5 times.



René Gagnon said:
Hi

I'm trying to loop through a series of "TextBox", where I
take the content of the box if there is something there
into another box on another sheet.

Basicly this is what I'm trying to do:

j = 1

For i = 1 To 5
If Sheet2.TextBox = "Not Applicable"
Else
Sheet3.TextBox[j].Value = Sheet2.TextBox
End If
Next i

Can someone show me how I can do this with MS Excel VBA

Many many THANKS ! ! !

Cheers

René
Click to expand...
 
Top