Toggle calculation
 

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?

Toggle calculation
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"FJDx" wrote in message ...
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?

Toggle calculation
 

"Ron de Bruin" wrote in message
...
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.

Toggle calculation
 

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/microsof...ic.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsof...heet.functions
news://msnews.microsoft.com/microsof...excel.newusers
news://msnews.microsoft.com/microsof...el.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsof....docmanagement
news://msnews.microsoft.com/microsof...word.word97vba
news://msnews.microsoft.com/microsof....word.newusers
news://msnews.microsoft.com/microsof...ord.pagelayout
news://msnews.microsoft.com/microsof...ord.vba.addins
news://msnews.microsoft.com/microsof....vba.beginners
news://msnews.microsoft.com/microsof....customization
news://msnews.microsoft.com/microsof...rd.vba.general
news://msnews.microsoft.com/microsof....vba.userforms
news://msnews.microsoft.com/microsof....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_tut...e_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_gr...Excel*&num=100

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



FJDx wrote:

"Ron de Bruin" wrote in message
...
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