Combining two macros

[Colin Hayes]

Hi All

I use 2 macros to cover / uncover the red triangle Comment marker :

This routine cover the markers with a shape :

Sub CommentIndicatorShapes_Place()

Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

Set ws = ActiveSheet
'shpW = 6
shpW = 7
'shpH = 4
shpH = 5

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Flip msoFlipVertical
.Flip msoFlipHorizontal
.Fill.ForeColor.SchemeColor = 22
'5=Yellow 10=Red 12=Blue 16=Brown 22=Grey 33=Pink 42=Lime 57=Green
80=White
.Fill.Visible = msoTrue
.Fill.Solid
'Put line around shape True / False
.Line.Visible = msoFalse
End With
Next cmt

End Sub


This routine removes the shapes again :


Sub CommentIndicatorShapes_Remove()


Dim ws As Worksheet
Dim shp As Shape

Set ws = ActiveSheet

For Each shp In ws.Shapes
If Not shp.TopLeftCell.Comment Is Nothing Then
If shp.AutoShapeType = _
msoShapeRightTriangle Then
shp.Delete
End If
End If
Next shp

End Sub


I've been trying to merge them into one routine , so that when it is run
it will cover the red triangles if none are present. If alternatively it
finds any present , it will uncover them. This would obviously save
having to run 2 macros , with the single merged routine covering /
uncovering as need be. Effectively , the single macro would toggle the
shapes on and off.

Can someone advise on how to merge the two?

Grateful for any advice.

 

[GS]

Colin,
Given the nature of the two macros iterating different objects
(comments/shapes), is there any reason why you can't just run them
back-to-back?

Example:
Call CommentIndicatorShapes_Place: CommentIndicatorShapes_Remove

FWIW, typical naming convention suggests to rename these so the action
precedes the subject of the action:

Example:
Call Create_CommentIndicatorShape: Remove_CommentIndicatorShape

This would be normal convention for "OnAction" procedures whereas the
way you've done it is how object event procedures are named. I suppose,
though, that it's a matter of personal preference<g>.

 

[Colin Hayes]

Colin,
Given the nature of the two macros iterating different objects
(comments/shapes), is there any reason why you can't just run them
back-to-back?

Example:
Call CommentIndicatorShapes_Place: CommentIndicatorShapes_Remove

FWIW, typical naming convention suggests to rename these so the action
precedes the subject of the action:

Example:
Call Create_CommentIndicatorShape: Remove_CommentIndicatorShape

This would be normal convention for "OnAction" procedures whereas the
way you've done it is how object event procedures are named. I suppose,
though, that it's a matter of personal preference<g>.

Hi Garry

Thank for getting back.

The two macros do run successfully back to back it's true to say , but I
wondering if it would be a neater process to combine them into one. The
'remove' macro does check for shapes , and then removes them. I was
wondering if a combined macro could check fro presence or absence of the
shapes , and then act accordingly. The coding is present in each macro ,
I was hoping it would be a simple thing to amalgamate them.


Best Wishes