Hi, I wrote this code to give selective access to tabs based on passwords inputed into the messagebox. For some reason, after I inserted the application.screenupdating the code crashes Excel everytime I close the file or try ot save/not save the file. In short, the on_close sub is going kaput...
This is the entire code...can you please help? (btw, opening the workbook is not a problem)
Code:
Private Sub Workbook_Open()
Worksheets("Cover Page").Visible = True
Worksheets("Control Sheet").Visible = False
Worksheets("Channel Summary").Visible = False
Worksheets("Club Drug").Visible = False
Worksheets("Grocery").Visible = False
Worksheets("Mass-Spec & Wholesale").Visible = False
Worksheets("Mass & Specialty").Visible = False
Worksheets("Wholesale").Visible = False
Worksheets("Quebec").Visible = False
Worksheets("Input Data").Visible = False
Worksheets("2008 Target").Visible = False
Call GetInput
End Sub
Sub GetInput()
Dim Password As String
Worksheets("Cover Page").Visible = True
Worksheets("Control Sheet").Visible = False
Worksheets("Channel Summary").Visible = False
Worksheets("Club Drug").Visible = False
Worksheets("Grocery").Visible = False
Worksheets("Mass-Spec & Wholesale").Visible = False
Worksheets("Mass & Specialty").Visible = False
Worksheets("Wholesale").Visible = False
Worksheets("Quebec").Visible = False
Worksheets("Input Data").Visible = False
Worksheets("2008 Target").Visible = False
Application.ScreenUpdating = True
Password = Application.InputBox(prompt:="Enter your Channel password", Type:=2)
If Password = "xyz" Then
Application.ScreenUpdating = False
Worksheets("Mass & Specialty").Visible = True
Worksheets("Cover Page").Visible = True
Worksheets("Mass & Specialty").Activate
ElseIf Password = "abc" Then
Application.ScreenUpdating = False
Worksheets("Grocery").Visible = True
Worksheets("Cover Page").Visible = True
Worksheets("Grocery").Activate
ElseIf Password = "def" Then
Application.ScreenUpdating = False
Worksheets("Wholesale").Visible = True
Worksheets("Cover Page").Visible = True
Worksheets("Wholesale").Activate
ElseIf Password = "ghi" Then
Application.ScreenUpdating = False
Worksheets("Club Drug").Visible = True
Worksheets("Cover Page").Visible = True
Worksheets("Club Drug").Activate
ElseIf Password = "jkl" Then
Application.ScreenUpdating = False
Worksheets("Quebec").Visible = True
Worksheets("Cover Page").Visible = True
Worksheets("Quebec").Activate
ElseIf Password = "mno" Then
Application.ScreenUpdating = False
Worksheets("Cover Page").Visible = True
Worksheets("Mass & Specialty").Visible = True
Worksheets("Wholesale").Visible = True
Worksheets("Mass-Spec & Wholesale").Visible = True
Worksheets("Mass-Spec & Wholesale").Activate
ElseIf Password = "pqr" Then
Application.ScreenUpdating = False
Worksheets("Mass & Specialty").Visible = True
Worksheets("Grocery").Visible = True
Worksheets("Club Drug").Visible = True
Worksheets("Wholesale").Visible = True
Worksheets("Quebec").Visible = True
Worksheets("Channel Summary").Visible = True
Worksheets("Input Data").Visible = True
Worksheets("Mass-Spec & Wholesale").Visible = True
Worksheets("2008 Target").Visible = True
Worksheets("Cover Page").Visible = True
Worksheets("Control Sheet").Visible = True
ElseIf Password = "stu" Then
Application.ScreenUpdating = False
Worksheets("Mass & Specialty").Visible = True
Worksheets("Grocery").Visible = True
Worksheets("Club Drug").Visible = True
Worksheets("Wholesale").Visible = True
Worksheets("Quebec").Visible = True
Worksheets("Channel Summary").Visible = True
Worksheets("Input Data").Visible = False
Worksheets("Mass-Spec & Wholesale").Visible = False
Worksheets("Cover Page").Visible = False
Worksheets("2008 Target").Visible = False
Worksheets("Control Sheet").Visible = False
Worksheets("Channel Summary").Activate
Else
Application.ScreenUpdating = False
MsgBox ("Incorrect Password")
Worksheets("Channel Summary").Visible = False
Worksheets("Club Drug").Visible = False
Worksheets("Grocery").Visible = False
Worksheets("Mass-Spec & Wholesale").Visible = False
Worksheets("Mass & Specialty").Visible = False
Worksheets("Wholesale").Visible = False
Worksheets("Quebec").Visible = False
Worksheets("Input Data").Visible = False
Worksheets("2008 Target").Visible = False
Worksheets("Control Sheet").Visible = False
Worksheets("Cover Page").Visible = True
End If
End Sub
Private Sub Workbook_beforeClose(cancel As Boolean)
If ActiveWorkbook.Saved = True Then
ActiveWorkbook.Close
Exit Sub
Else
Dim Msg As String
Msg = "Do you want to save "
Msg = Msg & ActiveWorkbook.Name & " before closing" & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Worksheets("Cover Page").Visible = True
Worksheets("Channel Summary").Visible = False
Worksheets("Club Drug").Visible = False
Worksheets("Grocery").Visible = False
Worksheets("Mass-Spec & Wholesale").Visible = False
Worksheets("Mass & Specialty").Visible = False
Worksheets("Wholesale").Visible = False
Worksheets("Quebec").Visible = False
Worksheets("Input Data").Visible = False
Worksheets("2008 Target").Visible = False
Worksheets("Control Sheet").Visible = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Case vbNo
Worksheets("Cover Page").Visible = True
Worksheets("Channel Summary").Visible = False
Worksheets("Club Drug").Visible = False
Worksheets("Grocery").Visible = False
Worksheets("Mass-Spec & Wholesale").Visible = False
Worksheets("Mass & Specialty").Visible = False
Worksheets("Wholesale").Visible = False
Worksheets("Quebec").Visible = False
Worksheets("Input Data").Visible = False
Worksheets("2008 Target").Visible = False
Worksheets("Control Sheet").Visible = False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Workbooks("Personal.xlsb").Saved = True
Workbooks("Personal.xlsb").Close
On Error GoTo 0
Case vbCancel
cancel = True
End Select
End If
End Sub