Toggling buttons

F

Francis Hookham

Toggling buttons

I often use Autoshape buttons and assigned macros to them - when the button
is used to toggle (Protect/Unprotect) then the colour and/or wording on the
button is changed - ok, that is not a problem

But, in the case of a toolbar button, is it possible for a custom toolbar
button to change appearnce as it toggles? Many toolbar buttons (Toggle Grid
and Lock Cell are two) have a grey background in one state and not in the
other. Is that too deep inside XL for me to utilise? Can a differently
edited button be used for each state?

Thanks again

Francis Hookham
 
B

Bernard Rey

Francis Hookham wrote :
I often use Autoshape buttons and assigned macros to them - when the button
is used to toggle (Protect/Unprotect) then the colour and/or wording on the
button is changed - ok, that is not a problem

But, in the case of a toolbar button, is it possible for a custom toolbar
button to change appearnce as it toggles? Many toolbar buttons (Toggle Grid
and Lock Cell are two) have a grey background in one state and not in the
other. Is that too deep inside XL for me to utilise? Can a differently
edited button be used for each state?

I'm not sure if this is easy to do with edited buttons. But you can change
"standard" designed button faces through the "FaceId" property. You can
include a line in your macro to set the button FaceId property to the wanted
number. You can find the list here: http://www.mvps.org/skp/faceid.htm
(don't be afrais if the description seem to fit the Windows version, it
works on the Mac too).
 
F

Francis Hookham

Thanks Bernard - pursuing this one a bit further there are two questions
1

the first two subs below toggle the position of a Postit - PostitHide moves
the Postit out of the way and sends the HidePostit button behind the
GetPostit button etc etc - works well

In the third sub I have tried to toggle the position of the Postit using
only one button in an If statement but the whole thing is a mess - either I
have got a lot wrong or I am trying to push VBA too far - surely not! Any
suggestions with what might seem a trivial prob but one which I often use
and would like to slicken up

2

My previous question was really to do with whether or not a custom toolbar
button could be used with a similar toggle type macro where the button would
grey out as it does in the case of many standard toolbar buttons such as
Align buttons - there are times when that would make custom toolbar button
much more useful - for instance combining the ProtectAll and UnprotectAll
subs in your post to Kevs on 12/8 - now there's a thought - that would be
smooth!

Thanks for all your help

Francis

Sub PostitHide()
ActiveSheet.Shapes("PostIt").Select
Selection.ShapeRange.IncrementLeft 330#
Selection.ShapeRange.IncrementTop 150#
ActiveSheet.Shapes("HidePostit").Select
Selection.ShapeRange.ZOrder msoSendToBack
Application.Goto Reference:="R1C1"
End Sub

Sub PostitGet()
ActiveSheet.Shapes("GetPostit").Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Shapes("PostIt").Select
Selection.ShapeRange.IncrementLeft -330#
Selection.ShapeRange.IncrementTop -150#
End Sub

Sub ShowHidePostit()
If ActiveSheet.Shapes("PostitButton").Characters.Text = "Show" Then
ActiveSheet.Shapes("PostitButton").Characters.Text = "Hide"
ActiveSheet.Shapes("PostitButton").ShapeRange.IncrementLeft 330#
ActiveSheet.Shapes("PostitButton").ShapeRange.IncrementTop 150#
Else
ActiveSheet.Shapes("PostitButton").Characters.Text = "Show"
ActiveSheet.Shapes("PostitButton").ShapeRange.IncrementLeft -330#
ActiveSheet.Shapes("PostitButton").ShapeRange.IncrementTop 1150#
End If
End Sub


Francis Hookham wrote :
I often use Autoshape buttons and assigned macros to them - when the button
is used to toggle (Protect/Unprotect) then the colour and/or wording on the
button is changed - ok, that is not a problem

But, in the case of a toolbar button, is it possible for a custom toolbar
button to change appearnce as it toggles? Many toolbar buttons (Toggle Grid
and Lock Cell are two) have a grey background in one state and not in the
other. Is that too deep inside XL for me to utilise? Can a differently
edited button be used for each state?

I'm not sure if this is easy to do with edited buttons. But you can change
"standard" designed button faces through the "FaceId" property. You can
include a line in your macro to set the button FaceId property to the wanted
number. You can find the list here: http://www.mvps.org/skp/faceid.htm
(don't be afrais if the description seem to fit the Windows version, it
works on the Mac too).

Bernard Rey - Toulouse / France
MVP - Macintosh
 
B

Bernard Rey

Francis Hookham wrote :
the first two subs below toggle the position of a Postit - PostitHide moves
the Postit out of the way and sends the HidePostit button behind the
GetPostit button etc etc - works well

OK, I see what you're trying to do.

You could have them a bit "cleaner", but it won't modify it deeply:

Sub PostitHide()
ActiveSheet.Shapes("PostIt").IncrementLeft 330#
ActiveSheet.Shapes("PostIt").IncrementTop 150#
ActiveSheet.Shapes("HidePostit").ZOrder msoSendToBack
Rabge("A1").Select
End Sub

Sub PostitGet()
ActiveSheet.Shapes("GetPostit").ZOrder msoSendToBack
ActiveSheet.Shapes("PostIt").IncrementLeft -330#
ActiveSheet.Shapes("PostIt").IncrementTop -150#
End Sub
In the third sub I have tried to toggle the position of the Postit using
only one button in an If statement but the whole thing is a mess - either I
have got a lot wrong or I am trying to push VBA too far - surely not! Any
suggestions with what might seem a trivial prob but one which I often use
and would like to slicken up

You're not so far from it. The trick is a bit hard to explain (to me) but it
lies in the differences between Shapes, Shape and ShapeRange. This may be
somewhat confusing as they don't have the same property and still belong to
one another. Here's a transcription that seem to work as you expected:

Sub ShowHidePostit()
ActiveSheet.Shapes("PostitButton").Select
With Selection
If .Text = "Show" Then
.Text = "Hide"
.ShapeRange.IncrementLeft 330#
.ShapeRange.IncrementTop 150#
Else
.Text = "Show"
.ShapeRange.IncrementLeft -330#
.ShapeRange.IncrementTop -150#
End If
End With
End Sub
My previous question was really to do with whether or not a custom toolbar
button could be used with a similar toggle type macro where the button would
grey out as it does in the case of many standard toolbar buttons such as
Align buttons - there are times when that would make custom toolbar button
much more useful - for instance combining the ProtectAll and UnprotectAll
subs in your post to Kevs on 12/8 - now there's a thought - that would be
smooth!

Greying out seems to be too much to ask. But it's possible to change the
icon, as mentioned.

Hereunder, a couple of ideas how to do. First a macro creating a new ToolBar
bearing one button, but keeping it hidden. It could be placed in the
ThisWorkbook Code sheet as a "Workbook_Open()" Private Sub, thus running
each time you open the concerned workbook:

Sub CreatingBar()
Application.CommandBars.Add(Name:="MyBar").Visible = False 'or True
Application.CommandBars("MyBar").Controls.Add Type:=msoControlButton
End Sub

Now a macro (but the lines can be inserted in any other macro, of course)
making the bar visible, and adding a smiling face icon on the button:

Sub SmilingFace()
Application.CommandBars("MyBar").Visible = True
Application.CommandBars("MyBar").Controls(1).FaceId = 59
End Sub

This next one Changes the smiling face to a sad face:

Sub WiningFace()
Application.CommandBars("MyBar").Visible = True
Application.CommandBars("MyBar").Controls(1).FaceId = 276
End Sub

And now the last but not least cleaning macro that you could insert as a
"Workbook_BeforeClose()" Private Sub, in order to be run when you close that
workbook:

Sub DeletingBar()
Application.CommandBars("MyBar").Delete
End Sub

I chose to create a new toolbar, as is MUCH easier to select the first
button on the bar when there is only one, than to find where the button may
have been added (or moved) on a standard but customized bar...



---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh


Francis Hookham wrote :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top