Validating data

Consider the workbook SALESMAN. The following program is concerned with ensuring that data entered into the weekly sales range are strictly numeric values within a set range. This has been arbitrarily selected as between 0 and 100. A VBA program will need to scrutinise each cell in the weekly sales range and check to see that its value is numeric and within the range 0 to 100. Should a value be entered that is outside this valid range, then the program should respond with a message requesting that the user should input only acceptable data. The program is also required to colour any invalid cell red.

The structured English pseudocode follows:

Unprotect the Weekly Sales sheet For each cell in the range Weekly Sales Check to see if the cell is Numeric

If it is not, change its colour to red and display a message to the user to ensure the cell content is numeric Then check to see if the cell is within the range 0 to 100 If it is not, change its colour to red and display a message to the user to ensure that the cell content is within the required limits. Otherwise leave alone and go on to the next cell in the range

The purpose of the following VBA macro is to validate the data in the range week_sales so that if any cell in this range is either not numeric or if it is outside the range 0-100, then a message will alert the user. Furthermore, the offending cell will be solidly coloured red.

Listing 4.2 Validating Data in the week sales Range

' Validate Week_sales data input

' checks that data input in the week sales is a number in the range 0-100

Sub validateWeekSales()

Dim Cell As Object

Worksheets("Weeklysales").select

For Each Cell In Range("Week_sales")

If Not IsNumeric(Cell) Then

MsgBox "Please enter a number in cell " &Cell.Address

With Cell.Interior

.ColorIndex = 3

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

ElseIf (Cell < 0) Or (Cell > 100) Then

MsgBox "Please enter number between 0 & 100 in cell "

& Cell.Address

With Cell.Interior

.ColorIndex = 3

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

Next

End Sub

0 0

Post a comment