Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |