Re: worksheet_calculation problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Just reading trough your first code I can't see anything there that
would cause this prompt to save. I would try a few things. Firstly how
are you calling the code? Is it from a button? Check that code. Try
commenting all your code lines to cancel the code out entirely then
close and re-open the sheet. Does it still happen? You should be able
to pinpoint why this is happening and fix it by ruling all other
possible causes out.
Nasim wrote:
Hi,
I have a sheet(1 )on which I want to check the postal code as soon as
the user enters the code. then on sheet(4) I have several formulas in
several cells to remove space and "-" and so on (last check is in A4)
then I have another formula in B5 to check if A4 is correct (returns
True or False) . then I have a worksheet_calculate event for sheet(4)
as follow:

Private Sub Worksheet_Calculate()
'check if a4 has 6 characters:
If Len(Range("a4").Value) <> 6 Then
MsgBox "Please enter correct postal code"
Else
'if the code itself is not correct
If Range("b5").Value <> True Then
MsgBox "Please enter correct postal code"
End If
End If
End Sub

It totally works but the problem is that whenever I open the saved file
and do absolutely nothing it asks me if I want to save changes.
I tried these things and the problem is still there:
1- in workbook_open put thisworkbook.saved= true
then changes code for sheet(3) as follow:

Private Sub Worksheet_Calculate()
If ThisWorkbook.Saved = True Then
Application.Calculate = xlCalculationManual
Else
Application.Calculate = XlCalculation.xlCalculationAutomatic
If Len(Range("a4").Value) <> 6 Then
MsgBox "Please enter correct postal code"
Else
If Range("b5").Value <> True Then
MsgBox "Please enter correct postal code"
End If
End If
End If
End Sub

and now I get a compile error: Copmile error in hidden module: hseet 4
I am using excell2003 but I am not sure what version the user might
use.
Any idea how to solve this problem?

.

Relevant Pages

  • worksheet_calculation problem
    ... Private Sub Worksheet_Calculate ... MsgBox "Please enter correct postal code" ... then changes code for sheetas follow: ... and now I get a compile error: Copmile error in hidden module: hseet 4 ...
    (microsoft.public.excel.programming)
  • Re: isnumeric compile error...
    ... A97 complains of a compile error. ... Private Sub Text2_AfterUpdate ... MsgBox "Numeric" ... I entered 1.2.2 and I got "Alpha". ...
    (comp.databases.ms-access)
  • Re: Help Im stuck!
    ... Private Sub Form_Open ... and I get a Compile error: ... If that's exactly what you have, then you haven't continuted the MsgBox ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.formscoding)
  • Userform VBA Complie Error: Method or data memeber not found
    ... Compile error: ... Private Sub cmdClose_Click ... If CloseMode = vbFormControlMenu Then ... MsgBox "Please use the button!" ...
    (microsoft.public.excel.programming)
  • Re: Go to Control with IIf statement
    ... Private Sub Form_AfterUpdate ... MsgBox "Must fill in Approved Pantone Number", ... After Update of my combo box and even if I pick other categories, the macro ...
    (microsoft.public.access.formscoding)