I am trying to do the disabled macros workaround but can't get it to
work...I cannot figure out what I have done wrong in my code...any
help is appreciated.

[ Workbook Open ]
Private Sub Workbook_Open()

' hide the toolbars
' turn them back on from the 'admin' tab and when closing the workbook
'Dim a As Integer
'For a = 1 To Application.CommandBars.Count
' Application.CommandBars(a).Enabled = False
'Next

' turn back on the standard toolbar to allow saving
Application.CommandBars("Standard").Enabled = True

' show and take the user to the welcome sheet
Worksheets("Welcome").Visible = True
Worksheets("Welcome").Activate

' hide the macro notification sheet
Worksheets("Enable_Macros").Visible = False

'**********************************************************************************
' perform checks on user id and show corresponding sheets
'**********************************************************************************
If Environ("username") = "a499025" Then
Worksheets("Zac").Visible = True
Worksheets("Admin").Visible = True
Else

<snip>

' user is not found, show error sheet and hide the welcome sheet
Worksheets("Error!").Visible = True
Worksheets("Error!").Activate
Worksheets("Welcome").Visible = xlVeryHidden
Dim ErrorMsg
ErrorMsg = MsgBox("I'm Sorry, You Do Not Have Access!" _
, vbOKOnly + vbCritical, "E R R O R ! ! !")
End If
'**********************************************************************************
'end user id checks
'**********************************************************************************

' hide the task pane
Application.CommandBars("Task Pane").Visible = False

' turn off Excel alert messages
Application.DisplayAlerts = False

End Sub

[ Workbook OnBeforeClose ]
Private Sub Workbook_BeforeClose(Cancel As Boolean)

' MsgBox ("save check") 'turn on to check close looping

