EXCEL VBA : How to validate value with isString or isNumeric?

Question

Here is my code below:

Dim m As String, n As Long

n = InputBox("Enter sales amount: ")

If n < 500 Or n > 5000 Then
ActiveCell.Value = n
ActiveCell.Interior.Color = RGB(255, 0, 0)
m = InputBox("Reason why increase/decrease? ")
ActiveCell.Offset(0, 1).Value = m
ActiveCell.Offset(1, 0).Select

Else
ActiveCell.Value = n
ActiveCell.Offset(1, 0).Select
End If

I was figuring out how do I validate value if input was string then inputbox will prompt and ask for integer ? Thanks in advance for the answers.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Code:

Dim m As String, n As String, sales as long
TryAgain:

n = InputBox("Enter sales amount: ")

If Not IsNumeric(n) Then
    MsgBox "Entry should be a number!"
    GoTo TryAgain
End If

sales = CLng(n)

If sales < 500 Or sales > 5000 Then
ActiveCell.Value = sales
ActiveCell.Interior.Color = RGB(255, 0, 0)
m = InputBox("Reason why increase/decrease? ")
ActiveCell.Offset(0, 1).Value = m
ActiveCell.Offset(1, 0).Select

Else
ActiveCell.Value = sales
ActiveCell.Offset(1, 0).Select
End If

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 17, 2014 at 15:06

Mr. Mascaro

2,7431 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anon231125 Over a year ago

Hi ! What if I want to put validation too in "reason why increase/decrease" ? if integer was an input then gonna ask for string

Mr. Mascaro Over a year ago

From a business needs standpoint that will be much more difficult. Would you reject 5, but accept awuifbouiqebv?

Anon231125 Over a year ago

Sorry, but does my second question sounds impossible to code? please give me some tips. Thanks in advance !

Mr. Mascaro Over a year ago

I don't think that it will be possible to vaidate something that is free text. You'll have to decode what your business rules are for the response and test for all of those. I doubt testing if it's a number will actually satisfy the business requirements.

Collectives™ on Stack Overflow

EXCEL VBA : How to validate value with isString or isNumeric?

1 Answer 1

Code:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Code:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related