VBA/Excel/Access/Word/Language Basics/InputBox
Содержание
- 1 Checks for the Cancel button clicks and takes no action.
- 2 Combine InputBox and Do Loop to read user input
- 3 Get the Password
- 4 Get the return value from InputBox
- 5 How to handle error caused by value from InputBox
- 6 It is a good idea to include the title and default arguments to provide the user with a little help in knowing what to enter.
- 7 Provide help file for InputBox
- 8 Read data from dialog box and fill it into the cell
- 9 Read password from InputBox
- 10 Reference InputBox from Application
- 11 Return a number from InputBox
- 12 The InputBox function displays a dialog box containing a simple text box
- 13 To make sure that the user has chosen the OK button, check that the input box hasn"t returned a zero-length string
- 14 To retrieve input from an input box, declare the numeric variable or String variable that will contain it
- 15 Use If and IsNumeric function to check user input
- 16 Use InputBox to read value for an array
- 17 Use parameter name in InputBox function
- 18 Using the InputBox Function: InputBox(prompt [, title] [, default] [, xpos] [, ypos] [, helpfile, context])
- 19 Validate the user"s entry.
- 20 Validation with the InputBox() Function
Checks for the Cancel button clicks and takes no action.
<source lang="vb">
Sub GetValue2()
UserEntry = InputBox("Enter the value") If UserEntry <> "" Then range("A1").value = UserEntry
End Sub
</source>
Combine InputBox and Do Loop to read user input
<source lang="vb">
Sub GetValue3()
MinVal = 1 MaxVal = 12 Msg = "Enter a value between 1 and 12" ValidEntry = False Do UserEntry = InputBox(Msg) If IsNumeric(UserEntry) Then If UserEntry >= 1 And UserEntry <= 12 Then ValidEntry = True Else Msg = "Your previous entry was INVALID. Enter a value between 1 and 12" End If End If If UserEntry = "" Then Exit Sub Loop Until ValidEntry ActiveSheet.Range("A1").Value = Val(UserEntry)
End Sub
</source>
Get the Password
<source lang="vb"> Sub GetPassword() Dim sPassWord As String, i As Integer i = 0 Do i = i + 1 If i > 3 Then MsgBox "Sorry, Only three tries" Exit Sub End If sPassWord = InputBox("Enter Password") Loop Until sPassWord = "XXX" MsgBox "Welcome" End Sub </source>
Get the return value from InputBox
<source lang="vb">
Sub InputBoxDemo()
Dim myValue myValue = InputBox("Please enter number of hours worked", "Hours Worked") MsgBox myValue
End Sub
</source>
How to handle error caused by value from InputBox
<source lang="vb">
Public Sub ErrorHandling()
On Error GoTo ErrorHandling_Err Dim dblResult As Double " holds the result of the division dblResult = 10 / InputBox("Enter a number:") MsgBox "The result is " & dblResult
ErrorHandling_Exit:
Exit Sub
ErrorHandling_Err:
Select Case Err.Number Case 13 " type mismatch - empty entry Resume Case 11 " division by 0 dblResult = 0 Resume Next Case Else MsgBox "Oops: " & Err.Description & " - " & Err.Number Resume ErrorHandling_Exit End Select
End Sub
</source>
It is a good idea to include the title and default arguments to provide the user with a little help in knowing what to enter.
<source lang="vb">
Sub GetRes()
Dim retValue As String retValue = InputBox("Enter your name.", "Name", "First, Last")
End Sub
</source>
Provide help file for InputBox
<source lang="vb">
Sub inputDemo()
Dim strWhichOffice As String strWhichOffice = InputBox("Enter the name of the office that you visited:", _ "Expense Assistant", "defaultValue", , , _ "c:\Help.chm", 0)
End Sub
</source>
Read data from dialog box and fill it into the cell
<source lang="vb">
Sub GetData()
Sales = InputBox(Prompt:="Enter Target Sales") If Sales = "" Then Exit Sub Range("B2").Value = Sales
End Sub
</source>
Read password from InputBox
<source lang="vb">
Sub GetPassword()
Dim PassWord As String, i As Integer i = 0 Do i = i + 1 If i > 3 Then MsgBox "Sorry, Only three tries" Exit Sub End If PassWord = InputBox("Enter Password") Loop Until PassWord = "XXX" MsgBox "Welcome"
End Sub
</source>
Reference InputBox from Application
<source lang="vb">
Public Sub InputBoxTest()
Dim Answer As Range Set Answer = Application.InputBox(prompt:="Enter range", Type:=8)
End Sub
</source>
Return a number from InputBox
<source lang="vb">
Sub AddTwoNums()
Dim myPrompt As String Dim value1 As String Dim mySum As Single Const myTitle = "Enter data" myPrompt = "Enter a number:" value1 = InputBox(myPrompt, myTitle, 0) mySum = value1 + 2 MsgBox mySum & "(" & value1 & "+2)"
End Sub
</source>
The InputBox function displays a dialog box containing a simple text box
<source lang="vb">
Sub InputBoxExample()
Dim strName As String strName = InputBox("What is Your Name?", "This is the Title", "This is the Default") msgBox "You Entered " & strName
End Sub
</source>
To make sure that the user has chosen the OK button, check that the input box hasn"t returned a zero-length string
<source lang="vb">
Sub res()
Dim strWhichOffice strWhichOffice = InputBox("Enter the name of the office:", _ "Expense Assistant 2000", "default", , , _ "c:\Help.chm", 0) If strWhichOffice = "" Then End
End Sub
</source>
To retrieve input from an input box, declare the numeric variable or String variable that will contain it
<source lang="vb">
Sub help()
Dim strWhichOffice strWhichOffice = _ InputBox("Enter the name of the office:", _ "Expense Assistant 2000", "default", , , _ "c:\Help.chm", 0)
End Sub
</source>
Use If and IsNumeric function to check user input
<source lang="vb">
Sub macro_if()
Dim number number = InputBox("Type in a number, please!") If Not IsNumeric(number) Then MsgBox "This is no number!" ElseIf number > 10 Then MsgBox "The number is larger than 10." Else MsgBox "The number is smaller then or equal 10." End If
End Sub
</source>
Use InputBox to read value for an array
<source lang="vb">
"Option Compare Database Option Explicit Public Sub ArrayExample()
Dim i As Integer Dim intNum(1 To 10) As Integer For i = 1 To 10 intNum(i) = InputBox("Please enter guess " & i, "Guess!") Next i For i = 1 To 10 Debug.Print "Guess number " & i & " = " & intNum(i) Next i
End Sub
</source>
Use parameter name in InputBox function
<source lang="vb"> Sub GetRange() Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="Specify a range:", Type:=8) If Rng Is Nothing Then Exit Sub MsgBox "You selected range " & Rng.Address End Sub </source>
Using the InputBox Function: InputBox(prompt [, title] [, default] [, xpos] [, ypos] [, helpfile, context])
<source lang="vb">
Sub Informant()
InputBox prompt:="Enter your place of birth:" & Chr(13) & " (e.g., Boston, Great Falls, etc.) "
End Sub
</source>
Validate the user"s entry.
<source lang="vb">
Sub GetValue3()
MinVal = 1 MaxVal = 12 msg = "Enter a value between 1 and 12" ValidEntry = False Do UserEntry = InputBox(msg) If IsNumeric(UserEntry) Then If UserEntry >= 1 And UserEntry <= 12 Then ValidEntry = True Else msg = "Your previous entry was INVALID." msg = msg & "Enter a value between 1 and 12" End If End If If UserEntry = "" Then Exit Sub Loop Until ValidEntry ActiveSheet.range("A1").value = UserEntry
End Sub
</source>
Validation with the InputBox() Function
<source lang="vb">
Sub Use()
Dim userName As String Dim userBirthday As Date Dim nameOk As Boolean nameOk = True Do userName = InputBox("What is your first and last name?", "Name") If (userName <> "") Then nameOk = ValidateName(userName) Loop While (nameOk = False) Or (userName <> "")
End Sub Function ValidateName(userName As String) As Boolean
Dim strLength As Integer Dim I As Integer Dim numSpaces As Integer Dim tempString As String Dim msb As Integer userName = Trim(userName) strLength = Len(userName) For I = 1 To strLength If Left(userName, 1) = " " Then numSpaces = numSpaces + 1 End If userName = Right(userName, Len(userName) - 1) Next I If (numSpaces <> 1) Then ValidateName = False msb = MsgBox("Please enter just two names separated by one space", vbCritical, "Error") Else ValidateName = True End If
End Function
</source>