Can't get Control.State to work

[wal]

Excel 2003

I created a button using Right-click-on-control-bars > Customize, and
assigned the following macro to it:

Sub ToggleCalculation()

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myMacros")
Set myControl = myBar.Controls("ToggleCalculation")

If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
myControl.State = msoButtonUp
ElseIf Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
myControl.State = msoButtonDown
End If

MsgBox myControl.State
End Sub

When I press the button, the calculation setting does toggle. But the
button stays in the "up" position (.State = 0).

Any ideas? Thanks.

 

[GS]

After serious thinking wal wrote :

Excel 2003

I created a button using Right-click-on-control-bars > Customize, and
assigned the following macro to it:

Sub ToggleCalculation()

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myMacros")
Set myControl = myBar.Controls("ToggleCalculation")

If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
myControl.State = msoButtonUp
ElseIf Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
myControl.State = msoButtonDown
End If

MsgBox myControl.State
End Sub

When I press the button, the calculation setting does toggle. But the
button stays in the "up" position (.State = 0).

Any ideas? Thanks.

Why are you playing around with the button state? I suspect you're
trying to hint whether calculation is on or the other. It would be
easier to change the caption so there's no ambiguity which mode
calculation is in without messing around with the button state.
Besides, how do you expect to restore calc mode when the button is down
(and so can't be clicked)?

Try this...

Sub ToggleCalculation()
Select Case Application.Calculation
Case xlCalculationAutomatic
Application.Calculation = xlCalculationManual
CommandBars.ActionControl.Caption = "Set Automatic Calculation"
Case xlCalculationManual
Application.Calculation = xlCalculationAutomatic
CommandBars.ActionControl.Caption = "Set Manual Calculation"
End Select
End Sub

 

[GS]

You should set the caption (at startup) to reflect calc mode default
setting.

With CommandBars("myMacros").Controls("ToggleCalculation")
With Application
If .Calculation = xlCalculationAutomatic Then
.Caption = "Set Manual Calculation"
ElseIf .Calculation = xlCalculationManual Then
.Caption = "Set Automatic Calculation"
End If
End With
End With

Obviously, you'll need to decide the default startup caption since
"ToggleCalculation" won't work. I suggest you set the caption to what
the startup calc mode is. It might be helpful to identify the control
by putting "ToggleCalculation" in its Tag property, then iterate the
controls on your "myMacros" toolbar.

<aircode>

Dim ctl As Object, CalcMode As Variant
CalcMode = Application.Calculation
For Each ctl In Commandbars("myMacros").Controls
If ctl.Tag = "ToggleCalculation" Then
If CalcMode = xlCalculationAutomatic Then
ctl.Caption = "Set Manual Calculation"
ElseIf CalcMode = xlCalculationManual Then
ctl.Caption = "Set Automatic Calculation"
Else
'set calc mode as desired if it's neither
Application.Calculation = xlCalculation[Automatic][Manual]
'set button caption accordingly
ctl.Caption = "Set [Manual][Automatic] Calculation"
End If
End If
Next ctl

</aircode>

 

[wal]

Why are you playing around with the button state?

Other posts seem to suggest it would work or be appropriate.

Besides, how do you expect to restore calc mode when the button is down
(and so can't be clicked)?

Isn't .State what causes a button to be "dark" vs. not? And doesn't
"dark" = down still allow the button to be pressed? For example, if
you run the following code for the Bold button, when the button is
"light" = up (and the text in the cell not bold), the result is 0 =
msoButtonUp; if the button is "dark" = down (cell text is bold), the
result is -1 = msoButtonDown and the button still works to toggle the
bold attribute.

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myFormatting")
Set myControl = myBar.Controls("Bold")
MsgBox myControl.State

Thanks for the sample code, which I'll try later. (I'm still curious
as to why the button can't be made to become dark by setting
the .State.)

 

[GS]

wal brought next idea :

Isn't .State what causes a button to be "dark" vs. not? And doesn't
"dark" = down still allow the button to be pressed? For example, if
you run the following code for the Bold button, when the button is
"light" = up (and the text in the cell not bold), the result is 0 =
msoButtonUp; if the button is "dark" = down (cell text is bold), the
result is -1 = msoButtonDown and the button still works to toggle the
bold attribute.

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myFormatting")
Set myControl = myBar.Controls("Bold")
MsgBox myControl.State

Thanks for the sample code, which I'll try later. (I'm still curious
as to why the button can't be made to become dark by setting
the .State.)

So are you using an actual CommandButton or are you trying to
manipulate a menuitem. In the case of the menuitem it might work better
if you set its 'checked' value to reflect the calc mode. Either way,
you have to validate the state to the current mode and update the
control accordingly. Given the numerous ambiguities associated with
either method, I've always found it easier (and more reliable) to
change the caption so it reflects the current state and suggests what
change will occur when clicked.

I do recall some info in a book by John Walkenback (or Rob Bovey) on
this. I'll try to find it and post back if nobody else responds...

 

[GS]

I found something in one of John Walkenback's "Power Programming with
VBA" books. It doesn't work as printed so some research will be
required.

 

[GS]

What I've been able to determine is that your code should work if the
commandbar was 'added' using VBA. If it was created via the UI as a
custom toolbar via the 'Customize' dialog then setting the State has no
effect. Since I have both scenarios set up, I remember why I chose to
change the Caption instead of messing around with MsoButtonState.

So then, if you want your code to work you have to build the commandbar
from scratch at startup. This means you'll also have to remove it at
shutdown. To do either will require code in the module behind
ThisWorkbook using Workbook_Open and Workbook_BeforeClose events

