Hi there

I have a workbook with 2 sheets. in the first sheet (sheet1) i have embeded
a textbox. Also in the first sheet there is a cell which shows a result of a
formula (the result can be 1, 2 or 3).

In the second sheet (sheet2) there is a shape (rectangle 1).

Now the shape should change its color while typing in the textbox in
condition to the result of the formula mentioned above.

If the shape is in the same sheet as the textbox, there is no problem to
realize my goal. As soon as the shape is in a different sheet, the following
code does not work. Can anybody help me please?

Thanks
Michael

Private Sub TextBox1_Change()

If Sheets("sheet1").Range("A1") = "1" Then

Sheets("sheet2").Shapes("rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 50 'green

End If

If Sheets("sheet1").Range("A1") = "2" Then

Sheets("sheet2").Shapes("rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 'red

End If

If Sheets("sheet1").Range("A1") = "3" Then

Sheets("sheet2").Shapes("rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13 'yellow

End If
End Sub

try this. modify your sheet and range to suit
Private Sub TextBox1_Change()
Select Case Sheets("sheet4").Range("g1")
Case 1: mc = 50
Case 2: mc = 10
Case 3: mc = 13
Case Else
mc =10
End Select
Sheets("sheet6").Shapes("rectangle 1") _
..Fill.ForeColor.SchemeColor = mc
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Michael S." <(E-Mail Removed)> wrote in message
news:88905D61-416B-45D5-8CDF-(E-Mail Removed)...
> Hi there
>
> I have a workbook with 2 sheets. in the first sheet (sheet1) i have
> embeded
> a textbox. Also in the first sheet there is a cell which shows a result of
> a
> formula (the result can be 1, 2 or 3).
>
> In the second sheet (sheet2) there is a shape (rectangle 1).
>
> Now the shape should change its color while typing in the textbox in
> condition to the result of the formula mentioned above.
>
> If the shape is in the same sheet as the textbox, there is no problem to
> realize my goal. As soon as the shape is in a different sheet, the
> following
> code does not work. Can anybody help me please?
>
> Thanks
> Michael
>
> Private Sub TextBox1_Change()
>
> If Sheets("sheet1").Range("A1") = "1" Then
>
> Sheets("sheet2").Shapes("rectangle 1").Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 50 'green
>
> End If
>
> If Sheets("sheet1").Range("A1") = "2" Then
>
> Sheets("sheet2").Shapes("rectangle 1").Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 'red
>
> End If
>
> If Sheets("sheet1").Range("A1") = "3" Then
>
> Sheets("sheet2").Shapes("rectangle 1").Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13 'yellow
>
> End If
> End Sub
>