Prevent user from closing excel unless two cells are equal

[Niven]

Hi,

I am creating a timesheet for users to enter their daily working hours as well as an allocation of their total hours worked. The allocation is very important as they work in different projects.

Is there a way I can prevent the workbook from closing and an error message appearing to let them know that their total hours need to equal the hours allocated?

Thanks.
Niven

 

[Niven]

Sorry I tried this but I get an error message and then "blnCancel = Not Sheets("Sheet1").Range("A1").Value" is highlighted in yellow.

 

[nuked]

Sorry I tried this but I get an error message and then "blnCancel = Not Sheets("Sheet1").Range("A1").Value" is highlighted in yellow.

Did you forget to change the sheet name again?

 

[Niven]

The name is Sheet 1 but the yellow highlight still appears. I changed the cell so I thought that was the problem but now I have the correct cell.

 

[Jon von der Heyden]

What's the cell address?
What is displayed in the cell address?
What is the exact code you are using?
What is the error description that shows up?

 

[Niven]

The cell address is C44 and in that cell I have =AND(H24:H31=K24:K31). I did do the Ctrl+Shift+Enter and it came up with the two curly brackets.

The code I am using is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim blnCancel As Boolean
Dim rngCell1 As Excel.Range, rngCell2 As Excel.Range
blnCancel = Not Sheets("Sheet1").Range("C44").Value
If blnCancel Then
Call MsgBox(Prompt:="Have ALL hours worked been allocated to a project?", Title:="Cannot close workbook", Buttons:=vbExclamation + vbOKOnly)
End If
Cancel = blnCancel
End Sub

The error message I get is Run-time error '9' Subscript out of range

 

[Jon von der Heyden]

What result is displayed as a result of that formula (in C44)?

 

[Niven]

Either True or False depending on whether the cells match. I tried it and it changes to True or False

 

[Jon von der Heyden]

And you say it breaks here?

blnCancel = Not Sheets("Sheet1").Range("C44").Value

That doesn't make sense to me. You say your sheet is called "Sheet1" and that C44 contains either TRUE or FALSE. This statement should always only return the inverse of what is in C44.

Sorry; I'm stumped.

 

[Niven]

Yes that line is highlighted in yellow.

 

[Jon von der Heyden]

The error you get is consistent with a misspelled sheet name. I don't suppose you have a space in the name i.e. "Sheet 1"?