Combining two macros

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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.
 
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,
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>.
Click to expand...

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
 
Back
Top