в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:33Z

1 версия

Новая страница

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

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Language Basics/InputBox - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия