I'm trying to get input from a user regarding parameters for loan calculations. I'm still very new at VBA so some of this will seem very silly, but I've been trying for hours to get this to work right and I keep fixing problems and creating more simultaneously. I've come to the conclusion that I have a fundamental misunderstanding of how this works.
This one actually works fine, but I am including it just in case I've done something less than perfect and someone can correct it.
Dim Validate As Boolean
Dim Response As Integer
Dim ScheduleName, Amount, IRate, Term, StartDate As Variant
'Get input from user
Do
'Store the input
ScheduleName = Trim(Application.InputBox(Prompt:="Name of the Schedule?" & vbCrLf & _
"Example: House or Car", _
Title:="Schedule Name", _
Type:=2))
'Check to see if the box was escaped or canceled
If ScheduleName = False Then
'If so, exit the sub
Exit Sub
End If
'Check to see if the name already exists
Validate = WorksheetExists(ScheduleName)
'If it does exist, warn the user and repeat the process
If Validate = True Then
Response = MsgBox("A schedule with that name already exists.", _
vbCritical, _
"Name Conflict Error")
End If
Loop While Validate = True
Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function
This one works if I input a number, but I'd like to be able to have the validation also check for a $ and automatically trim it off. I've got it checking for cancel already but I want this input validation to catch everything and ask for corrected input on anything except $xxxx, $xxxx.xx, xxxx.xx, or xxxx (any amount of digits, not just thousands).
Do
'Store the input
Amount = Application.InputBox(Prompt:="What is the amount of the loan in dollars?" & vbCrLf & _
"This does not include closing costs or the down payment." & vbCrLf & _
"Example: $225000", _
Title:="Loan Amount", _
Type:=1)
'Check to see if the box was escaped or canceled
If Amount = False Then
'If so, exit the sub
Exit Sub
End If
'Check to make sure the amount is reasonable
If Amount < 100 Then
Validate = True
Response = MsgBox("Enter a loan value greater than $100.", _
vbCritical, _
"Loan value error")
Else
Validate = False
End If
Loop While Validate = True
I'm not really sure what else to ask at this point. I've been searching google and these forums for a while but I can't find much in the way of examples for robust input validation.
Bookmarks