Cell data keeps altering - not sure why

Cell S17 should looks like =IF(R16>0.05,">0.05, therefore treat as Hill/Ridge","<0.05, therefore treat as Escarpment")

If cell R16 ends up <0.05 cell S17 is stripped of its formula and the text '<0.05, therefore treat as Escarpment' is left

I have a small bit of VBA in the sheet doing chart control. This is the only bit I can find that may be messing with cell S17

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("s17").Value = ">0.05, therefore treat as Hill/Ridge" Then
          Sheets("Orography").ChartObjects("Chart 26").Visible = True
          Sheets("Orography").ChartObjects("Chart 1").Visible = False
Else: Range("S17").Value = "<0.05, therefore treat as Escarpment"
          Sheets("Orography").ChartObjects("Chart 26").Visible = False
          Sheets("Orography").ChartObjects("Chart 1").Visible = True
     End If
End Sub
Book2.xlsm
vipamanAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
The compare i select case can't be done twice, but only to the result of Range("S16").Value
You should use Worksheet_Change, not Worksheet_SelectionChange, because it is only relevant when values change.

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Range("S16").Value

Case ">0.05, therefore treat as Hill/Ridge"
          Sheets("Orography").ChartObjects("Chart 28").Visible = True
          Sheets("Orography").ChartObjects("Chart 27").Visible = False

Case "<0.05, therefore treat as Escarpment"
          Sheets("Orography").ChartObjects("Chart 28").Visible = False
          Sheets("Orography").ChartObjects("Chart 27").Visible = True

End Select
End Sub

Open in new window

0
 
Ejgil HedegaardCommented:
S17 compare R16 to 0.05, but R16 is the downwind slope length.
The compare should be to R17.

The VBA code does not change S17.
0
 
vipamanAuthor Commented:
Hi, Sorry I inserted an extra line in the worksheet. If you change the R16 to R17. The problem still occurs. I have updated the spreadsheet. See attached. If you alter the height in cell R15 - S17 gets stripped of its formula. You can paste back in from the cell underneath but it repeatedly strips the formula once the ratio goes below 0.05
Book3.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
Your script is changing the value of the cell.

If statement does the following:

If greater than 0.05
Change the visibility of the charts.
Else
Change the cell value
Change visibility of charts
End If

The Else part of the script does not need to check the value of the range as the first part will check and then the script will go to the second part of the condition is not met:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("S17").Value = ">0.05, therefore treat as Hill/Ridge" Then
          Sheets("Orography").ChartObjects("Chart 26").Visible = True
          Sheets("Orography").ChartObjects("Chart 1").Visible = False
Else
          Sheets("Orography").ChartObjects("Chart 26").Visible = False
          Sheets("Orography").ChartObjects("Chart 1").Visible = True
End If
End Sub

Open in new window


Alternatively you could use the Select Case function:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Range("S17").Value

Case Range("S17").Value = ">0.05, therefore treat as Hill/Ridge"
          Sheets("Orography").ChartObjects("Chart 26").Visible = True
          Sheets("Orography").ChartObjects("Chart 1").Visible = False

Case Range("S17").Value = "<0.05, therefore treat as Escarpment"
          Sheets("Orography").ChartObjects("Chart 26").Visible = False
          Sheets("Orography").ChartObjects("Chart 1").Visible = True

End Select
End Sub

Open in new window

0
 
vipamanAuthor Commented:
I had the Select Case range function working, but there was a major issue with the graphs due to an excel crash. I had to rebuild them. The chart names have changed but I updated the code. The calcs all work now - you enter a value or say 20 or 30 in cell R14 and the calcs swap between escarpment and hill mode. The only thing now is the charts both display. I have gone around in circles with this. I don't know what I am doing wrong. File attached. Thanks in advance.
book5.xlsm
0
 
vipamanAuthor Commented:
Excellent! frustrations over for the moment
0
 
Rob HensonFinance AnalystCommented:
Some assistance points for pointing you down the right path in the first place would have been appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.