Worksheet calculate event

**sherry_1991** · 02-19-2013, 02:15 PM

Worksheet calculate event doesn't let me change values on another sheet "Queries". When I do gives me error 438, object doesn’t support this property or method.

Private Sub Worksheet_Calculate()
 
 i = 0
    numb = 18
    For i = 80 To 103
    
    
    Worksheets("Sheet1").Shapes.Range(Array("TextBox " & i)).Select
    With Selection.ShapeRange.Fill                ''''''''''''''''HIGHLIGHTS THIS LINE
    .Visible = msoTrue
              If Worksheets("Queries").Range(Worksheets("Queries").Cells(2, numb), Worksheets("Queries") _
              .Cells(2, numb)) = 1 Then
             
             .ForeColor.RGB = RGB(0, 255, 0)
              Else
             .ForeColor.RGB = RGB(205, 205, 193)
              End If
    .Solid
    End With
    numb = numb + 1
    Next i
    
End Sub

**Norie** · 02-19-2013, 02:26 PM

I don't think it's the calculate event preventing you doing it.

What you need to do is reference the shapes on the other sheet and not use Selection.

Private Sub Worksheet_Calculate()
Dim I As Long
Dim numb As Long

    I = 0
    numb = 18
    For I = 80 To 103

        With Worksheets("Sheet1").Shapes("TextBox " & I).OLEFormat.Object.ShapeRange.Fill                ''''''''''''''''HIGHLIGHTS THIS LINE
            .Visible = msoTrue
            If Worksheets("Queries").Cells(2, numb) = 1 Then

                .ForeColor.RGB = RGB(0, 255, 0)
            Else
                .ForeColor.RGB = RGB(205, 205, 193)
            End If
            .Solid
        End With
        numb = numb + 1
    Next I

End Sub

**sherry_1991** · 02-19-2013, 02:35 PM

Oh yea I was trying to do it but lack of knowledge prevented me. BUT YES THIS WORKS!!!!!!! AW YEAH! Thank you so much Norie. I really hope i don't encounter a problem now.