Where am I going wrong

[libby]

I have a spreadsheet which is updated by the user clicking
a button on the sheet. I don't want them to be able to
close the workbook without them updating, so I've disabled
the x.
However, on one sheet it gives them the option of opening
another workbook, but when they do this my code doesn't
work.

public ConTrolClose

Private Sub Workbook_Open()
ConTrolClose = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Select Case ConTrolClose
Case True
Cancel = True 'cant close workbook
Case False
Cancel = False ' can close workbook
End Select
End Sub

Private Sub cmdSheet1_Click()
Range("i1") = Time
ThisWorkbook.Save
UserForm1.Show
End Sub

Private Sub cmdSheet2_Click()
Range("i1") = Time
ThisWorkbook.Save
UserForm1.Show
End Sub

Private Sub cmdCloseWorkbook_Click()
ConTrolClose = False
Select Case ActiveSheet.Name
Case "Sheet1"
If MsgBox("Do you need to open Data", vbYesNo) = vbYes Then
Workbooks.Open "C:\Windows\Desktop\Data.xls", , , , "lrp"
If ActiveWorkbook.ReadOnly = True Then
MsgBox ("You cannot update at this time")
Application.Quit
Else
ThisWorkbook.Close
End If
End If
Case Else
Application.Quit
End Select
End Sub


Private Sub cmdReturntosheet_Click()
Unload Me
ConTrolClose = True
End Sub

The problem is with cmdCloseWorkbook
If Sheet1 is active then clicking this should give you the
option of either quitting or opening the workbook Data and
closing this one. It opens Data but doesn't close.

Any help much apprecitated

Libby

 

[BrianB]

Perhaps something like this would be easier to handle :-
'------------------------------------------------------
rsp = MsgBox("Do you need to open Data", vbYesNoCancel)
Select Case rsp
Case vbYes
'do something
Case vbNo
'do something else
Case vbCancel
'do something else
End Select
End Sub
'-----------------------------------------------------