Trouble with cell value

[Karen53]

Hi,

I'm not sure how to get this to recognize the change in my cell value.
Cells F10 and T10 both have formulas in them which change based on another
sheet. They will show either "" or a text string which could be anything.
My worksheet calculate is not picking up the change, I think because the
formula is there, therefore it is not blank? If the cell shows nothing then
there should be no grid. If there is a value then show the grid. How would
I do this with the formula there?

Private Sub Worksheet_Calculate()

On Error GoTo ws_exit
Application.EnableEvents = False

Dim Grid As String
Dim LabelRng As Range
Dim LCol As String
Dim StartRow As Long
Dim EndRow As Long

If Me.Range("F10").Value < > "" Then

'set the values
Grid = ("F11:N13")
LCol = "E"
StartRow = 11
EndRow = 13

Call Me.Borders2(Grid)
Call Me.PoolSideLabels(StartRow, EndRow, LCol)

ElseIf Me.Range("F10").Value = "" Then

Me.Range(Grid).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End If

ws_exit:
Application.EnableEvents = True

End Sub

Thanks for your help.

 

[JLGWhiz]

I get myself confused with this, but you assign the value to Grid if F10 is
not equal to null, the try to use that value later under ElseIf as
Range(Grid). That won't work because if F10 is not equal to null (is true)
then the ElseIf will not fire. Therefore your Grid is never defined with
borders. See?

 

[JLGWhiz]

Also, if F10 does equal null then it goes directly to ElseIf and it will not
know what Grid is.

 

[Karen53]

Thank you JLGWhiz!

I have the first half working now, but not removing the grid. Is it not
recognizing the cell as null? I've adjusted the code to:


If Me.Range("F10").Value <> "" Then

'set the values
Grid = ("F11:N13")
LCol = "E"
StartRow = 11
EndRow = 13

Call Me.Borders2(Grid)
Call Me.PoolSideLabels(StartRow, EndRow, LCol)
End If

If Me.Range("F10").Value = "" Then

Grid = ("F11:N13")

Me.Range(Grid).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End If


Thanks for your help.

 

[JLGWhiz]

Karen, I have no way to check this since it relates to the formulas you have
in those cells, so all I can do is suggest that you step through your code
when the cells appear to be blank and see what they equate to, or use the
debug.print method to analyze that gremlin. You might be able to get around
it by using an Or statement like:

If Me.Range("F10").Value = "" Or Me.Range("F10").Value = 0 Then

But, as I say, I don't know if your formulas call for number values or
string values so I can't give any positive cures.

One other note. If you are going to use the Grid variable in both If
statements, why not list it once, prior to and outside the If...End If
blocks. Then it would apply to both uses within the If statements. Just
looks a little neater.