How can I use data validation using VBA and throw error if condition not met?

Question

0.00/5 (No votes)

See more:

Hello,

I am new to VBA . I started coding just now, and I wanted to restrict values in cells between 1-10 by data validation through VBA and also to throw error if condition is not true.

But I am unable to do so.

Please go though my code and help me on this.

I would really appreciate it.

What I have tried:

VB

Sub form()

Dim userinput As String
Dim promptmsg As String
Dim tryerror As Integer

promptmsg = "Please enter a numeric value to enter"
'

'
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C6").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C8").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("C4").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
    Range("C4").Select
    
    If ActiveCell.Value >= 1 And ActiveCell.Value <= 10 Then
    

    
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="1", Formula2:="10"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Please try again"
        .ShowInput = True
        .ShowError = True
    End With
    
    Range("C4").Select
    userinput = InputBox(promptmsg)
    
    ActiveCell.FormulaR1C1 = userinput
    
    Range("C6").Select
    
    userinput = InputBox(promptmsg)
    
    ActiveCell.FormulaR1C1 = userinput
    
    Range("C8").Select
    
    userinput = InputBox(promptmsg)
    
    ActiveCell.FormulaR1C1 = userinput
    
    Range("C5").Select
    
    Else
    
    tryerror = msgbox("invalid value! try again?", vbYesNo)
    
    
    If tryagain = 6 Then
    form
    
    End If
     End If
     
    End Sub

Posted 11-Nov-19 5:44am

shivambhat

Updated 11-Nov-19 22:24pm

CHill60

v2

Add a Solution

Comments

Richard MacCutchan 11-Nov-19 11:56am

You have not told us what the problem is, and where it occurs. You could also simplify this by just trying simple values. Only when you have that working should you think about colours and patterns.

ZurdoDev 11-Nov-19 14:16pm

What is wrong with your code? I'm not going to try and run it.

ZurdoDev 11-Nov-19 14:17pm

Record a macro doing close to what you want and you'll be able to see what the code is.

shivambhat 12-Nov-19 6:22am

This was done by recording only,but thats not the issue.the problem occurs when i am trying to put conditional statement and also making using of it to throw error if the condition is not true.

ZurdoDev 12-Nov-19 7:04am

Yes, as Solution 1 points out, you can record a macro entering the Validation. Excel has a validation option.

If it doesn't do what you want, you need to be more clear.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-11-11T22:25:00

Based on MS Office documentation[^]:

VB

With Range("C1:C10").Validation 
 .Add Type:=xlValidateWholeNumber, AlertStyle:= xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="10" 
 .InputTitle = "Integers" 
 .ErrorTitle = "Integers" 
 .InputMessage = "Enter an integer from one to ten" 
 .ErrorMessage = "You must enter a number from one to ten" 
End With