OR

in a standard module (possibly named "mOpenClose") using Autpen()
and Auto_Close() subs.

I assume the macros are stored in PERSONAL.XLS, but if this isn't the
case and you keep them in another workbook that opens whenever Excel
starts (ie: "MyMacros.xl_") then the code to build/delete your
commandbar should go there.

 

[Peter T]

Should work, the "State" should remain as set, try this

Sub delBar()
' run this after testing to clean up
On Error Resume Next
CommandBars("testBar").Delete
End Sub

Sub addBar()
Dim cbr As CommandBar, cbt As CommandBarButton
delBar
Set cbr = CommandBars.Add("testBar", , , True)
cbr.Visible = True
Set cbt = cbr.Controls.Add(1)
With cbt
.Caption = "My Macro"
.OnAction = "MyMacro"
.Style = msoButtonCaption
End With
End Sub

Sub myMacro()
Dim cbt As CommandBarButton
Static b As Boolean
b = Not b
Set cbt = CommandBars.ActionControl
cbt.Caption = "My Macro " & b
cbt.State = b
End Sub

The way State is indicated differs, might be change in colour or a tick to
the left.

(This is not useful for 2007/2010 users unless used on a popup bar)

Regards,
Peter T

 

[GS]

Peter T has brought this to us :

(This is not useful for 2007/2010 users unless used on a popup bar)

Peter,
This will also work with custom menus/toolbars that appear on the
Addins tab, same as it would for earlier versions.

 

[GS]

Peter T brought next idea :

Should work, the "State" should remain as set, try this

Sub delBar()
' run this after testing to clean up
On Error Resume Next
CommandBars("testBar").Delete
End Sub

Sub addBar()
Dim cbr As CommandBar, cbt As CommandBarButton
delBar
Set cbr = CommandBars.Add("testBar", , , True)
cbr.Visible = True
Set cbt = cbr.Controls.Add(1)
With cbt
.Caption = "My Macro"
.OnAction = "MyMacro"
.Style = msoButtonCaption
End With
End Sub

Sub myMacro()
Dim cbt As CommandBarButton
Static b As Boolean
b = Not b
Set cbt = CommandBars.ActionControl
cbt.Caption = "My Macro " & b
cbt.State = b
End Sub

The way State is indicated differs, might be change in colour or a tick to
the left.

(This is not useful for 2007/2010 users unless used on a popup bar)

Regards,
Peter T

I suspect the OP is using a custom toolbar created via the 'Customize'
dialog. If so then setting 'State' has no effect, though no error is
generated by attempting to set this property.

Attempting to do same with any of the built-in commandbars generates an
'automation' error.

 

[Peter T]

Yes of course, forgot about those.

Regards,
Peter T

 

[Peter T]

I suspect the OP is using a custom toolbar created via the 'Customize'
dialog. If so then setting 'State' has no effect, though no error is
generated by attempting to set this property.

Attempting to do same with any of the built-in commandbars generates an
'automation' error.

That wouldn't have occurred to me but on checking, indeed as you say, a
button added that way doesn't seem to respond to State. However a button
added programmatically to a built-in bar does respond to State.

OP, how did you add your button?

Regards,
Peter T

 

[GS]

Peter T pretended :

That wouldn't have occurred to me but on checking, indeed as you say, a
button added that way doesn't seem to respond to State. However a button
added programmatically to a built-in bar does respond to State.

OP, how did you add your button?

Regards,
Peter T

Just to clarify...
The popup menu you refer to can't be a built-in popup unless the
menuitem was added programmatically. Otherwise, (as with all built-in
menuitems) any attempt to setting 'State' generates the 'automation'
error.

 

[GS]

wal wrote on 3/29/2011 :

Other posts seem to suggest it would work or be appropriate.


Isn't .State what causes a button to be "dark" vs. not? And doesn't
"dark" = down still allow the button to be pressed? For example, if
you run the following code for the Bold button, when the button is
"light" = up (and the text in the cell not bold), the result is 0 =
msoButtonUp; if the button is "dark" = down (cell text is bold), the
result is -1 = msoButtonDown and the button still works to toggle the
bold attribute.

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myFormatting")
Set myControl = myBar.Controls("Bold")
MsgBox myControl.State

Thanks for the sample code, which I'll try later. (I'm still curious
as to why the button can't be made to become dark by setting
the .State.)

Good luck with this!

Just in case you go with my suggestion to change the caption, please
know that the only way you can set the Tag prop for a menuitem created
via the 'Customize' dialog is to do it in the VBE. You can use the
Immediate Window, and the following statement:


CommandBars("myMacros").Controls("ToggleCalculation").Tag="ToggleCalculation"

Do this before initial running of the startup code I posted.

 

[wal]

Right-click-on-control-bars > Customize...

Control.State appears to be read-only for such buttons.

Thanks to all for all the input. I'll have to decide whether I want
to reconstruct a button upon Open or not.

 

[Peter T]

Indeed you originally said you were adding the button with Customize, sorry
I should have read your OP better. But why not add the bar and buttons each
time in the workbook's open event, and delete in the close. That's the
normal way, not difficult, will give you a lot more control and solve your
State problem.

Regards,
Peter T

PS, in the open event start by running the code to delete the old bar, just
in case it wasn't in the last close event.



Right-click-on-control-bars > Customize...

Control.State appears to be read-only for such buttons.

Thanks to all for all the input. I'll have to decide whether I want
to reconstruct a button upon Open or not.