Marching Ants Comment Border

[howard]

I would like to know if it is possible to have marching ant around a comments border ? If so, how can I set this up

 

[RoryA]

No, it isn't as far as I know.

 

[howard]

Thanks for letting me know

 

[Jaafar Tribak]

If you mean a Cell comment then you could probably alternate the DashStyle Property of the comment and get a very close visual effect ... The following worked for me nicely:

Code in the ThisWorkbook Module ( Code assumes the comment is added to Cell Sheet1!A1 - amend Const as needed)

Option Explicit

Private WithEvents cmbrs As CommandBars

Private Const COMMENT_RANGE_ADDR As String = "Sheet1!A1"

Private Sub Workbook_Open()

    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
    
End Sub

Private Sub cmbrs_OnUpdate()

    Static i As Long
    
    With Range(COMMENT_RANGE_ADDR).Comment.Shape.Line
        .DashStyle = (i Mod 2) + 4
        .Weight = 2
    End With
    
    i = IIf(i = 1, 0, i + 1)

End Sub

You could try playing with other dash styles.

 

[howard]

Thanks Jafaar. It works perfectly, when A double click on any cell

 

[Jaafar Tribak]

Thanks Jafaar. It works perfectly, when A double click on any cell

Hi howard,

Do you mean that you need to double-click on any cell or otherwise it doen't work !? I am not sure I undestand what you mean -- If you can confirm that please .

The code as is, should work without the need for double-clicking cells or anything for that matter .

 

[howard]

Hi Jafaar


I have set up a dashed-line around the border of my comments. The marching ants scenario only works if I click on a cell. It is not automatic

 

[Jaafar Tribak]

Hi Jafaar


I have set up a dashed-line around the border of my comments. The marching ants scenario only works if I click on a cell. It is not automatic

Strange! The code I posted works automatically for me without the need to click a cell or do anything.

Anyway, try this variation wich should now hopefully trigger the marching ants automatically :

Option Explicit

Private WithEvents cmbrs As CommandBars

Private Const COMMENT_RANGE_ADDR As String = "Sheet1!A1"

Private Sub Workbook_Open()

    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate

End Sub

Private Sub cmbrs_OnUpdate()

    Static i As Long

    With Range(COMMENT_RANGE_ADDR).Comment.Shape.Line
        .DashStyle = (i Mod 2) + 4
        .Weight = 2
    End With

    Application.CommandBars.FindControl(ID:=2040).Enabled = i
    
    i = IIf(i = 1, 0, i + 1)

End Sub

 

[howard]

Thanks for the amended code Jaafar

It works perfectly