Question about Input Box and how to use the entered value

  • Thread starter Thread starter TBA
  • Start date Start date
T

TBA

How do I use the value a user enters in an Input Box as a variable? What
I'd like to do is use an Input Box where the user enters in the cell address
of the top-left cell in the table they wish to work with. For example they
might type in A1 or B3, etc. Then I want to be able to select that cell
based on what the user types. Can it only be a string value? Any tips
appreciated. Thanks!

-gk-
 
If you use the Application.InputBox you can specify the type of
value to return. Setting type:=8 allows the user to enter (or
select, using the GUI) a cell reference. For example:

Dim rng As Range
Set rng = Application.InputBox( _
Prompt:="Enter or select range:" & vbNewLine, _
Title:="Enter Range", _
Type:=8)
Application.Goto rng(1)
 
Sub Main()
Dim s As String
s = InputBox("Enter something")
MsgBox "String entered: " & s
MsgBox "Numeric value of string entered: " & CStr(Val(s))
End Sub
 
'Actually, the InputBox method of the Application
'object has more Excel friendly options.
'Note the type specifier.
'Put your cursor on Application.InputBox
'and hit F1 for details.

Option Explicit

Sub Main()
'This uses the VBA InputBox.
Dim s As String
s = VBA.InputBox("Enter something")
MsgBox "String entered: " & s
MsgBox "Numeric value of String: " & CStr(Val(s))

'This uses the InputBox method of the Application object.
Dim r As Range
Set r = Application.InputBox( _
prompt:="Enter a range reference", Type:=8)
MsgBox "Range Returned: " & r.AddressLocal

End Sub
 
Back
Top