| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with cell value
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. -- Karen |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with cell value
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? "Karen53" wrote: 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. -- Karen |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with cell value
Also, if F10 does equal null then it goes directly to ElseIf and it will not
know what Grid is. "Karen53" wrote: 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. -- Karen |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with cell value
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. -- Karen "JLGWhiz" wrote: 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? "Karen53" wrote: 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. -- Karen |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with cell value
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. "Karen53" wrote: 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. -- Karen "JLGWhiz" wrote: 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? "Karen53" wrote: 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. -- Karen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trouble in Dragging the cell | Excel Worksheet Functions | |||
| Cell locking trouble | Excel Discussion (Misc queries) | |||
| Cell locking trouble | New Users to Excel | |||
| Trouble getting the value of a cell | Excel Programming | |||
| trouble with cell choice | Excel Programming | |||
Linear Mode
