Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > auto enable macros
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 211
Default auto enable macros
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?


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default auto enable macros
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" wrote:

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?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 211
Default auto enable macros
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" wrote:

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" wrote:

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?


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default auto enable macros
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)

"Gordon" wrote:

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" wrote:

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" wrote:

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?


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default auto enable macros

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)


On Thu, 25 Sep 2008 09:29:00 -0700, Gordon
wrote:

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?

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
choose default macros Not Enabled / Macros Enable Setting BEEJAY Excel Programming 2 June 30th 06 01:07 PM
VBA select default Don't Enable / Enable macros Setting BEEJAY Excel Programming 1 June 29th 06 08:45 PM
Enable macros? Steve Excel Worksheet Functions 2 October 14th 05 04:17 PM
macros enable myfanwy New Users to Excel 2 February 6th 05 10:01 PM
Suppress the Disable Macros / Enable Macros Dialog Shoji Karai Excel Programming 5 September 24th 03 03:10 AM


All times are GMT +1. The time now is 11:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement