Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fool Proof Price Validation Custom Function
I'd like to test and see if anyone knows a better way to write this function.
The goal of the function is to test if the textboxes on a userform have a valid price in them. Some prices I will or will not allow a negative price, some prices can or can not be zero, and most of all the prices need to be numeric. Does any one have a better way of doing this? I want this fool proof! Sub DataValidation() ' ensure valid price adder If ValidPrice(tbxAdderPrice, True, True) = False Then Exit Sub ' ensure valid unit price If ValidPrice(tbxUnitPrice, False, False) = False Then Exit Sub ' ensure valid crate price If ValidPrice(tbxCratePrice, False, True) = False Then Exit Sub End Sub Function ValidPrice(objControl As Object, bolAllowNegative As Boolean, bolAllowZeros As Boolean) As Boolean ' is called to ensure proper format of a price entered in a textbox. ' can be used for negative/postive prices SubName = "ValidPrice" ' set default to True ValidPrice = True ' test if zero, if zeros are not allowed If Not bolAllowZeros Then If Val(objControl) = 0 Then GoTo InvalidPrice End If ' test if numeric If IsNumeric(objControl) Then ' test if negative, if negatives are not allowed If Not bolAllowNegative Then If Len(objControl) < Len(Replace(objControl, "-", "")) Then GoTo InvalidPrice End If Else If Not bolAllowZeros Then ' if not numeric GoTo InvalidPrice End If End If ' format control objControl = Format(objControl, "#,##0.00") Exit Function InvalidPrice: ValidPrice = False StopCode = True strPrompt = "Problem" intButtons = vbCritical strTitle = "Please enter a valid price for " & objControl.Tag & "." MsgBox strTitle, intButtons, strPrompt End Function -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PRICE function, Pls help | Excel Worksheet Functions | |||
Price Function | Excel Worksheet Functions | |||
Full proof UK postcode validation | Excel Programming | |||
Using custom functions within custom validation | Excel Discussion (Misc queries) | |||
Custom function valid in Data Validation/Conditional Format? | Excel Programming |