auto enable macros

[Gordon]

Hi..

Going crazy here!

I know there is now way to automate enable macros when loading an excel file
but I've heard that you can use code to leave an introduction page and only
an introduction page and nothing else if macros aren't enabled.

Has anyone seen any code or advice on this? I tried some code that used a
workbook_open macro but I use long auto_run and auto_close code below. So
what I need needs to fit in or work alongside the code below?

Private Sub auto_open()
CommandBars("Worksheet Menu Bar").Enabled = False
Application.DisplayFullScreen = False
Sheets("SETUP").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveWindow.Caption = Sheets("SETUP").Range("J6")
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
Application.DisplayStatusBar = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayWorkbookTabs = True
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Chart").Visible = False
Application.CommandBars("Forms").Visible = False
Application.CommandBars("Web").Visible = False
Application.CommandBars("Reviewing").Visible = False
Application.CommandBars("Visual Basic").Visible = False
Application.CommandBars("Drawing").Visible = False
Application.CommandBars("web").Visible = False
Application.CommandBars("Picture").Visible = False
Application.CommandBars("PivotTable").Visible = False
Application.CommandBars("CELL").Enabled = False
Application.CommandBars("Visual Basic").Enabled = False
MenuBars(xlWorksheet).Menus("Data").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Edit").Enabled = True
MenuBars(xlWorksheet).Menus("Format").Enabled = True
MenuBars(xlWorksheet).Menus("Insert").Enabled = True
MenuBars(xlWorksheet).Menus("Window").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Tools").Enabled = True
MenuBars(xlWorksheet).Menus("View").Enabled = True
Application.CommandBars("Ply").Enabled = False
End Sub
Sub auto_close()
CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
MenuBars(xlWorksheet).Menus("Data").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Edit").Enabled = True
MenuBars(xlWorksheet).Menus("Format").Enabled = True
MenuBars(xlWorksheet).Menus("Insert").Enabled = True
MenuBars(xlWorksheet).Menus("Window").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Tools").Enabled = True
MenuBars(xlWorksheet).Menus("View").Enabled = True
Application.CommandBars("CELL").Enabled = True
Application.CommandBars("Ply").Enabled = True
Sheets("SETUP").Select
Range("A1").Select
Application.DisplayFullScreen = False
ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.Quit

End Sub

Any ideas?

 

[Joel]

You are using function names from excel 97. You should change the first line
of code as shown below. the macros have to be place in the VBA sheet
Thisworkbook. In VBA menu go to View - Project Explorer and double click
THISWORKBOOK. Place both macros on this page. Change first line.

from
Private Sub auto_open()
to
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)


from
Sub auto_close()
to
Private Sub Workbook_BeforeClose(Cancel as Boolean)

 

[Gordon]

Hi Joel...

I did what you said but when I open my file everything that used to happen
within the auto_run now doesn't happen at all.

When I close down the workbook_close macro crashes and none of the code that
was origianlly in the auto_close doesn't fire.

You can see why I've kept with auto_run for so long. I want to move over to
workbook stuff but I don't undertand how it works.

Any tips or advice would be welcome!!!!

Cheers

G

 

[Joel]

I singled step through the code and it worked fine in Excel 2003. I simply
commented out the parameter list to be able to step through the code. Only
event subs can have parameter lists. cheange the code like I did below.
Then Press the Sub line with the mouse and Press F8. Keep pressing F8 until
the code fails. Then repeat with the close macro.

I would also change a VBA setting to break on ALL errors

Tools - Options - General - Break on All Errors

I think the open code sets up the menu the way the defaults are set in excel
2003 so you may not see any changes. The close macro saves the file and
there may be a problem with the saving. Good Luck!


Private Sub App_WorkbookOpen() '(ByVal Wb As Workbook)

Private Sub Workbook_BeforeClose() '(Cancel As Boolean)

 

[Chip Pearson]

See http://www.cpearson.com/Excel/EnableMacros.aspx and
http://www.cpearson.com/Excel/EnableMacros2.aspx

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[Gordon]

Hi Chris...

Thanks for this. I understand the principals but I'm dammed if I can
dovetail it into my file. I've spent 3 days trying to get this right. I came
across these links 2 days ago.

Best option: Why don't I pay you £50 by paypal right now and send you the
file I need fixing? You spend 10 minutes on it and I'm a happy bunny!

Feel free to respond on (e-mail address removed)

Cheers

G