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.

Worksheet_Calculate Question
 

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.

Worksheet_Calculate Question
 

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" wrote in message
...
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.