TextBox
 

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[i] = "Not Applicable"
Else
Sheet3.TextBox[j].Value = Sheet2.TextBox[i]
End If
Next i

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

Many many THANKS ! ! !

Cheers

René

TextBox
 

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)

"René Gagnon" wrote in message
...
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[i] = "Not Applicable"
Else
Sheet3.TextBox[j].Value = Sheet2.TextBox[i]
End If
Next i

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

Many many THANKS ! ! !

Cheers

René

TextBox
 

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 wrote:[i]

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[i] = "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é

--

Dave Peterson