We help IT Professionals succeed at work.

Input Box and Do Loop

I need to write a sub that uses an inputbox to ask the user for a product code.This needs to be Embedded in a Do loop so that the user has to keep trying until the result is not a blank string.

I am stuck. any help is much appreciated.
Comment
Watch Question

Commented:
Try this. Keep in mind that clicking cancel returns an empty string as well:
Function GetProductCode()
	Do 
		strResponse = InputBox("Enter Product Code", "Enter Product Code")
	Loop While strResponse = ""
	GetProductCode = strResponse
End Function

Open in new window

Author

Commented:
Okay this is helpful, however i would like it to continue looping for all responses that do not match the format.

For example: if the input is not entered as AA123 then the loop continues
Commented:
What pattern/format are you looking for? This can be done with regular expressions. The below code will check that the response has two letters followed by between 1 and 3 numbers. All other entered values will cause the input box to appear again.

Function GetProductCode()
	Set objRegEx = New RegExp
	objRegEx.Pattern = "^[a-zA-Z]{1,2}\d{1,3}$"
	Do 
		strResponse = InputBox("Enter Product Code", "Enter Product Code")
	Loop Until objRegEx.Test(strResponse)
	GetProductCode = strResponse
	Set objRegEx = Nothing
End Function 

Open in new window

Author

Commented:
This works. Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.