drop down option

[peerogel]

I am horrible at explaining things so hopefully I do this right. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Cell A1:A3 will be my input values. Cell B1 adds those values. <o></o>
<o></o>
I wanted Cell C1 when B1 was grater than zero to return as FPS.<o></o>
<o></o>
I got it to work by doing the following if function. (=IF(B1>0,"FPS",""))<o></o>
<o></o>
However I also wanted to make a dropdown in cell C1 with two options "FPS" and "MPH". But I read somewhere in the forums that you cannot combine the dropdown and the if formula. <o></o>
<o></o>
So I was wondering if I can get some help. <o></o>
<o></o>
My main goal is to make the drop down in cell C1 with those two options (FPS and MPH). When the values in A1:A3 are entered C1 automatically changes to FPS. Then if I pull down the drop down and Choose "MPH", B1 gets divided by 1.466. <o></o>
<o></o>
or have cell b1 with formatted with to return the value with "fps" and if MPH is selected in change the format to "mph" and somehow also divide it by 1.466. <o></o>
<o></o>
I don’t mind hiding cells, I tried VLookup but im not good at using it and just could not get it to work. <o></o>
<o></o>
Thanks for any help. <o></o>

 

[Simon Lloyd]

Create your dropdown in C1 then right click the sheet tab and choose View Code and paste this in, it should do what you want but untested!

Private Sub Worksheet_Calculate()
If Me.Range("B1") > 0 And Me.Range("C1").Value <> "MPH" Then
Me.Range("C1").Value = "FPS"
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Me.Range("C1").Value = "MPH" Then
Me.Range("B1").Formula = "=sum(A1:A3)/1.466"
Me.Calculate
Else
Me.Range("B1").Formula = "=sum(A1:A3)"
Me.Calculate
End If
End Sub

 

[peerogel]

Thanks for the quick response.

It works execpt that after i select what i want, for example MPH, b1 does not change. I have to click on the menu again and then it will change. Then if i try to go back to FPS, same thing it will not change until i click on the menu again.

I also noticed for example it, if i change it to MPH and clear the contentsts of A1:A3 and C1 and then input the values on A1:A3 again FPS will appear on C1 but B1 will have the value of MPH. B1 will only change once i click on C1.

Once again thanks for the quick responce, I had burnt my last brain cell trying to figure out how to do it with Vlookup. I got it to work with vlookup but i think your macro is much much better.

 

[peerogel]

I forgot to mention, after I make the selection in C1, I have to click on another cell then click back on C1 for B1 to update.

Because if i make a selection on C1 and stay on the dropdown and I click on it after the selection, it wont update.

 

[Simon Lloyd]

I'll sort this tomorrow when i have time to use Worksheet_Change event rather than Worksheet_SelectionChange

 

[peerogel]

Thank you. ray:

 

[Simon Lloyd]

Here you go this should do what you want

'code supplied by Simon Lloyd
'23rd February 2011
'the Code Cage
Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.CountA(Me.Range("A1:A3")) = 0 Then Me.Range("C1").Value = ""
If Me.Range("B1") > 0 And Me.Range("C1").Value <> "MPH" Then
Me.Range("C1").Value = "FPS"
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Me.Range("C1").Value = "MPH" Then
Me.Range("B1").Formula = "=sum(A1:A3)/1.466"
Me.Calculate
Else
Me.Range("B1").Formula = "=sum(A1:A3)"
Me.Calculate
End If
End Sub

Now when entering any figure in A1 to A3 B1 changes value and C1 will display FPS, if you change it to MPH then B1 will be divided by 1.466, if you clear all contents from A1:A3 then C1 will be blank.

 

[peerogel]

Thank you. It works perfectly. But I have a problem, the reason i choosed sum of A1:A3 is because i can never explain everything correctly and figured I wouldnt confuse anyone like that.

I want to use the drop down menu for FPS and MPH for different equations. I figured I could just modify the range.formula line and problem solved.

But im getting an error when i clear the cells. This is what i changed. The debbuger highlights the third line of the first macro but everything else appears to be working fine.

This equation is to find the radius from lenght and height. A1 is lenght and A2 is height. (im also going to change for this equation only the "FPS and MPH" to "IN and FT" but i dont think i should have an issue with that.)

Once again, Thanks a million for your time.

Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.CountA(Me.Range("A2:A3")) = 0 Then Me.Range("C1").Value = ""
If Me.Range("B1") > 0 And Me.Range("C1").Value <> "MPH" Then
Me.Range("C1").Value = "FPS"
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Me.Range("C1").Value = "MPH" Then
Me.Range("B1").Formula = "=((A2^2)/(8*A3)+(A3/2))/1.466"
Me.Calculate
Else
Me.Range("B1").Formula = "=((A2^2)/(8*A3)+(A3/2))"
Me.Calculate
End If
End Sub

 

[Simon Lloyd]

These "=((A2^2)/(8*A3)+(A3/2))/1.466" need changing for "=Sum((A2^2)/(8*A3)+(A3/2))/1.466", so you need to add SUM.

EDIT: or probaly surround the equation in an IF statement to account for the error that arises when there is nothing to divide or multiply by, this might help http://www.thecodecage.com/forumz/excel-tutorials/117645-building-formulas-tutorial.html#post563726

 

[peerogel]

I tried adding sum and it didnt work. I tried the link but I could not figure out where to put and how to rewrite the macro. But i found a post by Schreiner and this is what i came up with and it appears to be working fine.

Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.CountA(Me.Range("A2:A3")) = 0 Then Me.Range("C1").Value = ""
If TypeName(Range("B1").Value) = "Error" Then Exit Sub
If Me.Range("B1") > 0 And Me.Range("C1").Value <> "MPH" Then
Me.Range("C1").Value = "FPS"
End If
End Sub