Error Checking with Range cell in Userform. Thank you!

Hi, all

I am trying to build a user form to find out the customers who purchase more than $1,000 during a certain period.

The userform has two inputs:

One is to select the data range of customer information. Assume all customer information are in the cell A2: H10, how shall I write the error checking code if the user selects the range which is out of (A2:H10)? message box would be " You selection include invalid data, please check"..

The other input is called " Get data past this data", and I can enter a date in the following cell ( txtDate). Regarding the error checking, I am thinking to use IsDate() function to make sure it is a valid date. Will be there be any other error checking you will recommend?

Any help is appreciated. thank you!!!

Hi,

For your first question, VBA has built in Intersect and Union functions to compare ranges, but unfortunately does not contain a Difference function. So I am going to attempt to write one here (untested), and hopefully it will work for you...

Function Difference(inputRange As Range) As Boolean
	Dim validRange As Range
	Dim intRange As Range
	
	'Sets the valid range of cells
	Set validRange = ThisWorkbook.Worksheets("WorksheetName").Range("A2:H10")
	
	'Sets the intersection of the inputRange and the validRange
	Set intRange = Intersect(inputRange, validRange)
	
	'To determine if the inputRange is entirely within the validRange,
	'we just need to count the number of cells in the intersection. If
	'the cell count is the same as the number of cells in the input range
	'then I believe we are all sweet, otherwise the inputRange is invalid.
	'NB: the intersection can never have more cells than the inputRange
	If inputRange.Cells.Count > intRange.Cells.Count Then
		'There are more cells in the inputRange than the intRange, so INVALID
		Difference = False
	Else
		'The inputRange has the same number of cells as the intersection, so VALID
		Difference = True
	End If
End Sub

You simply pass the input range into this function, and it will return TRUE if the selected range is valid, and FALSE is it is invalid. You would call this function after the range has been selected, something like this:

Dim blnValid As Boolean
...
Set rng = 'Some code that you get the user to select the range
blnValid = Difference(rng)
If blnValid Then
    'Range is valid
Else
    'Range is invalid
End If
...

For your second question, yes I believe the IsDate() function will work just fine for you. The alternative would be to use a DatePicker so that the input can never not be a date...

I hope this helps