worksheet_calculation problem
 

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 the
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?

worksheet_calculation problem
 

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 the
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?

worksheet_calculation problem
 

Hi Keri,

I do save my changes. I have a before save event and I save everything
there. I did what you said and I noticed that now I have 2 broblems.
First is the one I mentioned before (the worksheet_calculete) and now
that I commented them if I open and close the file it is ok but if I
just click in any validation list (without changing anything) it asks
me if I want to save!!! But I haven't changed anything!
Any ideas?

worksheet_calculation problem
 

An update:

I created a new workbook and tried my worksheet_calculation event and
it seems like the broblem in not this event.
The problem is the validation list.
If I open the the file and close it (when no validation list is
selected or clicked in) the file closes as usual. but if the selected
cell is a validation (last saved like that) or if I click in any
validation list (without changing or even opening the list) it asks me
if I want to save changes. If I click Yes it saves the file and asks
again. It goes on and on till I choose No.
It doesn't even give me a chance to click in anohter cell. what can I
do? What I have done wrong that it creates this problem?