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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.