Dropdown Box Conversion

R

RCrow

I have an excel spreadsheet that has a dropdown box (i.e. cell F6). The
spreadsheet calls a macro based on the choice made in the dropdown box, takes
the data (cost) in cell G6, passes it for conversion to the macro based on
the choice, and outputs the answer to cell H6 (for row = 1 to 65,536). I'm
pretty new to all this, but here is my attempt so far:

Worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("F6:F65536")
Set t = Target
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
For t = 1 To 65536
Call Conversion(t)
Next t
Application.EnableEvents = True
End Sub

And in the modules folder:

Public Sub Conversion(t)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This Macro does conversions based upon choices made in a drop down box '
' Created February 29,2008 '
' '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Roww = t.Row

If Cells("F", Roww).Value = "" Then
Cells("H", Roww).Value = "0.00"
ElseIf Cells(Roww, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?"))
Cells(Roww, "H").Value = Cells(Roww, "G").Value / (myVar * 16)
ElseIf Cells(Roww, "F").Value = "BOTTLE" Then
myVar = Val(InputBox("What is the size of the bottle in ounces?"))
Cells(Roww, "H").Value = Cells(Roww, "G").Value / myVar
ElseIf Cells(Roww, "F").Value = "BOX" Then
myVar = Val(InputBox("What is the size of the box in ounces?"))
Cells(Roww, "H").Value = Cells(Roww, "G").Value / myVar
ElseIf Cells(Roww, "F").Value = "CAN" Then
myVar = Val(InputBox("What is the size of the can in ounces?"))
Cells(Roww, "H").Value = Cells(Roww, "G").Value / myVar
ElseIf Cells(Roww, "F").Value = "GAL" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value / 128
ElseIf Cells(Roww, "F").Value = "GRAM" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value * 0.03527
ElseIf Cells(Roww, "F").Value = "LB" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value / 16
ElseIf Cells(Roww, "F").Value = "OZ" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value
ElseIf Cells(Roww, "F").Value = "PINT" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value / 16
ElseIf Cells(Roww, "F").Value = "PACKET" Then
myVar = Val(InputBox("What is the size of the packet in ounces?"))
Cells(Roww, "H").Value = Cells(Roww, "G").Value / myVar
ElseIf Cells(Roww, "F").Value = "QUART" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value / 32
ElseIf Cells(Roww, "F").Value = "TON" Then
Cells(Roww, "H").Value = Cells(Roww, "G").Value / 32000
End If
End Sub

As you can see, I cant get the thing to work. Any help on this would be
GREATLY appreciated! I know that there are some geniuses out there that can
figure this easy one out!

Thanks in advance!
 
J

JP

Here is the sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range
Dim t As Excel.Range
Dim i As Long

Set rng = Range("F6:F65536")
Set t = Target

If Intersect(t, rng) Is Nothing Then Exit Sub

Application.EnableEvents = False
Call Conversion(Target)
Application.EnableEvents = True

End Sub


This code can go in a standard module:

Public Sub Conversion(rRange As Excel.Range)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This Macro does conversions based upon choices made in a drop down
box '
' Created February
29,2008 '
'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.EnableEvents = False
Dim Roww As Long

Roww = rRange.Row

Select Case Cells(Roww, 6).Value
Case ""
Cells(Roww, 8).Value = "0.00"
Exit Sub
Case "BAG"
myVar = Val(InputBox("What is the size of the bag in
pounds?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / (myVar * 16)
Exit Sub
Case "BOTTLE"
myVar = Val(InputBox("What is the size of the bottle in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "BOX"
myVar = Val(InputBox("What is the size of the box in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "CAN"
myVar = Val(InputBox("What is the size of the can in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "GAL"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 128
Exit Sub
Case "GRAM"
Cells(Roww, 8).Value = Cells(Roww, 7).Value * 0.03527
Exit Sub
Case "LB"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 16
Exit Sub
Case "OZ"
Cells(Roww, 8).Value = Cells(Roww, 7).Value
Exit Sub
Case "PINT"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 16
Exit Sub
Case "PACKET"
myVar = Val(InputBox("What is the size of the packet in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "QUART"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 32
Exit Sub
Case "TON"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 32000
Exit Sub
Case Else
Cells(Roww, 8).Value = "0.00"
Exit Sub
End Select

End Sub


HTH,
JP
 
R

RCrow

TYVM JP....That worked like a champ!!

Rodney

JP said:
Here is the sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range
Dim t As Excel.Range
Dim i As Long

Set rng = Range("F6:F65536")
Set t = Target

If Intersect(t, rng) Is Nothing Then Exit Sub

Application.EnableEvents = False
Call Conversion(Target)
Application.EnableEvents = True

End Sub


This code can go in a standard module:

Public Sub Conversion(rRange As Excel.Range)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This Macro does conversions based upon choices made in a drop down
box '
' Created February
29,2008 '
'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.EnableEvents = False
Dim Roww As Long

Roww = rRange.Row

Select Case Cells(Roww, 6).Value
Case ""
Cells(Roww, 8).Value = "0.00"
Exit Sub
Case "BAG"
myVar = Val(InputBox("What is the size of the bag in
pounds?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / (myVar * 16)
Exit Sub
Case "BOTTLE"
myVar = Val(InputBox("What is the size of the bottle in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "BOX"
myVar = Val(InputBox("What is the size of the box in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "CAN"
myVar = Val(InputBox("What is the size of the can in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "GAL"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 128
Exit Sub
Case "GRAM"
Cells(Roww, 8).Value = Cells(Roww, 7).Value * 0.03527
Exit Sub
Case "LB"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 16
Exit Sub
Case "OZ"
Cells(Roww, 8).Value = Cells(Roww, 7).Value
Exit Sub
Case "PINT"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 16
Exit Sub
Case "PACKET"
myVar = Val(InputBox("What is the size of the packet in
ounces?"))
Cells(Roww, 8).Value = Cells(Roww, 7).Value / myVar
Exit Sub
Case "QUART"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 32
Exit Sub
Case "TON"
Cells(Roww, 8).Value = Cells(Roww, 7).Value / 32000
Exit Sub
Case Else
Cells(Roww, 8).Value = "0.00"
Exit Sub
End Select

End Sub


HTH,
JP
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Question revisited 4
Dynamic file location macro 2
P 1
Minimizing number of commands 0
Word macro that can call cells out of Excel for a find and replace 0
Sumproduct & VBA 4
Backwards compatibile macro 0
Locking specific cells 1

Top