Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
Am using Excel 2003. Want to stop users exiting or saving my spreadsheet
unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
Hi Adam,
You could use the BeforeSave and BeforeClose events of ThisWorkbook, e.g.: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = (Me.Sheets(1).Cells(1, 1) = _ Me.Sheets(2).Cells(1, 1)) End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Cancel = (Me.Sheets(1).Cells(1, 1) = _ Me.Sheets(2).Cells(1, 1)) End Sub Regards, KL "Adam Harding" wrote in message ... Am using Excel 2003. Want to stop users exiting or saving my spreadsheet unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
Hi Adam,
In the workbook's ThisWorkbook module (not in a standard module) paste something like: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Sheets(1).Range("A1").Value < _ Sheets(2).Range("A1").Value Then Cancel = True MsgBox "Your meaage to user" End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Sheets(1).Range("A1").Value < _ Sheets(2).Range("A1").Value Then Cancel = True MsgBox "Your meaage to user" End If End Sub Change the condition to suit your requirements and change the msgbox to something as inoffensively informative as possible. --- Regards, Norman "Adam Harding" wrote in message ... Am using Excel 2003. Want to stop users exiting or saving my spreadsheet unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
opps! change the sign in the equation:
Cancel = (Me.Sheets(1).Cells(1, 1) < _ Me.Sheets(2).Cells(1, 1)) KL "KL" wrote in message ... Hi Adam, You could use the BeforeSave and BeforeClose events of ThisWorkbook, e.g.: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = (Me.Sheets(1).Cells(1, 1) = _ Me.Sheets(2).Cells(1, 1)) End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Cancel = (Me.Sheets(1).Cells(1, 1) = _ Me.Sheets(2).Cells(1, 1)) End Sub Regards, KL "Adam Harding" wrote in message ... Am using Excel 2003. Want to stop users exiting or saving my spreadsheet unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
Thanks looks good i shall try it out and report back
"KL" wrote: Hi Adam, You could use the BeforeSave and BeforeClose events of ThisWorkbook, e.g.: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = (Me.Sheets(1).Cells(1, 1) = _ Me.Sheets(2).Cells(1, 1)) End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Cancel = (Me.Sheets(1).Cells(1, 1) = _ Me.Sheets(2).Cells(1, 1)) End Sub Regards, KL "Adam Harding" wrote in message ... Am using Excel 2003. Want to stop users exiting or saving my spreadsheet unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
Tried all of your suggestions and they don't alter the behaviour of hte form?
Any ideas? "Norman Jones" wrote: Hi Adam, In the workbook's ThisWorkbook module (not in a standard module) paste something like: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Sheets(1).Range("A1").Value < _ Sheets(2).Range("A1").Value Then Cancel = True MsgBox "Your meaage to user" End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Sheets(1).Range("A1").Value < _ Sheets(2).Range("A1").Value Then Cancel = True MsgBox "Your meaage to user" End If End Sub Change the condition to suit your requirements and change the msgbox to something as inoffensively informative as possible. --- Regards, Norman "Adam Harding" wrote in message ... Am using Excel 2003. Want to stop users exiting or saving my spreadsheet unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Closing and Saving
Hi Adam,
Have you changed the sheet ID's in Norman's or my code to the relevant ones? E.g. Sheets(1) to Sheets("Sheet23") Regards, KL "Adam Harding" wrote in message ... Tried all of your suggestions and they don't alter the behaviour of hte form? Any ideas? "Norman Jones" wrote: Hi Adam, In the workbook's ThisWorkbook module (not in a standard module) paste something like: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Sheets(1).Range("A1").Value < _ Sheets(2).Range("A1").Value Then Cancel = True MsgBox "Your meaage to user" End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Sheets(1).Range("A1").Value < _ Sheets(2).Range("A1").Value Then Cancel = True MsgBox "Your meaage to user" End If End Sub Change the condition to suit your requirements and change the msgbox to something as inoffensively informative as possible. --- Regards, Norman "Adam Harding" wrote in message ... Am using Excel 2003. Want to stop users exiting or saving my spreadsheet unless the total on one sheet = total on other sheet. How can this be done? Any help gratefully received |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving and closing | Setting up and Configuration of Excel | |||
Saving and Closing | Excel Worksheet Functions | |||
Saving and Closing | Excel Discussion (Misc queries) | |||
closing & saving | Excel Worksheet Functions | |||
VBA - closing without saving changes | Excel Programming |