Input Box Data Validation

[Derrick Rikley]

Hi Everyone,

I have an input box that asks for user input and drops it into a cell.

Dim strUserResponse As String
strUserResponse = InputBox("Please Enter the Build Location - CRK, STC, WPG", "Site")
Worksheets("Sheet1").Range("AA2").value = strUserResponse

I have numerous formuals that are based on this info so if it's typed incorrectly, my sheets won't work. How do I validate their input and return a message if they have entered the wrong info?

Thanks!!!

 

[Marc L]

​

Hi !​

    BL = [{"CRK","STC","WPG"}]
    S$ = InputBox("Please Enter the Build Location - " & Join(BL, ", "), "Site")
 
    If IsError(Application.Match(S, BL, 0)) Then MsgBox "Wrong info !", vbExclamation, "Site" _
                                            Else Worksheets("Sheet1").[AA2].Value = S

 

[Derrick Rikley]

Thanks Marc!

That works! But when someone enters the incorrect info and they receive the "Wrong Info" notification, I need the code to loop back to the iniput box so the can re-enter the info. Any suggestions?

 

[Abhijeet R. Joshi]

I think using an userform(make it look more like an inputbox) would be another option to this, as you can define your own list and ask the users to select only the correct data...
In case you agree to this, you can have a look here..

http://support.microsoft.com/kb/141573

 

[NARAYANK991]

Hi Rikley ,

Unless your code is part of a bigger and more complex procedure , you can easily replicate the functionality of what you want by a simple Data Validation drop-down in AA2 ; the only point against this approach would be that it is left to the user to go to the cell and make a selection.

Narayan

 

[Marc L]

But when someone enters the incorrect info and they receive the "Wrong Info" notification, I need the code to loop back to the iniput box so the can re-enter the info.

    BL = [{"CRK","STC","WPG"}]
 
    Do
        S$ = InputBox("Please Enter the Build Location - " & Join(BL, ", "), "Site")
         V = IsError(Application.Match(S, BL, 0)) And S > ""
        If V Then MsgBox "Wrong info !", vbExclamation, "Site"
    Loop While V
 
    If S > "" Then Worksheets("Sheet1").[AA2].Value = S

Enjoy !​

​

 

[Derrick Rikley]

I think using an userform(make it look more like an inputbox) would be another option to this, as you can define your own list and ask the users to select only the correct data...
In case you agree to this, you can have a look here..

Thanks Abhijeet, I looked at the link and that isn't going to work for me this time.

 

[Derrick Rikley]

Hi Rikley ,

Unless your code is part of a bigger and more complex procedure , you can easily replicate the functionality of what you want by a simple Data Validation drop-down in AA2 ; the only point against this approach would be that it is left to the user to go to the cell and make a selection.

Narayan

Thanks Narayan,

It is part of a long set of procedures in this module. I started with a drop-down list and eventually has to discard the procedure for functionality.

 

[Derrick Rikley]

    BL = [{"CRK","STC","WPG"}]
 
    Do
        S$ = InputBox("Please Enter the Build Location - " & Join(BL, ", "), "Site")
        V = IsError(Application.Match(S, BL, 0)) And S > ""
        If V Then MsgBox "Wrong info !", vbExclamation, "Site"
    Loop While V
 
    If S > "" Then Worksheets("Sheet1").[AA2].Value = S

Enjoy !​

​

MARC!! You are a GOD! That's exactly it! Thank you!!! I could not get my loop to work properly. Constant error messages. Thanks again!