Macro assistanced needed

[shapenote]

I am not at all good with writing macros and need some help.

I need to write a macro that will be workbook specific for a shared workbook. I need the macro to shut off the autocalc formula feature so that it is not constantly trying to update the (literal) thousands of formulas in the workbook as people are making changes.

Instead I want them to be able to make all the changes they need free of autocalc and then just hit F9 to calculate then they can save the shared workbook as normal.

is this possible with a macro?

 

[mathchick]

is there a reason you can't just turn off the auto-calculate? If you go to the Formulas Tab of the ribbon and select "Calculation Options" select "Manual."

 

[shapenote]

Yes .. cause then it will turn off the autocalc for all workbooks opened after that one. I want it to be just specific to that workbook so if I have to open any others, their autocal features will still work.

 

[radfordc]

Try putting this in to run as soon as the workbook opens

Application.Calculation = xlManual

 

[radfordc]

Then put
Application.Calculation = xlAutomatic
when closing workbook

 

[shapenote]

Am I putting this coding into VB for the workbook? I am sorry ... macros are not my strong suite especially with 2007.

 

[shapenote]

Here is what the current coding says for this workbook:

Option Explicit

Const MAIN_MENU_BAR = "Worksheet Menu Bar"
Const MENU_BAR_NAME = "&Lawson"
Const SUBMENU_BAR_NAME = "S3 Query Wizard"
Const SUBMENU_BAR_EVENT = "Launch_QueryWizard"

Public Sub Autpen()
Dim subMenu As Object
Dim subMenu2 As Object

On Local Error Resume Next

Set subMenu = CommandBars(MAIN_MENU_BAR).Controls(MENU_BAR_NAME)

If subMenu Is Nothing Then
With CommandBars(MAIN_MENU_BAR)
.Controls.Add(Type:=msoControlPopup, Before:=7).Caption = MENU_BAR_NAME
End With
Set subMenu = CommandBars(MAIN_MENU_BAR).Controls(MENU_BAR_NAME)
End If

Set subMenu2 = subMenu.Controls(SUBMENU_BAR_NAME)

If subMenu2 Is Nothing Then
subMenu.Controls.Add(Type:=msoControlButton).Caption = SUBMENU_BAR_NAME
subMenu.Controls(subMenu.Controls.Count).OnAction = SUBMENU_BAR_EVENT
End If

ThisWorkbook.RefreshAll

End Sub

Public Sub Auto_Close()
Dim subMenu As Object
Dim subMenu2 As Object

On Local Error Resume Next

Set subMenu = CommandBars(MAIN_MENU_BAR).Controls(MENU_BAR_NAME)

If Not (subMenu Is Nothing) Then
If subMenu.Controls.Count > 1 Then
Set subMenu2 = subMenu.Controls(SUBMENU_BAR_NAME)
If Not (subMenu2 Is Nothing) Then
subMenu2.Delete
End If
ElseIf subMenu.Controls.Count = 1 Then
Set subMenu2 = subMenu.Controls(SUBMENU_BAR_NAME)
If Not (subMenu2 Is Nothing) Then
subMenu.Delete
End If
ElseIf subMenu.Controls.Count = 0 Then
subMenu.Delete
End If
End If

End Sub

Sub Launch_QueryWizard()
Dim x As Lawson_QueryWizard.QueryWizard
Set x = New Lawson_QueryWizard.QueryWizard
x.Start
Set x = Nothing
End Sub



Where in the coding would I put to shut off autocalc so I can just manually calc before saving and closing the book while not affecting the autocalc funtion of any other workbooks I may have open?

 

[radfordc]

New procedure needed
see http://www.ozgrid.com/VBA/auto-run-macros.htm