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 > 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 <> "" 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