making a two position custom toolbar button

  • Thread starter Thread starter slim
  • Start date Start date
S

slim

I'm looking to make a macro that will allow me toggle the calculation mode
between automatic and manual. I can do this easily by writing a very simple
macro and adding two buttons to my toolbar, one for automatic and one for
manual.

Is it possible to make one button that has an on/off position to acheive
this, instead of having to have two sepearte buttons?

Thanks in advance for any help.
 
Try this Slim

If Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = xlCalculationManual
End If
 
Slim,

A slightly different approach from Ron's.

Ron's solution toggles between states, mine gives Manual mode if clicked,
Automatic is shift-clicked, so you decide. It also tells you the current
mode if you hover over it.

Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As Integer

Const vkShift As Integer = &H10

Sub SetCalculateMode() 'Shifted is automatic, unshifted is manual
Dim sMode As String

If GetKeyState(vkShift) < 0 Then
Application.Calculation = xlAutomatic
sMode = "Automatic"
Else
Application.Calculation = xlManual
sMode = "Manual"
End If
Application.CommandBars.ActionControl.TooltipText = "Calculation mode is
" & sMode

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I like that one also Bob

If the OP use a Controltoolbox button it can use the caption property to show
the state also

Private Sub CommandButton1_Click()
If Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
CommandButton1.Caption = "Calc = Auto"
Else
Application.Calculation = xlCalculationManual
CommandButton1.Caption = "Calc = Manual"
End If
End Sub
 
Just to add a bit to Ron's solution - I change the button face when the
button is clicked to indicate "depressed" in manual mode, "up" in
automatic (the faceid = 3785 which looks like the F9 key).

Public Sub ToggleCalcMode()
Dim nState As Long
With Application
If .Calculation = xlAutomatic Then
.Calculation = xlManual
nState = msoButtonDown
Else
.Calculation = xlAutomatic
nState = msoButtonUp
End If
End With
CommandBars("My Workbar").FindControl( _
Tag:="Toggle Calculation Mode").State = nState
End Sub
 
Ron,

Using the caption is also a nice friendly way of feeding information back.

Bob
 
JE McGimpsey said:
Just to add a bit to Ron's solution - I change the button face when the
button is clicked to indicate "depressed" in manual mode, "up" in
automatic
Click to expand...

Another nice touch!
 
All solutions work great. JE, it's getting hung up on this line:
CommandBars("My Workbar").FindControl( _
Tag:="Toggle Calculation Mode").State = nState
Am I missing something?

Is there a way to have the button physical change in appearance...such as
sometype of depressed look when it is turned on ?
 
Slim,

Here's a mod on mine with depressed buttons et al

Sub SetCalculateMode() 'Shifted is automatic, unshifted is manual
Dim sMode As String
Dim nState As Long

If GetKeyState(vkShift) < 0 Then
Application.Calculation = xlAutomatic
sMode = "Automatic"
nState = msoButtonDown
Else
Application.Calculation = xlManual
sMode = "Manual"
nState = msoButtonUp
End If
With Application.CommandBars.ActionControl
.TooltipText = "Calculation mode is " & sMode
.State = nState
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The example I gave was of a button on a toolbar that I use. I modify the
toolbar at run-time, depending on the environment I want, so the button
doesn't have a fixed location. That's why I find the button using the
Tag - when I create the button, I assign a value to the .Tag property
which then allows finding it even if the toolbar's been rearranged.

Bob Phillips gave you an example of how to use the .State property that
I described to change the button's appearance.
 
I took a few things from everyone's comments and came up with the following
macro. It works great except for the behavior of the button status when
first opening a fresh copy of excel. I have the macro set to show have the
button depressed when Manual calculation is on. If I close excel with the
button depressed, when I reopen excel, the button stays depressed, however,
the calculation mode is Automatic.

Here is the macro:

Sub CalcMode()

Dim nState As Long
Dim sMode As String

If Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False
End If

If Application.Calculation = xlCalculationManual Then
nState = msoButtonDown
sMode = "Manual"
Else
nState = msoButtonUp
sMode = "Automatic"
End If

With Application.CommandBars.ActionControl
.State = nState
.TooltipText = "Calculation mode is " & sMode
End With

End Sub


Is there someway for Excel to remember the CalcMode and button status when
excel is closed so when it is reopened it reverts to that status.

Thanks again for everybody's help.
 
Slim,

You could test the button state in the workbook open event and set the mode
accordingly. For this, you need to know the commandbar name and the control
name so as to address it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I apologize for all these newbie questions..I slowly catching on to basic vb
commands.

I assume that you are talking about attempting to initialize the button
status upon opening excel by using Auto_Open() routine or Open _Workbook().

Could someone possibly give me sample code on how to name my command button
in the original macro and also how to check it in the auto_open?

What I would like it to do is when I open excel..I want it to check the
calculation status, and adjust the command button accordingly.

Here is where that macro is at right now:

Sub CalcMode()

Dim nState As Long
Dim sMode As String

If Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
nState = msoButtonUp
sMode = "Automatic"

Else: Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False
nState = msoButtonDown
sMode = "Manual"

End If

With Application.CommandBars.ActionControl
.State = nState
.TooltipText = "Calculation mode is " & sMode
End With

End Sub


Again, I have no problems when exiting the program with the button in the up
position (Automatic)..only when I exit excel with the button down
(Manual)..then when I reopen excel, the button is down but the calculation
status is incorrectly set to Automatic for some reaason.

Thanks again for everybody's help..you guys are great!
 
Slim,

As I said before, you need to know the name of the commandbar that your
button is on, and the name of the button itself. By default, when you add a
button, it is given the name "Custom Button", but in the same menu that
allows yhou to assign the macro, (Tools>Customize, right-click on the
button), you can rename the button.

Assuming you know the commandbar (let's assume it is the Format CB) and
button name (let's assume it is Calculate) the Workbook _Open code could
look like this

Private Sub Workbook_Open()

With Application.CommandBars("Format")
With .Controls("Calculate")
If Application.Calculation = xlCalculationAutomatic Then
.State = msoButtonUp
Else
.State = msoButtonDown
End If
End With
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Bob. Got it working now!

Bob Phillips said:
Slim,

As I said before, you need to know the name of the commandbar that your
button is on, and the name of the button itself. By default, when you add a
button, it is given the name "Custom Button", but in the same menu that
allows yhou to assign the macro, (Tools>Customize, right-click on the
button), you can rename the button.

Assuming you know the commandbar (let's assume it is the Format CB) and
button name (let's assume it is Calculate) the Workbook _Open code could
look like this

Private Sub Workbook_Open()

With Application.CommandBars("Format")
With .Controls("Calculate")
If Application.Calculation = xlCalculationAutomatic Then
.State = msoButtonUp
Else
.State = msoButtonDown
End If
End With
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

basic the
Click to expand...
 
Back
Top