How to make a simple VBA unit converter that references an excel spreadsheet (input and output)?

Hi all, i wrote this pretty simple sheet on excel last night and now i'd like to give it some user appeal by making a Visual basic interface. I have no experience with VB, so I was hoping someone could help me figure out how to do a few things, I've tried looking online but I'm so inexperienced with the software that I don't even know what keywords to search.


There are 2 major things I need it to do:





1) I'll have a feature with a title like "Enter value" [input box]. The user will type a numeric value into that box and it will go to a specific cell in the excel spreadsheet; in this case, B2.





2) The next feature will have an output box. "Result" [ output]. This output is controlled by the excel functions in cell B3. So, after inputting the value for B2, B3 will have an automatic value appear, and I need the VB form to update with the value from B3.





If my explanation is unclear, just think of it as a converter. Imagine that you want to convert the input value, B2, and the output value is controlled by a formula in B3. And the whole point of the VBA userform is just to bypass the spreadsheet and allow the person to simply type in their value and have it automatically show the result.





That's just about it, There's more to it, but those 2 tasks are the root of what I need to figure out, I can probably work around the rest.





Thanks in advanced for your help!|||You can also do as you wish without a userform.





For example the following Before_DoubleClick event handler will query the user for a value. When entered, it will display a message box with the result.





Copy the following event handler to the clipboard:





Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


inputValue = Val(InputBox("Enter value", "Input"))


If inputValue = "" Then


Exit Sub


End If


Range("B2").Value = inputValue


MsgBox Range("B3").Value, vbOKOnly, "Result"


End Sub





Select the appropriate worksheet and right click the sheet tab.





Select 'View Code'.





Paste the event handler into the editing area to the right.





Close the VBE and return to the worksheet.





Double click any cell, enter a value, and press Enter or click 'OK'.





==================





If you wish to use a userform to do this, and you have created your userform using the default control and userform names, you can use the following codes.





First, you will need to create a macro to call the userform.





Copy this macro to the clipboard:





Sub shwForm ()


Userform1.Show


End Sub





Press ALT + F11





In the menus at the top of the VBE, select INSERT %26gt; MODULE





Paste the macro into the editing area to the right.





Close the VBE and return to the worksheet.





If you wish to show the form using a command button, create one from the Forms toolbar. Assign the macro above to the commandbutton.





Otherwise, you can call the macro using a keyboard shortcut.





Press ALT + F8





When the Macros window opens, highlight the macro and click 'Options...'





Enter a letter to be used as a keyboard shortcut and click 'OK'.





Close the Macros window.





This will now enable you to show the userform.





Now for the code to enable the userform functionality. The following assumes your textbox for entry of a value is named TextBox1 and for the result is named TextBox2.





Access the userform in the VBE and right click anywhere in the body of the form.





Select 'View Code'.





Paste the following event handler into the editing area to the right.








Private Sub TextBox1_Change()


Range("B2").Value = Me.TextBox1.Value


Me.TextBox2.Value = Range("B3").Value


End Sub





Close the VBE and return to the worksheet. Press your command button, or activate your keyboard shortcut, to call the userform.





Enter a value in TextBox1, and TextBox2 will return the value of B3.





Of course, there are other things you can do to enhance the code. For example, you can restrict the value enter to a numeric value with an event handler like this:








Private Sub TextBox1_Change()


If Not IsNumeric(Me.TextBox1) And Me.TextBox1.Value %26lt;%26gt; "" Then


MsgBox "Entry must be numeric", vbCritical, "Invalid Entry"


Me.TextBox1.SetFocus


Exit Sub


End If


Range("B2").Value = Me.TextBox1.Value


Me.TextBox2.Value = Range("B3").Value


End Sub|||An Excel Userform will do the trick.





Here's a link, but you can find hundreds of examples on the web


http://www.exceltip.com/st/Create_User_F鈥?/a>