' check to see if user has saved the file
' and warn them before closing if the document is unsaved
Dim exitMsg
If Not ActiveWorkbook.Saved Then 'if the document has NOT been saved
exitMsg = MsgBox("This workbook contains unsaved changes." _
& Chr(13) & "Do you want to continue without saving?"
_
, vbYesNo + vbCritical + vbDefaultButton2, "W A R N I
N G ! ! !")
If exitMsg = vbNo Then 'user says they don't want to continue
Cancel = True 'cancel closing the document
Exit Sub 'stop running the code
End If
End If

' bring the toolbars back
Dim a As Integer
For a = 1 To Application.CommandBars.Count
Application.CommandBars(a).Enabled = True
Next

' turn on the "Macros" sheet for next user
Worksheets("Enable_Macros").Visible = True
Worksheets("Enable_Macros").Activate

' hide all sheets to prevent next user from seeing them
For Each ws In Worksheets
If ws.Name <> "Enable_Macros" Then ws.Visible = xlVeryHidden
Next

' set the save flag to avoid prompt
ActiveWorkbook.Saved = True
ActiveWorkbook.Close

' ensure Excel alerts are turned on
Application.DisplayAlerts = True

End Sub

On Oct 30, 3:05 pm, zwestbrook <zwestbr...@gmail.com> wrote:
> I am trying to do the disabled macros workaround but can't get it to
> work...I cannot figure out what I have done wrong in my code...any
> help is appreciated.
>
> [ Workbook Open ]
> Private Sub Workbook_Open()
>
> ' hide the toolbars
> ' turn them back on from the 'admin' tab and when closing the workbook
> 'Dim a As Integer
> 'For a = 1 To Application.CommandBars.Count
> ' Application.CommandBars(a).Enabled = False
> 'Next
>
> ' turn back on the standard toolbar to allow saving
> Application.CommandBars("Standard").Enabled = True
>
> ' show and take the user to the welcome sheet
> Worksheets("Welcome").Visible = True
> Worksheets("Welcome").Activate
>
> ' hide the macro notification sheet
> Worksheets("Enable_Macros").Visible = False
>
> '***********************************************************************************
> ' perform checks on user id and show corresponding sheets
> '***********************************************************************************
> If Environ("username") = "a499025" Then
> Worksheets("Zac").Visible = True
> Worksheets("Admin").Visible = True
> Else
>
> <snip>
>
> ' user is not found, show error sheet and hide the welcome sheet
> Worksheets("Error!").Visible = True
> Worksheets("Error!").Activate
> Worksheets("Welcome").Visible = xlVeryHidden
> Dim ErrorMsg
> ErrorMsg = MsgBox("I'm Sorry, You Do Not Have Access!" _
> , vbOKOnly + vbCritical, "E R R O R ! ! !")
> End If
> '***********************************************************************************
> 'end user id checks
> '***********************************************************************************
>
> ' hide the task pane
> Application.CommandBars("Task Pane").Visible = False
>
> ' turn off Excel alert messages
> Application.DisplayAlerts = False
>
> End Sub
>
> [ Workbook OnBeforeClose ]
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> ' MsgBox ("save check") 'turn on to check close looping
>
> ' check to see if user has saved the file
> ' and warn them before closing if the document is unsaved
> Dim exitMsg
> If Not ActiveWorkbook.Saved Then 'if the document has NOT been saved
> exitMsg = MsgBox("This workbook contains unsaved changes." _
> & Chr(13) & "Do you want to continue without saving?"
> _
> , vbYesNo + vbCritical + vbDefaultButton2, "W A R N I
> N G ! ! !")
> If exitMsg = vbNo Then 'user says they don't want to continue
> Cancel = True 'cancel closing the document
> Exit Sub 'stop running the code
> End If
> End If
>
> ' bring the toolbars back
> Dim a As Integer
> For a = 1 To Application.CommandBars.Count
> Application.CommandBars(a).Enabled = True
> Next
>
> ' turn on the "Macros" sheet for next user
> Worksheets("Enable_Macros").Visible = True
> Worksheets("Enable_Macros").Activate
>
> ' hide all sheets to prevent next user from seeing them
> For Each ws In Worksheets
> If ws.Name <> "Enable_Macros" Then ws.Visible = xlVeryHidden
> Next
>
> ' set the save flag to avoid prompt
> ActiveWorkbook.Saved = True
> ActiveWorkbook.Close
>
> ' ensure Excel alerts are turned on
> Application.DisplayAlerts = True
>
> End Sub

any help?

bump

hoping...

praying...

What does it do instead of working ?

Tim

"zwestbrook" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> bump
>
> hoping...
>
> praying...
>
>

Rather than just say "it doesn't work", you should tell what it does do that
it shouldn't or what it doesn't do that it should.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"zwestbrook" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am trying to do the disabled macros workaround but can't get it to
> work...I cannot figure out what I have done wrong in my code...any
> help is appreciated.
>
> [ Workbook Open ]
> Private Sub Workbook_Open()
>
> ' hide the toolbars
> ' turn them back on from the 'admin' tab and when closing the workbook
> 'Dim a As Integer
> 'For a = 1 To Application.CommandBars.Count
> ' Application.CommandBars(a).Enabled = False
> 'Next
>
> ' turn back on the standard toolbar to allow saving
> Application.CommandBars("Standard").Enabled = True
>
> ' show and take the user to the welcome sheet
> Worksheets("Welcome").Visible = True
> Worksheets("Welcome").Activate
>
> ' hide the macro notification sheet
> Worksheets("Enable_Macros").Visible = False
>
> '**********************************************************************************
> ' perform checks on user id and show corresponding sheets
> '**********************************************************************************
> If Environ("username") = "a499025" Then
> Worksheets("Zac").Visible = True
> Worksheets("Admin").Visible = True
> Else
>
> <snip>
>
> ' user is not found, show error sheet and hide the welcome sheet
> Worksheets("Error!").Visible = True
> Worksheets("Error!").Activate
> Worksheets("Welcome").Visible = xlVeryHidden
> Dim ErrorMsg
> ErrorMsg = MsgBox("I'm Sorry, You Do Not Have Access!" _
> , vbOKOnly + vbCritical, "E R R O R ! ! !")
> End If
> '**********************************************************************************
> 'end user id checks
> '**********************************************************************************
>
> ' hide the task pane
> Application.CommandBars("Task Pane").Visible = False
>
> ' turn off Excel alert messages
> Application.DisplayAlerts = False
>
> End Sub
>
> [ Workbook OnBeforeClose ]
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> ' MsgBox ("save check") 'turn on to check close looping
>
> ' check to see if user has saved the file
> ' and warn them before closing if the document is unsaved
> Dim exitMsg
> If Not ActiveWorkbook.Saved Then 'if the document has NOT been saved
> exitMsg = MsgBox("This workbook contains unsaved changes." _
> & Chr(13) & "Do you want to continue without saving?"
> _
> , vbYesNo + vbCritical + vbDefaultButton2, "W A R N I
> N G ! ! !")
> If exitMsg = vbNo Then 'user says they don't want to continue
> Cancel = True 'cancel closing the document
> Exit Sub 'stop running the code
> End If
> End If
>
> ' bring the toolbars back
> Dim a As Integer
> For a = 1 To Application.CommandBars.Count
> Application.CommandBars(a).Enabled = True
> Next
>
> ' turn on the "Macros" sheet for next user
> Worksheets("Enable_Macros").Visible = True
> Worksheets("Enable_Macros").Activate
>
> ' hide all sheets to prevent next user from seeing them
> For Each ws In Worksheets
> If ws.Name <> "Enable_Macros" Then ws.Visible = xlVeryHidden
> Next
>
> ' set the save flag to avoid prompt
> ActiveWorkbook.Saved = True
> ActiveWorkbook.Close
>
> ' ensure Excel alerts are turned on
> Application.DisplayAlerts = True
>
> End Sub
>

On Nov 2, 10:55 pm, "Tim Williams" <timjwilliams at gmail dot com>
wrote:
> What does it do instead of working ?
>
> Tim
>
> "zwestbrook" <zwestbr...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > bump
>
> > hoping...
>
> > praying...- Hide quoted text -
>
> - Show quoted text -

Thanks Tim and Chip...I've been under a rock the past few days and
haven't been checking this posting.

Instead of displaying the "please enable macros" sheet when clicking
"disable macros" it displays whatever sheets the last user had access
to. Does that make sense?

You're making changes to visible sheets etc and then *not* saving the file
before it closes...

Tim

"zwestbrook" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Nov 2, 10:55 pm, "Tim Williams" <timjwilliams at gmail dot com>
> wrote:
>> What does it do instead of working ?
>>
>> Tim
>>
>> "zwestbrook" <zwestbr...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > bump
>>
>> > hoping...
>>
>> > praying...- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Tim and Chip...I've been under a rock the past few days and
> haven't been checking this posting.
>
> Instead of displaying the "please enable macros" sheet when clicking
> "disable macros" it displays whatever sheets the last user had access
> to. Does that make sense?
>

> > "zwestbrook" <zwestbr...@gmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
>
> Instead of displaying the "please enable macros" sheet when clicking
> "disable macros" it displays whatever sheets the last user had access
> to. Does that make sense?- Hide quoted text -
>
> - Show quoted text -


You're making the changes to sheet visibility etc, and then *not
saving* the workbook before it closes.
You even set the Saved flag to ensure the user isn't prompted to save:
instead you should simply save the workbook before closing.

Tim

On Nov 7, 1:26 am, Tim Williams <timjwilli...@gmail.com> wrote:
> > > "zwestbrook" <zwestbr...@gmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > Instead of displaying the "please enable macros" sheet when clicking
> > "disable macros" it displays whatever sheets the last user had access
> > to. Does that make sense?- Hide quoted text -
>
> > - Show quoted text -
>
> You're making the changes to sheet visibility etc, and then *not
> saving* the workbook before it closes.
> You even set the Saved flag to ensure the user isn't prompted to save:
> instead you should simply save the workbook before closing.
>
> Tim

Thanks for your feedback...I was worried that I had stepped over
something as the complexity grew. I'll revisit that part and hopefully
will get this working with your help. Thanks!