Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to stop data entry errors
Hi,
I'm using excel 2000 on Windows 2000 professional Sample Spreadsheet named QuotedPart __________________________________________________ ___ Column Row A B C D E 6 Label 1 Shell Entry 7 Data 1 10 08 $15.00 8 Blank 9 Subtotal $15.00 10 Blank 11 Label 2 12 Data 2 0.00 13 Blank 14 Subtotal 0.00 15 Blank __________________________________________________ Data 1 and 2 are populated by lookup formulas. They provide prompts for data entry. If the formula in data1 returns a value and the agent enters the shell and entry sizes. Cell E7 returns an amount. If the agent neglects entering the shell and entry sizes, the amound in E7 remains $0.00, and the quote in too low. I want to present a message if E7 is $0.00, and delay printing the quote until the agent enters the appropriate values. I would like to apply this check to a named range, FormulaCriteria, that describes a range of 8 cells: Cell Name Msgbox message if Column E is $0.00: This quote does not. . . Data 1: QuotedPart!$A$7 . . .contain a core part price Data 2: QuotedPart!$A$13 . . .contain an entry adder price Data 3: QuotedPart!$A$23 . . .contain a clamp price Data 4: QuotedPart!$A$28 . . .contain a chain price Data 5: QuotedPart!$A$38 . . .contain a mod code price Data 6: QuotedPart!$A$43 . . .contain a 2-piece price Data 7: QuotedPart!$A$48 . . .contain a band price Data 8: QuotedPart!$A$62 . . .contain a self-lock price I've got the following macro that works on one cell, E7. Private Sub cmdPrint_Click() Dim rng As Range Dim myval As Long 'Set rng = Range("FormulaCriteria") myval = Sheet6.Range("e7").Value If myval 1 Then Hide_Print Else MsgBox "This quote does not contain a core part price", vbAbortRetryIgnore, "Core Part Error" End If End Sub I want to loop through the cells in range, FormulaCriteria, and check if corresponding cell in column E contains a value greater than 0. I haven't figured out how to write the loop statement. If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
stop entry of data that causes a negative value from a formula | Excel Discussion (Misc queries) | |||
Macro to Audit Two Columns of Data for Errors | Excel Programming | |||
Data Validation: stop blank entry | Excel Discussion (Misc queries) | |||
How do I stop sharing violation errors in Excel 2003 - file NOT sh | Excel Discussion (Misc queries) |