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 :
