For, Each, If, Loop?
 

This code runs thru but I am not getting the desired affect, Visible =
false for the condition.
I am checking a Cell for interior color, if the interior color is a
match then I want the coorosponding TextBox set "Visible = False"

Dim obj As OLEObject
Dim i as Long

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
With Worksheets("sheet2")
For i = 1 To 50
..OLEObjects("TextBox" & i).Visible = _
..Range("A" & 10 + 1).Interior.ColorIndex = 3
Next i
End With
End If
Next

p.s.
The initial code was from Bob Phillips, Thanks Bob ;)
But it would not complete.

Dim i As Long
With Worksheets("sheet2")
For i = 1 To 50
..OLEObjects("TextBox" & i).Visible = _
..Range("A" & 10 + 1).Interior.ColorIndex = 3
Next i
End With

So I attempted to fix it. :eek: :rolleyes:

Any help is appreciated.


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

I think this is what you want

Dim obj As OLEObject
Dim i As Long

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
i = i + 1
With Worksheets("sheet2")
.OLEObjects("TextBox" & i).Visible = _
.Range("A" & 10 + i).Interior.ColorIndex = 3
End With
End If
Next obj


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rick_Stanich"
wrote in message
news:Rick_Stanich.24066v_1141236615.8625@excelforu m-nospam.com...

This code runs thru but I am not getting the desired affect, Visible =
false for the condition.
I am checking a Cell for interior color, if the interior color is a
match then I want the coorosponding TextBox set "Visible = False"

Dim obj As OLEObject
Dim i as Long

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
With Worksheets("sheet2")
For i = 1 To 50
OLEObjects("TextBox" & i).Visible = _
Range("A" & 10 + 1).Interior.ColorIndex = 3
Next i
End With
End If
Next

p.s.
The initial code was from Bob Phillips, Thanks Bob ;)
But it would not complete.

Dim i As Long
With Worksheets("sheet2")
For i = 1 To 50
OLEObjects("TextBox" & i).Visible = _
Range("A" & 10 + 1).Interior.ColorIndex = 3
Next i
End With

So I attempted to fix it. :eek: :rolleyes:

Any help is appreciated.


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

I simply do not see why the macro doesn't recognize the interior color?
Is there more than one method of coloring th einterior of a cell?


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

Yes there is, it might be set by conditional formatting, in which case that
is not the interior property.

Go to one of those cells, menu FormatConditional Formatting, and see if it
has conditions.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rick_Stanich"
wrote in message
news:Rick_Stanich.240b1n_1141242902.4486@excelforu m-nospam.com...

I simply do not see why the macro doesn't recognize the interior color?
Is there more than one method of coloring th einterior of a cell?


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

There is no Conditional Formatting?

Ignore this question if I am way off track.
Is the following code looking for a textbox on the worksheet? (its ho
I read it).

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox The

--
Rick_Stanic

I am m
-----------------------------------------------------------------------
Rick_Stanich's Profile: http://www.excelforum.com/member.php...fo&userid=2895
View this thread: http://www.excelforum.com/showthread.php?threadid=51792

For, Each, If, Loop?
 

effectively, yes.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rick_Stanich"
wrote in message
news:Rick_Stanich.241vj4_1141316107.0136@excelforu m-nospam.com...

There is no Conditional Formatting?

Ignore this question if I am way off track.
Is the following code looking for a textbox on the worksheet? (its how
I read it).

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

Oh a wise guy eh! :) (Thank you for all your help)

Is Mr. Ogilvy or Mr. Pearson available for input?
(Nothing personal Bob, these two are my Excel Idols, haha; I'm a
groupie :eek: In a manly way. ;) )


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

Ok I'll add you name to the list of posts to bin without reading.

"Rick_Stanich"
wrote in message
news:Rick_Stanich.243wwm_1141411204.2854@excelforu m-nospam.com...

Oh a wise guy eh! :) (Thank you for all your help)

Is Mr. Ogilvy or Mr. Pearson available for input?
(Nothing personal Bob, these two are my Excel Idols, haha; I'm a
groupie :eek: In a manly way. ;) )


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920

For, Each, If, Loop?
 

You didn't take my pun seriously?


--
Rick_Stanich

I am me
------------------------------------------------------------------------
Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
View this thread: http://www.excelforum.com/showthread...hreadid=517920