Toggle calculation

[FJDx]

I have assigned the following macro to a button on my Excel 2002
toolbar:

Sub ToggleCalcMode()
Dim nState As Long
Dim sMode As String
With Application
If .Calculation = xlAutomatic Then
.Calculation = xlManual
nState = msoButtonDown
sMode = "Manual"
Caption = "Manual"
Else
.Calculation = xlAutomatic
nState = msoButtonUp
sMode = "Automatic"
Caption = "Auto"
End If
End With
End Sub

I selected an icon from 'Change button image'. How can I change the
picture of the toolbar button depending on whether I have it set to
Automatic or Manual?

 

[Ron de Bruin]

Hi

Dave Peterson give you this answer (12 July)

I've never had good luck keeping these in sync--if I change the calculation via
tools|options, then my toolbar doesn't get updated.

But this may work for you:

Option Explicit

Sub ToggleCalcMode()
Dim nState As Long
Dim sMode As String
Dim myCaption As String
Dim myFaceID As Long
With Application
If .Calculation = xlAutomatic Then
.Calculation = xlManual
nState = msoButtonDown
sMode = "Manual"
myCaption = "Manual"
myFaceID = 100
Else
.Calculation = xlAutomatic
nState = msoButtonUp
sMode = "Automatic"
myCaption = "Auto"
myFaceID = 101
End If
With .CommandBars.ActionControl
.Style = msoButtonIconAndCaption 'should have been set already
.Caption = myCaption
.State = nState
.FaceId = myFaceID
End With
End With
End Sub

And Jim Rech has shared a nice program that will show faceid's:

http://www.BMSLtd.ie/MVP/Default.htm
Look for Jim Rech's:
BtnFaces.zip

 

[FJDx]

Hi

Dave Peterson give you this answer (12 July)

I've never had good luck keeping these in sync--if I change the calculation via
tools|options, then my toolbar doesn't get updated.

Thanks for your help. I didn't get Dave Peterson's reply. Must have been
a problem with the newserver as I was watching the thread.

 

[Dave Peterson]

You may want to connect directly to the MS Newsservers.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm