Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem setting the State of a custom Toolbar button
I have created a number of custom toolbar buttons on a custom
toolbar. Some of these are toggle buttons and I can set their state (ie, msoButtonDown or msoButtonUp). One of these, however, is not responding and the State doesn't change. The code is below in full, and any suggestions are appreciated. Thanks in advance Paul Martin Melbourne, Australia Public Sub ToggleCalcMode() With Application .Calculation = IIf(.Calculation = xlCalculationManual, _ xlCalculationAutomatic, xlCalculationManual) MsgBox "Excel is in " & IIf(.Calculation = xlCalculationManual, "Manual", "Automatic") _ & " calc mode" CommandBars(CUSTOM_TOOLBAR).Controls("Toggle Calculation Mode").State = _ IIf(.Calculation = xlCalculationAutomatic, msoButtonDown, msoButtonUp) End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem setting the State of a custom Toolbar button
Does anyone have any suggestions regarding this issue?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem setting the State of a custom Toolbar button
Assuming your button can show a difference in state (it depends on type &
location) I don't see anything obvious in your code why the state does not change (I may have missed). Try something like this instead - Sub ToggleCalc() Dim nCalc As Long Dim nState As Long Dim sCalc Dim cb As CommandBarButton ' Set cb = CommandBars(CUSTOM_TOOLBAR). _ Controls("Toggle Calculation Mode") Set cb = Application.CommandBars.ActionControl ' for testing On Error GoTo errH nCalc = Application.Calculation Select Case nCalc Case xlCalculationManual, xlCalculationSemiautomatic nCalc = xlCalculationAutomatic sCalc = "Automatic" nState = msoButtonDown Case Else nCalc = xlCalculationManual sCalc = "Manual" nState = msoButtonUp End Select Application.Calculation = nCalc cb.State = nState MsgBox "Calculation is now " & sCalc Exit Sub errH: MsgBox "Cannot change Calculation" ' eg no activeworkbook End Sub Regards, Peter T "Paul Martin" wrote in message ... I have created a number of custom toolbar buttons on a custom toolbar. Some of these are toggle buttons and I can set their state (ie, msoButtonDown or msoButtonUp). One of these, however, is not responding and the State doesn't change. The code is below in full, and any suggestions are appreciated. Thanks in advance Paul Martin Melbourne, Australia Public Sub ToggleCalcMode() With Application .Calculation = IIf(.Calculation = xlCalculationManual, _ xlCalculationAutomatic, xlCalculationManual) MsgBox "Excel is in " & IIf(.Calculation = xlCalculationManual, "Manual", "Automatic") _ & " calc mode" CommandBars(CUSTOM_TOOLBAR).Controls("Toggle Calculation Mode").State = _ IIf(.Calculation = xlCalculationAutomatic, msoButtonDown, msoButtonUp) End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem setting the State of a custom Toolbar button
Thanks for the attempt, Peter, but your suggested code doesn't change
the button's state either. I did, however, find the cause of the problem and the solutions. I used the calculator icon, by clicking, Tools, Customise, Commands, Tools and dragging Custom (with the calculator icon) onto my custom toolbar. This icon doesn't seem to like having its state changed by my code. I solved the problem by putting my own custom icon there and in code changing it's FaceID to 283 (which is the same icon). This button now changes state as expected. Hopefully this solution might help others down the track. Regards Paul On Jul 16, 4:42 am, "Peter T" <peter_t@discussions wrote: Assuming your button can show a difference in state (it depends on type & location) I don't see anything obvious in your code why the state does not change (I may have missed). Try something like this instead - Sub ToggleCalc() Dim nCalc As Long Dim nState As Long Dim sCalc Dim cb As CommandBarButton ' Set cb = CommandBars(CUSTOM_TOOLBAR). _ Controls("Toggle Calculation Mode") Set cb = Application.CommandBars.ActionControl ' for testing On Error GoTo errH nCalc = Application.Calculation Select Case nCalc Case xlCalculationManual, xlCalculationSemiautomatic nCalc = xlCalculationAutomatic sCalc = "Automatic" nState = msoButtonDown Case Else nCalc = xlCalculationManual sCalc = "Manual" nState = msoButtonUp End Select Application.Calculation = nCalc cb.State = nState MsgBox "Calculation is now " & sCalc Exit Sub errH: MsgBox "Cannot change Calculation" ' eg no activeworkbook End Sub Regards, Peter T "Paul Martin" wrote in message ... I have created a number of custom toolbar buttons on a custom toolbar. Some of these are toggle buttons and I can set their state (ie, msoButtonDown or msoButtonUp). One of these, however, is not responding and the State doesn't change. The code is below in full, and any suggestions are appreciated. Thanks in advance Paul Martin Melbourne, Australia Public Sub ToggleCalcMode() With Application .Calculation = IIf(.Calculation = xlCalculationManual, _ xlCalculationAutomatic, xlCalculationManual) MsgBox "Excel is in " & IIf(.Calculation = xlCalculationManual, "Manual", "Automatic") _ & " calc mode" CommandBars(CUSTOM_TOOLBAR).Controls("Toggle Calculation Mode").State = _ IIf(.Calculation = xlCalculationAutomatic, msoButtonDown, msoButtonUp) End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem setting the State of a custom Toolbar button
So if I follow, what I said about "assuming your button can show a
difference in state etc" turned out to be rather pertinent! Regards, Peter T "Paul Martin" wrote in message ... Thanks for the attempt, Peter, but your suggested code doesn't change the button's state either. I did, however, find the cause of the problem and the solutions. I used the calculator icon, by clicking, Tools, Customise, Commands, Tools and dragging Custom (with the calculator icon) onto my custom toolbar. This icon doesn't seem to like having its state changed by my code. I solved the problem by putting my own custom icon there and in code changing it's FaceID to 283 (which is the same icon). This button now changes state as expected. Hopefully this solution might help others down the track. Regards Paul On Jul 16, 4:42 am, "Peter T" <peter_t@discussions wrote: Assuming your button can show a difference in state (it depends on type & location) I don't see anything obvious in your code why the state does not change (I may have missed). Try something like this instead - Sub ToggleCalc() Dim nCalc As Long Dim nState As Long Dim sCalc Dim cb As CommandBarButton ' Set cb = CommandBars(CUSTOM_TOOLBAR). _ Controls("Toggle Calculation Mode") Set cb = Application.CommandBars.ActionControl ' for testing On Error GoTo errH nCalc = Application.Calculation Select Case nCalc Case xlCalculationManual, xlCalculationSemiautomatic nCalc = xlCalculationAutomatic sCalc = "Automatic" nState = msoButtonDown Case Else nCalc = xlCalculationManual sCalc = "Manual" nState = msoButtonUp End Select Application.Calculation = nCalc cb.State = nState MsgBox "Calculation is now " & sCalc Exit Sub errH: MsgBox "Cannot change Calculation" ' eg no activeworkbook End Sub Regards, Peter T "Paul Martin" wrote in message ... I have created a number of custom toolbar buttons on a custom toolbar. Some of these are toggle buttons and I can set their state (ie, msoButtonDown or msoButtonUp). One of these, however, is not responding and the State doesn't change. The code is below in full, and any suggestions are appreciated. Thanks in advance Paul Martin Melbourne, Australia Public Sub ToggleCalcMode() With Application .Calculation = IIf(.Calculation = xlCalculationManual, _ xlCalculationAutomatic, xlCalculationManual) MsgBox "Excel is in " & IIf(.Calculation = xlCalculationManual, "Manual", "Automatic") _ & " calc mode" CommandBars(CUSTOM_TOOLBAR).Controls("Toggle Calculation Mode").State = _ IIf(.Calculation = xlCalculationAutomatic, msoButtonDown, msoButtonUp) End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding custom images to a button on custom toolbar | Excel Programming | |||
Custom button on toolbar | Excel Programming | |||
custom toolbar button questions | Excel Programming | |||
Delete a custom button by holding down the ALT key and dragging the button off the toolbar | Excel Programming | |||
Can't delete custom button and toolbar | Excel Programming |