Referencing cells with input boxes.

[Guest]

Please help this newbie. I want to get a number from a user and transfer that
number to a cell in my worksheet so I can use that number in other formulas.
Any good suggestions or good reference material I might access would be
greatfully and humbly appreciated.

George

 

[Guest]

George,
There are numerous ways to get input from a user: one of the simpler ways in
to use a cell with validation:
Say you want the user to enter a number in Cell A1. You click on A1 then on
the menu item at the top: Data>Validation Then select say Whole numbers and
you fill in the min and max. Or if you want to limit the input to a specific
list of numbers, click on List, then in the RefersTo box at the bottom enter
some numbers separated by commas: 6,12,10,4,25
I like to use this List with the name of a range in the RefersTo box:
=MyList.
then on one of the worksheets I have a list of numbers or text. I select
this list and give it a name in the Name Box (to the left of the formula bar).

Once the user provides the input, you can have links to the input cell. Say
you want to use the user's input in cell F25. Then in F25 you enter: =A1,
or =A1+C25, or whatever.

 

[Claud Balls]

Private Sub Workbook_Open()
response = InputBox("Please enter number", "Number Entry")
Range("A1") = response
End Sub

 

[Norman Jones]

Hi George,

Look in VBA help at the InputBox Function and the InputBoxMethod.

As the syntax for either is relatively straightforward, perhaps the best
additional source would be the NG archives. If you perform a Google Groups
search, I am sure that you will find a wide range of practical usage and
syntactical style.

The following is a simple example which uses the InputBox Fnction which may
suffice for your immediate scenario.

Sub XYZ()
Dim rngDest As Range
Dim res As String, res2 As Long
Dim blRetry As Boolean
Dim msgCancel
Dim msgBlank
Dim msgRetry
Dim msgNoNumber

msgCancel = "You cancelled"
msgBlank = "You clicked OK without entering anything"
msgNoNumber = " is not a number."

blRetry = True

Set rngDest = Range("A1") '<<=== Destination for User Input
Do While blRetry
If Not blRetry Then GoTo XIT
res = InputBox("{Please Enter A number")
If StrPtr(res) = 0 Then
msgbox msgCancel
ElseIf Len(res) = 0 Then
msgbox msgBlank
ElseIf Not IsNumeric(res) Then
msgbox """" & res & """" & msgNoNumber
Else
msgbox "You entered " & res
rngDest.Value = res
Exit Sub
End If
res2 = msgbox("Do you want to try again?", vbYesNo)

blRetry = res2 = vbYes

Loop

XIT:
msgbox "You opted to terminate " & _
"without making a valid entry"
End Sub