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

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

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

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

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

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

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