Disable Macro Workaround Doesn't Work!

Z

zwestbrook

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
 
Z

zwestbrook

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
Click to expand...

any help? :(
 
C

Chip Pearson

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 said:
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
Click to expand...
 
Z

zwestbrook

What does it do instead of working ?

Tim






- Show quoted text -
Click to expand...

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?
 
T

Tim Williams

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

Tim
 
T

Tim Williams

zwestbrook said:
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 -
Click to expand...


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
 
Z

zwestbrook

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
Click to expand...

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Word macro that can call cells out of Excel for a find and replace 0
Making a macro only work on certain files 2
Hidden sheet bleeds through 3
Change UserName & Password from Worksheet, 0
Can't delete sheets 8
hide menu bar 1
hyperlinking to charts in file 1

Top