Macro to stop data entry errors

[dan dungan]

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

 

[dan dungan]

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

This is what I ended up with. It seems to work.

I'm not sure what stopped folks from responding to my question.

Thanks,

Dan Dungan