Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Dropdown Box Conversion
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Dropdown Box Conversion
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!



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Dropdown Box Conversion
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


On Mar 6, 8:28*pm, RCrow wrote:
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!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Dropdown Box Conversion
TYVM JP....That worked like a champ!!

Rodney

"JP" wrote:

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


On Mar 6, 8:28 pm, RCrow wrote:
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!


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Dropdown Box Conversion
Glad to hear it and thanks for letting me know!

--JP

On Mar 6, 10:06*pm, RCrow wrote:
TYVM JP....That worked like a champ!!

Rodney



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
select From dropdown and return another dropdown menu RE4379 Excel Discussion (Misc queries) 2 March 11th 10 03:09 PM
Dropdown box display only data dependent on another dropdown box? Chris Excel Worksheet Functions 8 August 5th 08 05:01 PM
Dropdown List within a dropdown Henn9660 Excel Worksheet Functions 1 April 10th 08 07:42 PM
populating a dropdown based on choice from a previous dropdown Conor[_3_] Excel Programming 2 March 9th 06 07:15 PM
offer dropdown options based on another dropdown Conor Excel Discussion (Misc queries) 2 January 13th 06 04:28 PM


All times are GMT +1. The time now is 10:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement