LinkBack URL
About LinkBacks
I am trying to make a convert button in cell a1 to convert any inputed value in b6 back and forth from in to mm.
I am trying to make a convert button in cell a1 to convert any inputed value in b6 back and forth from in to mm.
I would put a dropdown in C6 (in or mm) and have B6 for numerical entry only
Then the button macro can beDoes that work for you?
Sub Convert1() If Range("C6") = "mm" Then Range("B6").Value = WorksheetFunction.Convert(Range("B6"), "in", "m") * 1000 ElseIf Range("C6") = "in" Then Range("B6").Value = WorksheetFunction.Convert(Range("B6"), "m", "in") / 1000 End If End Sub
Last edited by ChemistB; 08-18-2015 at 11:56 AM. Reason: Opps mm
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I am not familiar with VBA and Macros. Your formula works if play the macro. How can I get it to automatically update when the drop-down is changed?
Right click on the sheet tab and "View Code" Paste this in. Modify the range in the intersect to match your sheet.
The code is triggered if either B2 or C2 is changed.Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("C2")) Is Nothing Then If Target = "in" Then Target.Offset(-1, -1).Value = WorksheetFunction.Convert(Target.Offset(0, -1), "in", "m") * 1000 Target.Offset(-1, 0).Value = "mm" ElseIf Target = "mm" Then Target.Offset(-1, -1).Value = WorksheetFunction.Convert(Target.Offset(0, -1), "m", "in") / 1000 Target.Offset(-1, 0).Value = "In" End If ElseIf Not Intersect(Target, Range("B2")) Is Nothing Then If Target.Offset(0, 1).Value = "in" Then Target.Offset(-1, 0).Value = WorksheetFunction.Convert(Target.Value, "in", "m") * 1000 Target.Offset(-1, 1).Value = "mm" ElseIf Target.Offset(0, 1).Value = "mm" Then Target.Offset(-1, 0).Value = WorksheetFunction.Convert(Target.Value, "m", "in") / 1000 Target.Offset(-1, 1).Value = "In" End If End If End Sub
Thank you for the replies, the worksheet you sent works but I am unable to modify your code to work on my work sheet. If youhave time, here is a copy of what i am trying to do. I would like the value in cell I10 to automatically change back and forth when the drop down in N10 is changed.
Okay, so whatever number it is now, it will switch to the equivalent in the other system.
So it's set to 1 now and inches. If I switch to mm, you want it to switch to 25.4 (approximately), yes?
Yes, and if changed to another value other than 1, it should still be able to convert.
Okay, here is the code and here is the workbook.
I assume you have specific tool diameters you can work with? I would suggest that I10 be a dependent dropdown menu. If N10 = inches, the dropdown has a list of available inches, In N10 = mm, the dropdown has a list of avialble mm diameters. Does that make sense?Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("N10")) Is Nothing Then If Target = "in" Then Target.Offset(0, -5).Value = WorksheetFunction.Convert(Target.Offset(0, -5), "m", "in") / 1000 ElseIf Target = "mm" Then Target.Offset(0, -5).Value = WorksheetFunction.Convert(Target.Offset(0, -5), "in", "m") * 1000 End If End If End Sub
No specific tool diameters. Is there any way to prevent multiple calculations if the selection is not changed. If the selection in mm and I select mm again, the macro runs.
Replace the current code with this code
Does that work?Public oldval As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = oldval Then Exit Sub If Not Intersect(Target, Range("N10")) Is Nothing Then If Target = "in" Then Target.Offset(0, -5).Value = WorksheetFunction.Convert(Target.Offset(0, -5), "m", "in") / 1000 ElseIf Target = "mm" Then Target.Offset(0, -5).Value = WorksheetFunction.Convert(Target.Offset(0, -5), "in", "m") * 1000 End If End If oldval = Target.Value End Sub
Yes, Perfect. Thank You!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks