Worksheet_Calculate Question

I am very new to VBA, so I appreciate any help on this one. I am using a
worksheet_calculate event to trigger a msg box if a user enters a value that
exceeds a limit on a sheet.

Basically, a user enters a value on an input sheet. then, some calcs are
performed and a title cell either stays the same, or changes to over. on
another sheet, i have a formula to count all the instances of the word over,
and then use that value to trigger some other msgs.

With the worksheet_calculate, the msg box appears as i want it to, but the
user has already entered the value that causes the overage and hit enter or
tab and has moved to the next cell. How can i make the worksheet_calculate
move the cursor back to the previous cell and highlight the incorrect entry
so that it has to be corrected?

Here is the code I have so far:

Private Sub Worksheet_Calculate()

Dim Over As Range
Dim msb As Integer

Application.EnableEvents = False

For Each Over In Range("W37")
If Over.Value > 0 Then
msb = MsgBox("You have entered a value which exceeds the maximum
limit. Please try again.")
ElseIf Over.Value = 0 Then Exit Sub
End If

Application.EnableEvents = True

Next

End Sub

Any suggestions would be appreciated.

could you use Application.Undo to remove the data that was input?

"Volsfan" wrote:

> I am very new to VBA, so I appreciate any help on this one. I am using a
> worksheet_calculate event to trigger a msg box if a user enters a value that
> exceeds a limit on a sheet.
>
> Basically, a user enters a value on an input sheet. then, some calcs are
> performed and a title cell either stays the same, or changes to over. on
> another sheet, i have a formula to count all the instances of the word over,
> and then use that value to trigger some other msgs.
>
> With the worksheet_calculate, the msg box appears as i want it to, but the
> user has already entered the value that causes the overage and hit enter or
> tab and has moved to the next cell. How can i make the worksheet_calculate
> move the cursor back to the previous cell and highlight the incorrect entry
> so that it has to be corrected?
>
> Here is the code I have so far:
>
> Private Sub Worksheet_Calculate()
>
> Dim Over As Range
> Dim msb As Integer
>
> Application.EnableEvents = False
>
> For Each Over In Range("W37")
> If Over.Value > 0 Then
> msb = MsgBox("You have entered a value which exceeds the maximum
> limit. Please try again.")
> ElseIf Over.Value = 0 Then Exit Sub
> End If
>
> Application.EnableEvents = True
>
> Next
>
> End Sub
>
> Any suggestions would be appreciated.
>
>

Use the worksheet_selectionchange event to trap any selection and save that
in a module scope variable, and then when you get the error, just pick up
that variable and activate that cell.

Option Explicit

Private oCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set oCell = Target
End Sub

Private Sub Worksheet_Calculate()
Dim Over As Range
Dim msb As Integer

Application.EnableEvents = False

For Each Over In Range("W37")
If Over.Value > 0 Then
msb = MsgBox("You have entered a value which " & _
"exceeds the maximum limit. Please try again.")
oCell.Activate
ElseIf Over.Value = 0 Then Exit Sub
End If
Next

Application.EnableEvents = True

End Sub

--
HTH

Bob Phillips

"Volsfan" <[email protected]> wrote in message
news:[email protected]...
> I am very new to VBA, so I appreciate any help on this one. I am using a
> worksheet_calculate event to trigger a msg box if a user enters a value
that
> exceeds a limit on a sheet.
>
> Basically, a user enters a value on an input sheet. then, some calcs are
> performed and a title cell either stays the same, or changes to over. on
> another sheet, i have a formula to count all the instances of the word
over,
> and then use that value to trigger some other msgs.
>
> With the worksheet_calculate, the msg box appears as i want it to, but the
> user has already entered the value that causes the overage and hit enter
or
> tab and has moved to the next cell. How can i make the worksheet_calculate
> move the cursor back to the previous cell and highlight the incorrect
entry
> so that it has to be corrected?
>
> Here is the code I have so far:
>
> Private Sub Worksheet_Calculate()
>
> Dim Over As Range
> Dim msb As Integer
>
> Application.EnableEvents = False
>
> For Each Over In Range("W37")
> If Over.Value > 0 Then
> msb = MsgBox("You have entered a value which exceeds the maximum
> limit. Please try again.")
> ElseIf Over.Value = 0 Then Exit Sub
> End If
>
> Application.EnableEvents = True
>
> Next
>
> End Sub
>
> Any suggestions would be appreciated.
>
>